Re: slow sub-query problem

Slightly off:

I prefer "exists" to "join" if it's possible while on the list I almost never see any answer that uses "exists". Is my exists fixation is some kind of bad practice?

Sandor Daku

> On 19 Nov 2014, at 02:37, David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> 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.
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql