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

Ludwig Lim <lud_nowhere_man@yahoo.com> writes:
> NOTICE:  QUERY PLAN:

> Aggregate  (cost=119123.54..119123.54 rows=1 width=0)
> (actual time=811.08..811.0
> 8 rows=1 loops=1)
>   ->  Index Scan using idx_monthly_branch on points
> (cost=0.00..1187
> 65.86 rows=143073 width=0) (actual time=0.19..689.75
> rows=136790 loops=1)
> Total runtime: 811.17 msec

> NOTICE:  QUERY PLAN:

> Aggregate  (cost=62752.34..62752.34 rows=1 width=0)
> (actual time=3593.93..3593.9
> 3 rows=1 loops=1)
>   ->  Seq Scan on points  (cost=0.00..62681.70
> rows=28254 width=0) (a
> ctual time=0.33..3471.54 rows=136790 loops=1)
> Total runtime: 3594.01 msec

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 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.

>    Its rather strange why "SELECT COUNT(*)...WHERE
> branch_cd=1" uses sequential scan even though it just
> comprises 5.3% of whole 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?

            regards, tom lane