Query Optimizer::7.3 to 7.4:: in() problems

We have noted a significant (seeming) slow down in the query optimizer from
7.3. to 7.4 as it relates to in() clauses. I reviewed some of Tom Lane's
comments regarding the fact that it was actually optimized specifically - so
our curiosity has been roused.

I am wondering if there is something either in our SQL, or is there some
sort of bug that we need to be aware of? Should we preempt the optimizer
and break out the in clause in to joins, adding the distinct clauses - or in
theory - should the optimizers implementation be satisfactory?

Below is each query against 7.4., one with the select clause in the in()
and the other with what would be the ids that come from the select. The
latter is much quicker (obviously), and as stated, 7.3. is fine.

Also, for reference, see page 19 of Tom's Paper here:
http://conferences.oreillynet.com/presentations/os2003/lane_tom.pdf

-the slow-down only occurs on postgres v 7.4, on 7.3 there is no performance
problem.

-it looks as if it has something to do with the planner and the way that it
handles nested selects; maybe there was some sort of configuration change on
the new version?

Thanks for your help,

Gary

select
supplier.name_supplier,
uw.name_first as worker_first,
uw.name_last as worker_last,
uw.address_email as worker_email,
uw.phone as worker_phone,
uw.city as worker_city,
uw.state as worker_state,
uw.zip as worker_zip,
resume.keyp_resumeid as resumeid,
resume.keyf_workerid as workerid,
resume.keyf_supplierid as supplierid,
resume.timestamp_created as resume_timestamp,
resume.name_first as resume_name_first,
resume.name_last as resume_name_last,
resume.education,
resume.skills,
resume.experience,
resume.ssn,
resume.birthday_month,
resume.birthday_day,
resume.keyf_createdby,
resume.source,
resume.sex,
resume.ethnicity,
resume.resume_name
from resume
left join supplier on supplier.keyp_supplierid = resume.keyf_supplierid
left join users uw on uw.keyf_workerid = resume.keyf_workerid
where resume.keyp_resumeid in ( select keyf_resumeid from order_resume where
keyf_orderid = 51972 )

Explain output:

QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Hash IN Join (cost=17206.13..130689.47 rows=307 width=636) (actual
time=92663.493..92937.700 rows=9 loops=1)
Hash Cond: ("outer".keyp_resumeid = "inner".keyf_resumeid)
-> Merge Right Join (cost=15968.92..111132.41 rows=6088284 width=636)
(actual time=1410.481..84968.348 rows=4214415 loops=1)
Merge Cond: ("outer".keyf_workerid = "inner".keyf_workerid)
-> Index Scan using users_workerid_idx on users uw
(cost=0.00..3762.70 rows=30609 width=228) (actual time=0.172..185.773
rows=30609 loops=1)
-> Sort (cost=15968.92..16068.38 rows=39781 width=412) (actual
time=1410.256..18131.555 rows=4214415 loops=1)
Sort Key: resume.keyf_workerid
-> Hash Left Join (cost=36.19..4339.70 rows=39781
width=412)
(actual time=4.699..682.843 rows=39780 loops=1)
Hash Cond: ("outer".keyf_supplierid =
"inner".keyp_supplierid)
-> Seq Scan on resume (cost=0.00..3706.80 rows=39780
width=380) (actual time=0.044..184.580 rows=39780 loops=1)
-> Hash (cost=34.15..34.15 rows=815 width=36) (actual
time=4.571..4.571 rows=0 loops=1)
-> Seq Scan on supplier (cost=0.00..34.15
rows=815 width=36) (actual time=0.048..2.902 rows=815 loops=1)
-> Hash (cost=1236.32..1236.32 rows=355 width=4) (actual
time=0.455..0.455 rows=0 loops=1)
-> Index Scan using order_resume_orderid on order_resume
(cost=0.00..1236.32 rows=355 width=4) (actual time=0.188..0.417 rows=9
loops=1)
Index Cond: (keyf_orderid = 51972)
Total runtime: 92947.979 ms
(16 rows)

select
supplier.name_supplier,
uw.name_first as worker_first,
uw.name_last as worker_last,
uw.address_email as worker_email,
uw.phone as worker_phone,
uw.city as worker_city,
uw.state as worker_state,
uw.zip as worker_zip,
resume.keyp_resumeid as resumeid,
resume.keyf_workerid as workerid,
resume.keyf_supplierid as supplierid,
resume.timestamp_created as resume_timestamp,
resume.name_first as resume_name_first,
resume.name_last as resume_name_last,
resume.education,
resume.skills,
resume.experience,
resume.ssn,
resume.birthday_month,
resume.birthday_day,
resume.keyf_createdby,
resume.source,
resume.sex,
resume.ethnicity,
resume.resume_name
from resume
left join supplier on supplier.keyp_supplierid = resume.keyf_supplierid
left join users uw on uw.keyf_workerid = resume.keyf_workerid
where resume.keyp_resumeid in (
56842,56892,57000,55677,55843,56781,57062,55807,56631);

Explain Output QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Right Join (cost=108.02..3970.20 rows=1531 width=636)
Merge Cond: ("outer".keyf_workerid = "inner".keyf_workerid)
-> Index Scan using users_workerid_idx on users uw (cost=0.00..3762.70
rows=30609 width=228)
-> Sort (cost=108.02..108.04 rows=10 width=412)
Sort Key: resume.keyf_workerid
-> Nested Loop Left Join (cost=0.00..107.85 rows=10 width=412)
-> Index Scan using resume_pkey, resume_pkey, resume_pkey,
resume_pkey, resume_pkey, resume_pkey, resume_pkey, resume_pkey, resume_pkey
on resume (cost=0.00..54.30 rows=9 width=380)
Index Cond: ((keyp_resumeid = 56842) OR (keyp_resumeid
=
56892) OR (keyp_resumeid = 57000) OR (keyp_resumeid = 55677) OR
(keyp_resumeid = 55843) OR (keyp_resumeid = 56781) OR (keyp_resumeid =
57062)
OR (keyp_resumeid = 55807) OR (keyp_resumeid = 56631))
-> Index Scan using supplier_pkey on supplier
(cost=0.00..5.94 rows=1 width=36)
Index Cond: (supplier.keyp_supplierid =
"outer".keyf_supplierid)
(10 rows)

Richard Seiersen
rich67dev(at)hotmail(dot)com

_________________________________________________________________
Make your home warm and cozy this winter with tips from MSN House & Home.
http://special.msn.com/home/warmhome.armx