Thread: speed 6.5 vs 7.0

hi folks, i have 2 machines 1 is a pIII 500 with 758 megs of ram,
and one is a dual pIII 450 with 512 megs of ram. both are running
freebsd 4.0.

the 500 is running pg 6.5.3, the dual450 is running pg 7.0 

here's the load averages -

pIII 500 - 12:39PM  up 24 days, 14:33, 25 users, load averages: 2.16, 2.19, 2.18
d540   - 12:44PM  up 6 days, 17:26, 4 users, load averages: 2.61, 2.13, 1.89

here is the query

SELECT gid FROM members
WHERE active = 't'
AND  (gender = 0       AND  (wantrstypemale LIKE '%Short Term%'               OR wantrstypemale LIKE '%Marriage%'
       OR wantrstypemale LIKE '%Long Term%'               OR wantrstypemale LIKE '%Penpal%'               OR
wantrstypemaleLIKE '%Activity Partner%')
 
)  ORDER BY created DESC;

and here are the explains

pIII 500NOTICE:  QUERY PLAN:
Sort  (cost=2.05 rows=1 width=12)  ->  Index Scan using mgenders on members  (cost=2.05 rows=1 width=12)
EXPLAIN

dual pIII 450
NOTICE:  QUERY PLAN:
Sort  (cost=305.01..305.01 rows=3 width=12)  ->  Index Scan using mgenders on members  (cost=0.00..304.98 rows=3
width=12)
EXPLAIN

now is it just me or is the cost on the dualpIII450 a little out of wack ?

jeff

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