query plan and the use of indexes - Mailing list pgsql-novice

Hello -

I'm trying to understand why the planner sometimes chooses to use an index and sometimes not.  In the query plans below, the part of the query being varied is the where clause involving fast_score_dom at the end of the select statement.  fast_score_dom is indexed with a b-tree.  The distribution of values in the field is highly skewed. The range is 1-100, with about 75% of the occurrences between 1 and 10.  There are about 22.6M rows in the table with an average length of about 500 bytes.

I don't understand why the use of a single inequality ( > or < ) causes the planner to filter on the condition, but the use of an interval (> and <) causes the planner to use the index.  I thought that a b-tree could always use an inequality, even if it is only one.  I altered the ranges on the inequalities, but that didn't seem to affect the decision. 

My underlying problem is sluggish response times, and I hope that understanding the planner better will let me organize the queries better.

Thanks for your help.

david

===================================


stx=# explain analyze select count(*) from domain_dom where alexa_ContactInfo___PhysicalAddress___State_dom = 'tx' and fast_score_dom = 50;
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2008.49..2008.50 rows=1 width=0) (actual time= 80.158..80.160 rows=1 loops=1)
   ->  Bitmap Heap Scan on domain_dom  (cost=2000.16..2008.18 rows=121 width=0) (actual time=78.959..79.841 rows=162 loops=1)
         Recheck Cond: ((alexa_contactinfo___physicaladdress___state_dom = 'tx'::text) AND (fast_score_dom = 50))
         ->  BitmapAnd  (cost=2000.16..2000.16 rows=2 width=0) (actual time=78.429..78.429 rows=0 loops=1)
               ->  Bitmap Index Scan on domain_alexa_state_dom  (cost=0.00..353.09 rows=1078 width=0) (actual time= 47.775..47.775 rows=52236 loops=1)
                     Index Cond: (alexa_contactinfo___physicaladdress___state_dom = 'tx'::text)
               ->  Bitmap Index Scan on domain_fast_score_dom  (cost=0.00..1646.76 rows=37818 width=0) (actual time=4.554..4.554 rows=7086 loops=1)
                     Index Cond: (fast_score_dom = 50)
 Total runtime: 81.096 ms
(9 rows)

Time: 94.000 ms
stx=# explain analyze select count(*) from domain_dom where alexa_ContactInfo___PhysicalAddress___State_dom = 'tx' and fast_score_dom > 50;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4644.83..4644.84 rows=1 width=0) (actual time=306.703..306.706 rows=1 loops=1)
   ->  Bitmap Heap Scan on domain_dom  (cost=355.10..4624.72 rows=8044 width=0) (actual time=89.808..304.484 rows=1079 loops=1)
         Recheck Cond: (alexa_contactinfo___physicaladdress___state_dom = 'tx'::text)
         Filter: (fast_score_dom > 50)
         ->  Bitmap Index Scan on domain_alexa_state_dom  (cost=0.00..353.09 rows=1078 width=0) (actual time=47.899..47.899 rows=52236 loops=1)
               Index Cond: (alexa_contactinfo___physicaladdress___state_dom = 'tx'::text)
 Total runtime: 307.760 ms
(7 rows)

Time: 328.000 ms
stx=# explain analyze select count(*) from domain_dom where alexa_ContactInfo___PhysicalAddress___State_dom = 'tx' and fast_score_dom > 90;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4644.83..4644.84 rows=1 width=0) (actual time=302.594..302.596 rows=1 loops=1)
   ->  Bitmap Heap Scan on domain_dom  (cost=355.10..4624.72 rows=8044 width=0) (actual time=269.649..302.513 rows=2 loops=1)
         Recheck Cond: (alexa_contactinfo___physicaladdress___state_dom = 'tx'::text)
         Filter: (fast_score_dom > 90)
         ->  Bitmap Index Scan on domain_alexa_state_dom  (cost=0.00..353.09 rows=1078 width=0) (actual time=47.918..47.918 rows=52236 loops=1)
               Index Cond: (alexa_contactinfo___physicaladdress___state_dom = 'tx'::text)
 Total runtime: 303.658 ms
(7 rows)

Time: 329.000 ms
stx=# explain analyze select count(*) from domain_dom where alexa_ContactInfo___PhysicalAddress___State_dom = 'tx' and fast_score_dom > 90 and fast_score_dom < 100;
                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2304.17..2304.18 rows=1 width=0) (actual time=71.894..71.896 rows=1 loops=1)
   ->  Bitmap Heap Scan on domain_dom  (cost=2283.79..2303.86 rows=121 width=0) (actual time=71.843..71.853 rows=2 loops=1)
         Recheck Cond: ((alexa_contactinfo___physicaladdress___state_dom = 'tx'::text) AND (fast_score_dom > 90) AND (fast_score_dom < 100))
         ->  BitmapAnd  (cost=2283.79..2283.79 rows=5 width=0) (actual time= 71.415..71.415 rows=0 loops=1)
               ->  Bitmap Index Scan on domain_alexa_state_dom  (cost=0.00..353.09 rows=1078 width=0) (actual time=48.054..48.054 rows=52236 loops=1)
                     Index Cond: (alexa_contactinfo___physicaladdress___state_dom = 'tx'::text)
               ->  Bitmap Index Scan on domain_fast_score_dom  (cost=0.00..1930.39 rows=113455 width=0) (actual time=0.140..0.140 rows=131 loops=1)
                     Index Cond: ((fast_score_dom > 90) AND (fast_score_dom < 100))
 Total runtime: 72.904 ms
(9 rows)

Time: 94.000 ms
stx=# explain analyze select count(*) from domain_dom where alexa_ContactInfo___PhysicalAddress___State_dom = 'tx' and fast_score_dom > 10 and fast_score_dom < 100;
                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2304.17..2304.18 rows=1 width=0) (actual time=286851.236..286851.238 rows=1 loops=1)
   ->  Bitmap Heap Scan on domain_dom  (cost=2283.79..2303.86 rows=121 width=0) (actual time=286266.662..286769.573 rows=48021 loops=1)
         Recheck Cond: ((alexa_contactinfo___physicaladdress___state_dom = 'tx'::text) AND (fast_score_dom > 10) AND (fast_score_dom < 100))
         ->  BitmapAnd  (cost=2283.79..2283.79 rows=5 width=0) (actual time=286264.337..286264.337 rows=0 loops=1)
               ->  Bitmap Index Scan on domain_alexa_state_dom  (cost=0.00..353.09 rows=1078 width=0) (actual time=47.946..47.946 rows=52236 loops=1)
                     Index Cond: (alexa_contactinfo___physicaladdress___state_dom = 'tx'::text)
               ->  Bitmap Index Scan on domain_fast_score_dom  (cost=0.00..1930.39 rows=113455 width=0) (actual time= 286172.307..286172.307 rows=7251484 loops=1)
                     Index Cond: ((fast_score_dom > 10) AND (fast_score_dom < 100))
 Total runtime: 286852.339 ms
(9 rows)

Time: 286844.000 ms
stx=# explain analyze select count(*) from domain_dom where alexa_ContactInfo___PhysicalAddress___State_dom = 'tx' and  fast_score_dom < 10;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4644.83..4644.84 rows=1 width=0) (actual time=325.753..325.755 rows=1 loops=1)
   ->  Bitmap Heap Scan on domain_dom  (cost=355.10..4624.72 rows=8044 width=0) (actual time=95.283..319.123 rows=3329 loops=1)
         Recheck Cond: (alexa_contactinfo___physicaladdress___state_dom = 'tx'::text)
         Filter: (fast_score_dom < 10)
         ->  Bitmap Index Scan on domain_alexa_state_dom  (cost=0.00..353.09 rows=1078 width=0) (actual time=50.832..50.832 rows=52236 loops=1)
               Index Cond: (alexa_contactinfo___physicaladdress___state_dom = 'tx'::text)
 Total runtime: 327.018 ms
(7 rows)

Time: 344.000 ms