Why this query does't use index-only scan in PostgreSQL?

Question

I have a table with 28 columns and 7M records without primary key.

CREATE TABLE records (
  direction smallint,
  exporters_id integer,
  time_stamp integer
  ...
)

I create index on this table and vacuum table after that (autovacuum is on)

CREATE INDEX exporter_dir_time_only_index ON sacopre_records
USING btree (exporters_id, direction, time_stamp);

and i want execute this query

SELECT count(exporters_id) FROM records WHERE exporters_id = 50

The table has 6982224 records with exporters_id = 50. I expected this query use index-only scan to get results but it used sequential scan. This is "EXPLAIN ANALYZE" output:

Aggregate  (cost=204562.25..204562.26 rows=1 width=4) (actual time=1521.862..1521.862 rows=1 loops=1)
->  Seq Scan on sacopre_records (cost=0.00..187106.88 rows=6982149 width=4) (actual time=0.885..1216.211 rows=6982224 loops=1)
    Filter: (exporters_id = 50)
    Rows Removed by Filter: 2663
Total runtime: 1521.886 ms

but when I change the exporters_id to another id, query use index-only scan

Aggregate  (cost=46.05..46.06 rows=1 width=4) (actual time=0.321..0.321 rows=1 loops=1)
->  Index Only Scan using exporter_dir_time_only_index on sacopre_records  (cost=0.43..42.85 rows=1281 width=4) (actual time=0.313..0.315 rows=4 loops=1)
    Index Cond: (exporters_id = 47)
    Heap Fetches: 0
Total runtime: 0.358 ms

Where is the problem?

Have you tried SELECT COUNT(exporters_id=50) FROM records? — Tordek
@Tordek, I test it now and get same result, it use seq-scan. — Foad Tahmasebi
maybe new index is not analyzed and therefore presented to planner?.. try vacuum analyze records — Vao Tsun
@VaoTsun, I said that above , I executed "vacuum analyze" and auto vacuum is on. — Foad Tahmasebi

Kouber Saparev Kouber Saparev · Accepted Answer · 2015-08-19T07:58:59

The explain is telling you the reason. Look closer.

Aggregate  (cost=204562.25..204562.26 rows=1 width=4) (actual time=1521.862..1521.862 rows=1 loops=1)
->  Seq Scan on sacopre_records (cost=0.00..187106.88 rows=6982149 width=4) (actual time=0.885..1216.211 rows=6982224 loops=1)
    Filter: (exporters_id = 50)
    Rows Removed by Filter: 2663
Total runtime: 1521.886 ms

Your filter is removing only 2663 rows out of the total amount of 6982149 rows in the table, hence doing a sequential scan should really be faster than using an index, as the disk head should pass through 6982149 - 2663 = 6979486 records anyway. The disk head is starting to read the entire table sequentially and on the way is removing that tiny fraction (0.000004 %) that does not match your criteria. While in the index scan case it should jump from the index file(s) and get back to the data file(s) 6979486 times, which for sure should be slower than these 1.5 seconds you are getting now!

Why this query does't use index-only scan in PostgreSQL?

1 Answers