Message-ID: <546DF6B0.4040001@gmail.com>
Date: Thu, 20 Nov 2014 14:12:00 +0000
From: Tim Dudgeon <tdudgeon.ml@gmail.com>
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9;
 rv:24.0) Gecko/20100101 Thunderbird/24.6.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: slow sub-query problem
References: <546A3FBA.9020901@gmail.com>
 <1416249886486-5827275.post@n5.nabble.com>
 <CAKFQuwbaGBD=QMUqb=51gawxK8G6NOiGzm6-fT2efX0kHCpDtw@mail.gmail.com>
 <546B09F3.90609@gmail.com> <1416361021024-5827453.post@n5.nabble.com>
In-Reply-To: <1416361021024-5827453.post@n5.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

I tried them all out.

Original query: 17039ms
Simple join: 889ms
Join with SELECT: 1302ms (799ms without DISTINCT which I don't think is 
needed here)
Using INTERSECT: 1454ms (1474 without DISTINCT)

So with the current data the simple join and the Join with SELECT but no 
DISTINCT are the best.

Thanks for your help with this.

Tim


On 19/11/2014 01:37, David G Johnston 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql