Nulik
Nulik

Reputation: 7350

how can I accelerate a query for a table with 180M records?

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

Answers (1)

Cetin Basoz
Cetin Basoz

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

Related Questions