Re: Selective usage of index in planner/optimizer (Too conservative?)

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Something fishy about this --- why is the estimated
> number of rows
> different in the two cases (143073 vs 28254)? Did
> you redo VACUUM
> and/or ANALYZE in between?

I neither VACUUMed nor ANALYZEd between the 2
cases.
>
> > I am wondering why in test case #2 it did not
> use
> > an index scan, where as in case #3 it did.
>
> Probably because it knows "branch_cd=5" is more
> selective than
> "branch_cd=1". It would be useful to see the
> pg_stats entry for
> branch_cd.

Should I try altering the statistics? I tried
ANALYZE points(branch_cd);
but it still gave me the same results.

> > Its rather strange why "SELECT COUNT(*)...WHERE
> > branch_cd=1" uses sequential scan even though it
> just
> > comprises 5.3% of whole table...

What I mean is the table is rather large. (2
million rows) and I thought the planner would
automatically used an index to retrieve a small subset
(based on the percentage) of the large table.

> No, what's strange is that it's faster to use an
> indexscan for that.
> The table must be very nearly in order by branch_cd;
> have you clustered
> it recently?

I never clustered the table.

But prior to testing I dropped an index and create
a new one. Does dropping and creating index "confuse"
the planner even after a VACUUM ANALYZE?

I seem to notice this trend everytime I add a new
index to the table. It would slow down and the
performance would gradually improve in a day or two.

Should I try changing "cost" variables? I'm using
Pentium IV, with SCSI [RAID 5].

regards,

ludwig.

__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com