Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37984=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1XquCi-0004Gj-Ra
 for pgsql-sql@arkaria.postgresql.org; Wed, 19 Nov 2014 01:37:08 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37984=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1XquCh-0003KD-SP
 for pgsql-sql@arkaria.postgresql.org; Wed, 19 Nov 2014 01:37:07 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <david.g.johnston@gmail.com>)
 id 1XquCf-0003K4-Pv
 for pgsql-sql@postgresql.org; Wed, 19 Nov 2014 01:37:05 +0000
Received: from [162.253.133.43] (helo=mwork.nabble.com)
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <david.g.johnston@gmail.com>) id 1XquCb-0002P5-UZ
 for pgsql-sql@postgresql.org; Wed, 19 Nov 2014 01:37:03 +0000
Received: from msam.nabble.com (unknown [162.253.133.85])
 by mwork.nabble.com (Postfix) with ESMTP id 638B6A7D4E5
 for <pgsql-sql@postgresql.org>; Tue, 18 Nov 2014 17:37:01 -0800 (PST)
Date: Tue, 18 Nov 2014 18:37:01 -0700 (MST)
From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-sql@postgresql.org
Message-ID: <1416361021024-5827453.post@n5.nabble.com>
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>
Subject: Re: slow sub-query problem
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Host-Lookup-Failed: Reverse DNS lookup failed for 162.253.133.43 (failed)
X-Pg-Spam-Score: 0.5 (/)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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