Re: speed 6.5 vs 7.0

Jeff MacDonald <jeff@pgsql.com> writes:
> now is it just me or is the cost on the dualpIII450 a little out of wack ?

You seem to be confusing EXPLAIN's cost numbers with reality ;-)

The EXPLAIN numbers are on an arbitrary scale that is only used to
measure the relative costs of different plans, so there's no attempt
to adjust it for the actual speed of different machines.  Given
identical Postgres versions, databases, and queries, you should get
identical EXPLAIN results no matter what hardware you're using.

The difference that you see here is entirely due to the differences
between the 6.5 and 7.0 cost estimators, and basically the answer is
that the 6.5 estimator is broken.  It's crediting the indexscan with
the selectivity of the whole WHERE clause, when in reality the only part
that the index can exploit is "gender = 0".  So although there may be
few tuples returned by the query, the indexscan will have to scan a lot
of tuples and hence should have a pretty high cost.

I'm actually pretty surprised that 7.0 will use an indexscan in
this situation at all.  Making the (perhaps incorrect?) assumption
that "gender = 0" selects about half the tuples, a plain sequential
scan ought to be faster.  Have you done a VACUUM ANALYZE on this table?
        regards, tom lane