agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Tim Dudgeon <tdudgeon.ml@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: slow sub-query problem
Date: Thu, 20 Nov 2014 14:12:00 +0000
Message-ID: <546DF6B0.4040001@gmail.com> (raw)
In-Reply-To: <1416361021024-5827453.post@n5.nabble.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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

view thread (10+ messages)

Message-ID: <546DF6B0.4040001@gmail.com>
Permalink:  ../546DF6B0.4040001@gmail.com/
Also on:    postgresql.org/message-id/546DF6B0.4040001@gmail.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tdudgeon.ml@gmail.com
  Subject: Re: slow sub-query problem
  In-Reply-To: <546DF6B0.4040001@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox