2
\d hits;

    Column    |            Type             
--------------+-----------------------------
 id           | integer                     
 link_id      | integer                     
 country_code | character varying(2)        
 created_at   | timestamp without time zone 

Indexes:
    "hits_pkey" PRIMARY KEY, btree (id)
    "hits_id_country_code_idx" btree (id, country_code)
VACUUM;
EXPLAIN ANALYSE SELECT id, country_code 
  FROM hits 
  WHERE id > 0;

 Seq Scan on hits
    (cost=0.00..2194406.50 rows=73641640 width=7)
    (actual time=63752.432..126357.467 rows=100000000 loops=1)
   Filter: (id > 0)
 Planning time: 183.492 ms
 Execution time: 129469.889 ms
SET enable_seqscan = OFF;
EXPLAIN ANALYSE SELECT id, country_code 
  FROM hits 
  WHERE id > 0;

 Index Only Scan using hits_id_country_code_idx on hits
     (cost=0.57..2385505.27 rows=73641640 width=7)
     (actual time=67.338..13708.853 rows=100000000 loops=1)
   Index Cond: (id > 0)
   Heap Fetches: 0
 Planning time: 239.017 ms     
 Execution time: 16077.119 ms

How can I help the planner to understand how many values in the visibility map are set? Because when I run a VACUUM frequently the index only scan is much faster, but the planner chooses a sequence scan instead.

3
  • How is this possible? Are the great majority of the table's IDs negative? Also, for the first example, do you get a significant execution time variation between two consecutive runs? Caching can have an enormous impact in execution time, so if you're running those two queries in sequence, the second query would benefit greatly, but still be slightly slower than running the first one again (unless most of your IDs are negative). – Ezequiel Tolnay Apr 11 '16 at 7:17
  • All IDs are positive. I ran the queries multiple times, dropping the cache after each execution. The result didn't change significantly. – neonmate Apr 11 '16 at 10:07
  • Silly me I didn't realise this is all about the second index... Have you tried with a unique index for id + country_code? I believe PG relies on unique keys for retrieval of info, whilst only for searches/order for non/unique ones. – Ezequiel Tolnay Apr 11 '16 at 10:16

Your Answer

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

Browse other questions tagged or ask your own question.