Re: voodoo index usage ;)

"D. Duccini" <duccini(at)backpack(dot)com> writes:
> # explain select * from radusage where account = 'someuser';
> NOTICE: QUERY PLAN:

> Seq Scan on radusage (cost=0.00..13870.80 rows=5674 width=50)

> and if i add in datetime (without effectively changing the semantic
> meaning of the search)

> # explain select * from radusage where account = 'someuser' and datetime >
> '1900-01-01';
> NOTICE: QUERY PLAN:

> Index Scan using idxradaccount on radusage (cost=0.00..15295.37 rows=5668
> width=50)

You could get more information by looking at the estimated cost of the
other alternative in each case (do SET ENABLE_SEQSCAN = OFF or SET
ENABLE_INDEXSCAN = OFF, respectively, to force the planner to choose the
other alternative). I bet you'll find that the estimated costs are
pretty close together. What's probably happening here is that the small
extra cost estimated for evaluating the "datetime > '1900-01-01'"
condition at each row is pushing the cost of the seqscan up to be more
than the cost of the indexscan. That extra cost gets charged for every
row in the table in the seqscan case, but only for those rows pulled
from the index in the indexscan case, so adding extra WHERE conditions
favors the indexscan case. Not by a lot, but evidently by enough in
this example.

regards, tom lane