PostgreSQL. Improve indexes

Question

I have the following structure:

create table bitmex
(
  timestamp timestamp with time zone not null,
  symbol    varchar(255)             not null,
  side      varchar(255)             not null,
  tid       varchar(255)             not null,
  size      numeric                  not null,
  price     numeric                  not null,
  constraint bitmex_tid_symbol_pk
  primary key (tid, symbol)
);

create index bitmex_timestamp_symbol_index  on bitmex (timestamp, symbol);
create index bitmex_symbol_index  on bitmex (symbol);

I need to know the exact value of the quantity every time. So reltuples is not usable.

The table has more than 45,000,000 rows.

Running

explain analyze select count(*) from bitmex where symbol = 'XBTUSD';

gives

Finalize Aggregate  (cost=1038428.56..1038428.57 rows=1 width=8)
  ->  Gather  (cost=1038428.35..1038428.56 rows=2 width=8)
        Workers Planned: 2
        ->  Partial Aggregate  (cost=1037428.35..1037428.36 rows=1 width=8)
              ->  Parallel Seq Scan on bitmex  (cost=0.00..996439.12 rows=16395690 width=0)
                    Filter: ((symbol)::text = 'XBTUSD'::text)

Running

explain analyze select count(*) from bitmex;

gives

Finalize Aggregate  (cost=997439.34..997439.35 rows=1 width=8) (actual time=6105.463..6105.463 rows=1 loops=1)
  ->  Gather  (cost=997439.12..997439.33 rows=2 width=8) (actual time=6105.444..6105.457 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=996439.12..996439.14 rows=1 width=8) (actual time=6085.960..6085.960 rows=1 loops=3)
              ->  Parallel Seq Scan on bitmex  (cost=0.00..954473.50 rows=16786250 width=0) (actual time=0.364..4342.460 rows=13819096 loops=3)
Planning time: 0.080 ms
Execution time: 6108.277 ms

Why it did not use indexes? Thanks

Roughly how many rows have symbol = 'XBTUSD'? If most of the table has it, then the index isn't going to speed up anything and PG will skip it. Also, when was the table last ANALYZED? Stats being out of date can also cause this sort of thing. The second one doesn't use the index because you told it to select all rows and COUNT them. — jpmc26
– jpmc26, Commented May 22, 2018 at 21:05
@jpmc26 . . . That is not true. The index is smaller to scan so it should be preferred over the original table. The index is a covering index. — Gordon Linoff
– Gordon Linoff, Commented May 22, 2018 at 21:12

Laurenz Albe · Accepted Answer · 2018-05-23 07:33:15Z

1

If all rows have to be visited, an index scan is only cheaper if the table does not have to be consulted for most of the values found in the index.

Due to the way PostgreSQL is organized, the table has to be visited to determine if the entry found in the index is visible or not. This step can be skipped if the whole page is marked as “visible” in the visibility map of the table.

To update the visibility map, run VACUUM on the table. Maybe then an index only scan will be used.

But counting the number of rows in a table is never cheap, even with an index scan. If you need to do that often, it may be a good idea to have a separate table that only contains a counter for the number of rows. Then you can write triggers that update the counter whenever rows are inserted or deleted.

That will slow down the performance during INSERT and DELETE, but you can count the rows with lightning speed.

answered May 23, 2018 at 7:33

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yakovenko Alexey Over a year ago

thank you, i've made separate table and update counters manually.

Collectives™ on Stack Overflow

PostgreSQL. Improve indexes

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related