> Hi,
>
> I've found another one of these performance problems in the benchmark,
> related to another ignored index.
>
> The whole thing works perfectly after a VACUUM ANALYZE on the
> table.
>
> IMHO this is somewhat non-optimal. In the absence of information
> to the contrary, PostgreSQL should default to using an index if
> it might be appropriate, not ignore it.
This is an interesting idea. So you are saying that if a column has no
vacuum analyze statistics, assume it is unique? Or are you talking
about a table that has never been vacuumed? Then we assume it is a
large table. Interesting. It would help some queries, but hurt others.
We have gone around and around on what the default stats should be.
Tom Lane can comment on this better than I can.
>
> Related to this:
>
> test=# explain select id from bench1 order by id;
> NOTICE: QUERY PLAN:
>
> Sort (cost=38259.21..38259.21 rows=300000 width=4)
> -> Seq Scan on bench1 (cost=0.00..6093.00 rows=300000 width=4)
>
> EXPLAIN
>
> The basic idea to speed this one up (a lot...) would be to walk the index.
>
> This is _after_ ANALYZE, of course.
But you are grabbing the whole table. Our indexes are separate files.
The heap is unordered, meaning a sequential scan and order by is usually
faster than an index walk unless there is a restrictive WHERE clause.
Thanks for the tip about needing an index on pg_index. That will be in
7.1. I remember previous crashme rounds did bring up some good info for
us, like the fact older releases couldn't handle trailing comments from
perl.
-- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
