CODEWITHSUNDEEP
sqlpostgresqlperformanceindexingpostgresql-10
Yakovenko Alexey
Yakovenko Alexey

Reputation: 147

PostgreSQL. Improve indexes

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

Upvotes: 2

Views: 69

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246053

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.

Upvotes: 1

Related Questions