Reputation: 7350
I have very large amount of data, about 180M records and growing.
My question is, what would you suggest to do in order to accelerate the queries even further?
explain analyze
SELECT valtr_id,block_num,bnumvt,from_id,to_id,from_balance::text,to_balance::text,value,kind,tx_id,error
FROM value_transfer
WHERE from_id=19920723
ORDER BY bnumvt DESC LIMIT 20
Limit (cost=0.57..275.79 rows=20 width=69) (actual time=138197.769..138198.447 rows=20 loops=1)
-> Index Scan Backward using bnumvt_idx on value_transfer (cost=0.57..216277802.83 rows=15716575 width=69) (actual time=138197.765..138198.437 rows=20 loops=1)
Filter: (from_id = 19920723)
Rows Removed by Filter: 58980120
Planning time: 2.188 ms
Execution time: 138198.618 ms
(6 rows)
postgres=> select count(*) from value_transfer where from_id=19920723;
count
----------
15584518
(1 row)
postgres=> select count(*) from value_transfer;
count
-----------
179685101
(1 row)
>
postgres=> SELECT * FROM pg_indexes WHERE tablename = 'value_transfer';
schemaname | tablename | indexname | tablespace | indexdef
------------+----------------+---------------------+------------+-----------------------------------------------------------------------------------------
public | value_transfer | value_transfer_pkey | | CREATE UNIQUE INDEX value_transfer_pkey ON public.value_transfer USING btree (valtr_id)
public | value_transfer | bnumvt_idx | | CREATE UNIQUE INDEX bnumvt_idx ON public.value_transfer USING btree (bnumvt)
public | value_transfer | vt_block_id_idx | | CREATE INDEX vt_block_id_idx ON public.value_transfer USING btree (block_id)
public | value_transfer | vt_block_num_idx | | CREATE INDEX vt_block_num_idx ON public.value_transfer USING btree (block_num)
public | value_transfer | vt_from_id_idx | | CREATE INDEX vt_from_id_idx ON public.value_transfer USING btree (from_id)
public | value_transfer | vt_to_id_idx | | CREATE INDEX vt_to_id_idx ON public.value_transfer USING btree (to_id)
public | value_transfer | vt_tx_from_idx | | CREATE INDEX vt_tx_from_idx ON public.value_transfer USING btree (tx_id)
(7 rows)
CREATE TABLE value_transfer (
valtr_id BIGSERIAL PRIMARY KEY,
tx_id BIGINT REFERENCES transaction(tx_id) ON DELETE CASCADE ON UPDATE CASCADE,
bnumvt BIGINT NOT NULL,
block_id INT REFERENCES block(block_id) ON DELETE CASCADE ON UPDATE CASCADE,
block_num INT NOT NULL,
from_id INT NOT NULL,
to_id INT NOT NULL,
value NUMERIC DEFAULT 0,
from_balance NUMERIC DEFAULT 0,
to_balance NUMERIC DEFAULT 0,
kind CHAR NOT NULL,
depth INT DEFAULT 0,
error TEXT NOT NULL
);
Upvotes: 0
Views: 44
Reputation: 23797
Creating an index matching your query's search and sort would speed it up:
CREATE INDEX vt_from_id_ bnumvt_idx
ON public.value_transfer
USING btree (from_id, bnumvt desc);
Upvotes: 1