0

I have the following table:

create schema test;
CREATE TABLE test.foo (
    info_datetime timestamp NOT NULL,
    asset_id int4 NOT NULL,
    price float8 NULL,
    CONSTRAINT minute_prices_pkey PRIMARY KEY (info_datetime, asset_id)
);
CREATE INDEX foo_asset_id_idx ON test.foo USING btree (asset_id);

when i run this with a small amount of rows the query uses the dedicated index:

explain
select *
from test.foo mp
where mp.asset_id = 1

Bitmap Heap Scan on foo mp  (cost=4.21..14.37 rows=8 width=20) (actual time=0.005..0.006 rows=0 loops=1)
  Recheck Cond: (asset_id = 1)
  Buffers: shared hit=1
  ->  Bitmap Index Scan on foo_asset_id_idx  (cost=0.00..4.21 rows=8 width=0) (actual time=0.003..0.003 rows=0 loops=1)
        Index Cond: (asset_id = 1)
        Buffers: shared hit=1
Planning Time: 0.077 ms
Execution Time: 0.026 ms

however when I insert about 3000 rows, the same query doesn't use the index

Seq Scan on foo mp  (cost=0.00..42.56 rows=1332 width=20) (actual time=0.012..0.435 rows=1332 loops=1)
  Filter: (asset_id = 1)
  Rows Removed by Filter: 873
  Buffers: shared hit=15
Planning Time: 0.074 ms
Execution Time: 0.707 ms
5
  • 1
    Did you run ANALYZE test.foo after inserting 3000 rows ?
    – pifor
    Apr 23 '20 at 10:35
  • Depending on estimations query optimizer chooses cheaper execution plan. For some cases it may be easier to scan the whole table instead of perform 3000 bookmark lookups to get rest of the columns. Apr 23 '20 at 10:38
  • Please edit your question and add the execution plans generated using explain (analyze, buffers) Apr 23 '20 at 10:58
  • it seems to be very inconsistent one time it uses the index and other times it doesn't
    – moshevi
    Apr 23 '20 at 11:03
  • @pifor yes i ran
    – moshevi
    Apr 23 '20 at 11:13
0

In this plan:

Seq Scan on foo mp  (cost=0.00..42.56 rows=1332 width=20) (actual time=0.012..0.435 rows=1332 loops=1)
  Filter: (asset_id = 1)
  Rows Removed by Filter: 873
  Buffers: shared hit=15
Planning Time: 0.074 ms
Execution Time: 0.707 ms

You can see that roughly 60% (1332 of 2205) of the rows from that table are returned. Doing that with a Seq Scan is much more efficient that doing an index lookup for each of those rows.

In the first query Postgres only expected 8 rows to be returned (in reality no row was returned) and for that it is more efficient to probe the index.

See this related answer

2
  • ohh, ok. So if this is not the case the index should be used ?
    – moshevi
    Apr 23 '20 at 13:57
  • @moshevi: an index is used if that will reduce the number of rows substantially. There is no hard cut-off threshold for this. But typically this is somewhere around 10-15% for B-Tree indexes (might be less might be more depends on a lot of things). Sometimes the index can be used for a Bitmap Index Scan if a larger number of rows will satisfy the condition. Apr 23 '20 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.