agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: slow sub-query problem
Date: Tue, 18 Nov 2014 18:37:01 -0700 (MST)
Message-ID: <1416361021024-5827453.post@n5.nabble.com> (raw)
In-Reply-To: <546B09F3.90609@gmail.com>
References: <546A3FBA.9020901@gmail.com>
	<1416249886486-5827275.post@n5.nabble.com>
	<CAKFQuwbaGBD=QMUqb=51gawxK8G6NOiGzm6-fT2efX0kHCpDtw@mail.gmail.com>
	<546B09F3.90609@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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)  latest in thread

Message-ID: <1416361021024-5827453.post@n5.nabble.com>
Permalink:  ../1416361021024-5827453.post@n5.nabble.com/
Also on:    postgresql.org/message-id/1416361021024-5827453.post@n5.nabble.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: david.g.johnston@gmail.com
  Subject: Re: slow sub-query problem
  In-Reply-To: <1416361021024-5827453.post@n5.nabble.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