query plan and the use of indexes

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.09rows=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.09rows=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.307rows=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.09rows=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