Re: slow sub-query problem

Tim Dudgeon wrote
> SELECT t1.id, t1.structure_id, t1.batch_id,
> t1.property_id, t1.property_data
> FROM chemcentral.structure_props t1
> JOIN chemcentral.structure_props t2 ON t1.id = t2.id
> WHERE t2.structure_id IN (SELECT structure_id FROM
> chemcentral.structure_props WHERE property_id = 643413)
> AND t1.property_id IN (1, 643413, 1106201)
> ;

What about:

SELECT t1.id, t1.structure_id, t1.batch_id, t1.property_id, t1.property_data
FROM chemcentral.structure_props t1
JOIN (
SELECT DISTINCT super.id FROM chemcentral.structure_props super
WHERE super.structure_id IN (
SELECT sub.structure_id
FROM chemcentral.structure_props sub
WHERE sub.property_id = 643413
)
) t2 ON (t1.id = t2.id)
WHERE t1.property_id IN (1, 643413, 1106201)
;

?

I do highly suggest using column table prefixes everywhere in this kind of
query...

Also, AND == INTERSECT so:

SELECT ... FROM chemcentral.structure_props WHERE property_id IN
(1,643413,1106201)
INTERSECT DISTINCT
SELECT ... FROM chemcentral.structure_props WHERE structure_id IN (SELECT
... WHERE property_id = 643413)

You can even use CTE/WITH expressions and give these subqueries meaningful
names.

David J.

--
View this message in context: http://postgresql.nabble.com/slow-sub-query-problem-tp5827273p5827453.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.