agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: daku.sandor@gmail.com
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: slow sub-query problem
Date: Wed, 19 Nov 2014 10:27:08 +0100
Message-ID: <653A2F06-93F6-4FA1-BBE8-9353BB427503@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>
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.g.johnston@gmail.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
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) latest in thread
Message-ID: <653A2F06-93F6-4FA1-BBE8-9353BB427503@gmail.com>
Permalink: ../653A2F06-93F6-4FA1-BBE8-9353BB427503@gmail.com/
Also on: postgresql.org/message-id/653A2F06-93F6-4FA1-BBE8-9353BB427503@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: daku.sandor@gmail.com
Subject: Re: slow sub-query problem
In-Reply-To: <653A2F06-93F6-4FA1-BBE8-9353BB427503@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