Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My PosgreSQL planner (9.2) does not pick index and rather choose to do seq scan.

Here is my table. The index I am talking about is name_left_prefix

cwu=# \d web_city;
                                  Table "public.web_city"
   Column   |         Type          |                       Modifiers                       
------------+-----------------------+-------------------------------------------------------
 id         | integer               | not null default nextval('web_city_id_seq'::regclass)
 name       | character varying(64) | not null
 latitude   | double precision      | 
 longitude  | double precision      | 
 time_zone  | character varying(64) | not null
 population | bigint                | 
 country_id | integer               | 
 location   | geometry(Point,4326)  | 
Indexes:
    "web_city_pkey" PRIMARY KEY, btree (id)
    "name_left_prefix" btree ("left"(name::text, 5))
    "web_city_country_id" btree (country_id)
    "web_city_location_id" gist (location)
Foreign-key constraints:
    "country_id_refs_id_55d3b49fd200671" FOREIGN KEY (country_id) REFERENCES web_country(id) DEFERRABLE INITIALLY DEFERRED

Now I run the query that is supposed to use that index.

cwu=# explain analyze select count(*) from web_city GROUP BY (left(name,5));
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=19016.35..21188.28 rows=94677 width=10) (actual time=502.308..691.288 rows=64459 loops=1)
   ->  Sort  (cost=19016.35..19345.84 rows=131796 width=10) (actual time=502.300..660.121 rows=131796 loops=1)
         Sort Key: ("left"((name)::text, 5))
         Sort Method: external merge  Disk: 2048kB
         ->  Seq Scan on web_city  (cost=0.00..5554.45 rows=131796 width=10) (actual time=0.077..46.672 rows=131796 loops=1)
 Total runtime: 694.977 ms
(6 rows)

It does not use it though. But it does use it when I disable seq_scan and the resulting time is considerably better!

cwu=# set enable_seqscan=false;
SET
cwu=# explain analyze select count(*) from web_city GROUP BY (left(name,5));
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=0.00..21237.14 rows=94677 width=10) (actual time=0.187..114.887 rows=64459 loops=1)
   ->  Index Scan using name_left_prefix on web_city  (cost=0.00..19394.69 rows=131796 width=10) (actual time=0.163..79.829 rows=131796 loops=1)
 Total runtime: 118.095 ms
(3 rows)

Why this is happening? I don't really desire to set enable_seqscan=false; from my application before making that query.

EDIT: I have just found out that this does not happen on my server that has PostgreSQL 9.2.3. But it does happen on my laptop with PostgreSQL 9.2.4. Maybe I'll try to downgrade just test it out.

EDIT2: After setting work_mem = 4MB to avoid Sort Method: external merge Disk: 2048kB(Thank you, Igor!) the time has got considerably better for the Seq Scan. Now I approve the decision not to use the index:).

cwu=# explain analyze select count(*) from web_city GROUP BY (left(name,5));
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=6213.43..6671.27 rows=36627 width=10) (actual time=95.879..111.447 rows=64459 loops=1)
   ->  Seq Scan on web_city  (cost=0.00..5554.45 rows=131796 width=10) (actual time=0.424..45.285 rows=131796 loops=1)
 Total runtime: 115.838 ms
share|improve this question

migrated from stackoverflow.com Apr 26 '13 at 18:23

This question came from our site for professional and enthusiast programmers.

2  
Generally, when a query optimizer chooses what appears to be the less efficient route, it has decided that using the more efficient (but possibly higher overhead) route is not going to produce any substantial benefit. –  Robert Harvey Apr 23 '13 at 19:43
    
0,5s is quite a difference though. –  clime Apr 23 '13 at 19:46
1  
Did you analyze the table. Maybe you have wrong statistics –  a_horse_with_no_name Apr 23 '13 at 19:47
1  
I see index only scan in the second case. The time to execute index only scan depends on many factors and can be much slower than plain scan (if the whole table was updated recently). My guess - the planner just picks the safest (not the fastest) plan. –  Igor Romanchenko Apr 23 '13 at 20:08
1  
@clime Side note - increase the work_mem in postgresql configs, so that DB wont need on disk sort for 2 MB of data (external merge Disk: 2048kB in the first plan) –  Igor Romanchenko Apr 23 '13 at 20:15

2 Answers 2

PostgreSQL only supports index-only scans since 9.2 and only if the visibility map allows that.

If there is but a single concurrent transaction which has updated a record in a page (and hence there exists a record not visible to all transaction), the visibility map record for this page is invalidated and its records need to be checked for visibility (requiring a heap fetch).

A heap fetch is quite an expensive procedure and it's impossible to tell now many of them would be required on the optimization stage.

So optimizer just tries to play safe and chooses the more predictable path.

share|improve this answer
1  
You copied my idea :) –  Igor Romanchenko Apr 23 '13 at 20:16
    
@IgorRomanchenko: actually there might be some variable to control estimated probablility of a heap fetch. Either that or the optimizer is not aware of index-only scans! –  Quassnoi Apr 23 '13 at 20:19
    
One question. Is optimizer aware that external disk merge will be used? From my case It does not quite seem so. Because it would have to consider that to be also very very expensive... –  clime Apr 23 '13 at 20:42

I guess the selectivity of column web_city.name is not very good. So the optimizer doesn't use index scan; You can see the distinct values of the column : as following:

--get the distinct valus

select tablename,attname,n_distinct from pg_stats where tablename='web_city' and attname='name';

If the output n_distinct is very low , then it imply that the selectivity is not good.

share|improve this answer
    
Your guess is completely wrong. Using index for GROUP BY operations has nothing to do with index selectivity. –  Igor Romanchenko Apr 24 '13 at 18:07
    
@IgorRomanchenko, sorry , I notice this now, it will use seq scan –  francs Apr 25 '13 at 1:42
    
I want to delete my answer. –  francs Apr 25 '13 at 1:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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