Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XqebM-0002Mk-Gx for pgsql-sql@arkaria.postgresql.org; Tue, 18 Nov 2014 08:57:32 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XqebM-0006xy-1Z for pgsql-sql@arkaria.postgresql.org; Tue, 18 Nov 2014 08:57:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XqebL-0006xs-7U for pgsql-sql@postgresql.org; Tue, 18 Nov 2014 08:57:31 +0000 Received: from mail-wi0-x236.google.com ([2a00:1450:400c:c05::236]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XqebH-000120-Es for pgsql-sql@postgresql.org; Tue, 18 Nov 2014 08:57:30 +0000 Received: by mail-wi0-f182.google.com with SMTP id h11so11749578wiw.9 for ; Tue, 18 Nov 2014 00:57:26 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:subject:references :in-reply-to:content-type; bh=5iPpbHZMqgkJjpB3e5NEydz69wDWKZCStjOpOXu1ypk=; b=Q/8OuqNCxx8XqXsobEAAgXS+dYJsgbyckNTXQ3Edezxu5rYwLqpLWkqzOgncyPDvGC 1Xe6KGCLjSJToplTn6c1BpjgibX2GDmSoJ0JN/AgBz2T1NpvUGAj5W1fLvi2KnkCR7Ew EXgKIEmVAL42iK9ArWzr6S7JCvHLPyH9m+5wIqMESUKtOqY63pGUSXD5qKIcWAoCH4PE h7puL7Lmbl7ZPZkoMbT1keuiloPlX4/Z9fEO16um9h087yXqn8sGC/cAUNdzOQGXtwKH ET2c5l1ZyyMAan4JRamwyho2mIAPb0ifYY03Ayj+FEOVz/3fqOr29f1cRqHoedORTeJf bA7Q== X-Received: by 10.194.203.105 with SMTP id kp9mr18067718wjc.81.1416301046044; Tue, 18 Nov 2014 00:57:26 -0800 (PST) Received: from [192.168.1.64] (host217-43-225-174.range217-43.btcentralplus.com. [217.43.225.174]) by mx.google.com with ESMTPSA id wa10sm50233363wjc.8.2014.11.18.00.57.24 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Tue, 18 Nov 2014 00:57:25 -0800 (PST) Message-ID: <546B09F3.90609@gmail.com> Date: Tue, 18 Nov 2014 08:57:23 +0000 From: Tim Dudgeon 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> <546A48C2.2030907@gmail.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------080909050500000809040801" X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------080909050500000809040801 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Dave, thanks for the suggestion. I was trying to work on that basis. Eventually I got this that works quite well: 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) ; which has this plan. "Hash Join (cost=4376.38..6539.42 rows=43 width=648) (actual time=467.265..795.887 rows=381 loops=1)" " Hash Cond: (t2.id = t1.id )" " -> Nested Loop (cost=1092.16..1352.77 rows=507201 width=4) (actual time=0.807..84.228 rows=173867 loops=1)" " -> HashAggregate (cost=1091.73..1091.75 rows=2 width=4) (actual time=0.779..0.897 rows=366 loops=1)" " Group Key: structure_props.structure_id" " -> Index Scan using idx_sp_property_id on structure_props (cost=0.43..1090.77 rows=382 width=4) (actual time=0.032..0.592 rows=369 loops=1)" " Index Cond: (property_id = 643413)" " -> Index Scan using idx_sp_structure_id on structure_props t2 (cost=0.43..127.34 rows=317 width=8) (actual time=0.010..0.172 rows=475 loops=366)" " Index Cond: (structure_id = structure_props.structure_id)" " -> Hash (cost=3269.89..3269.89 rows=1146 width=648) (actual time=464.458..464.458 rows=811892 loops=1)" " Buckets: 1024 Batches: 32 (originally 1) Memory Usage: 4097kB" " -> Index Scan using idx_sp_property_id on structure_props t1 (cost=0.44..3269.89 rows=1146 width=648) (actual time=0.033..231.895 rows=811892 loops=1)" " Index Cond: (property_id = ANY ('{1,643413,1106201}'::integer[]))" "Planning time: 0.885 ms" It looks a little strange to me, but it works much better. Tim On 17/11/2014 19:19, David Johnston wrote: > Please reply to the list... > > In short... > > tablea as t1 join tablea as t2 on t1.id = t2.id > > > A natural key prevents duplicate real data which a serially generated > made up key does not. > > David J. > > On Monday, November 17, 2014, Tim Dudgeon > wrote: > > > On 17/11/2014 18:44, David G Johnston wrote: > > Tim Dudgeon wrote > > All relevant columns are indexed and using PostgreSQL 9.4. > Any clues how to re-write it to avoid the slow sub-query. > > Try using an actual join instead of a subquery. You will have > to provide > aliases and then setup the where clause appropriately. > > I'm trying to go in that direction but in the query is entirely > within one table, so I need to join the table to itself? I've been > trying this but not getting it to work yet. > > > I am reading the query correctly in that the repeated > reference to 643413 is > redundant? > > In this example its sort of redundant, but in a real world case > the query for structure_id and property_id are independent and may > have nothing in common. > > The lack of a defined natural primary key makes blind reasoning > difficult. > > > The id column is the primary key. > > Tim > > > David J. > > > > > > -- > View this message in context: > http://postgresql.nabble.com/slow-sub-query-problem-tp5827273p5827275.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > > --------------080909050500000809040801 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Dave,

thanks for the suggestion. I was trying to work on that basis.
Eventually I got this that works quite well:


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)
;

which has this plan.

"Hash Join  (cost=4376.38..6539.42 rows=43 width=648) (actual time=467.265..795.887 rows=381 loops=1)"
"  Hash Cond: (t2.id = t1.id)"
"  ->  Nested Loop  (cost=1092.16..1352.77 rows=507201 width=4) (actual time=0.807..84.228 rows=173867 loops=1)"
"        ->  HashAggregate  (cost=1091.73..1091.75 rows=2 width=4) (actual time=0.779..0.897 rows=366 loops=1)"
"              Group Key: structure_props.structure_id"
"              ->  Index Scan using idx_sp_property_id on structure_props  (cost=0.43..1090.77 rows=382 width=4) (actual time=0.032..0.592 rows=369 loops=1)"
"                    Index Cond: (property_id = 643413)"
"        ->  Index Scan using idx_sp_structure_id on structure_props t2  (cost=0.43..127.34 rows=317 width=8) (actual time=0.010..0.172 rows=475 loops=366)"
"              Index Cond: (structure_id = structure_props.structure_id)"
"  ->  Hash  (cost=3269.89..3269.89 rows=1146 width=648) (actual time=464.458..464.458 rows=811892 loops=1)"
"        Buckets: 1024  Batches: 32 (originally 1)  Memory Usage: 4097kB"
"        ->  Index Scan using idx_sp_property_id on structure_props t1  (cost=0.44..3269.89 rows=1146 width=648) (actual time=0.033..231.895 rows=811892 loops=1)"
"              Index Cond: (property_id = ANY ('{1,643413,1106201}'::integer[]))"
"Planning time: 0.885 ms"


It looks a little strange to me, but it works much better.

Tim



On 17/11/2014 19:19, David Johnston wrote:
Please reply to the list...

In short...

tablea as t1 join tablea as t2 on t1.id = t2.id

A natural key prevents duplicate real data which a serially generated made up key does not.

David J.

On Monday, November 17, 2014, Tim Dudgeon <tdudgeon.ml@gmail.com> wrote:

On 17/11/2014 18:44, David G Johnston wrote:
Tim Dudgeon wrote
All relevant columns are indexed and using PostgreSQL 9.4.
Any clues how to re-write it to avoid the slow sub-query.
Try using an actual join instead of a subquery.  You will have to provide
aliases and then setup the where clause appropriately.
I'm trying to go in that direction but in the query is entirely within one table, so I need to join the table to itself? I've been trying this but not getting it to work yet.


I am reading the query correctly in that the repeated reference to 643413 is
redundant?
In this example its sort of redundant, but in a real world case the query for structure_id and property_id are independent and may have nothing in common.

The lack of a defined natural primary key makes blind reasoning
difficult.

The id column is the primary key.

Tim

David J.





--
View this message in context: http://postgresql.nabble.com/slow-sub-query-problem-tp5827273p5827275.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.




--------------080909050500000809040801--