From: AJ Weber <aweber@comcast.net>
To: Shaun Thomas <sthomas@optionshouse.com>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: how to improve perf of 131MM row table?
Date: Thu, 26 Jun 2014 14:01:59 -0400
Message-ID: <53AC6017.5010307@comcast.net> (raw)
In-Reply-To: <53AC491A.8090207@optionshouse.com>
References: <53AB2CB1.8080201@comcast.net>
<53AB35CC.10202@optionshouse.com>
<1801a5d4-c3f1-4c0c-9c2c-ffb9d7f723a3@email.android.com>
<53AB4551.4070207@optionshouse.com>
<53AC1F6E.4000700@comcast.net>
<CAGTBQpZNueaUF8=ZZRZW+v4o5Vfdg0ssJdqgT_sMnH7R0JfqZg@mail.gmail.com>
<53AC3E8D.8040106@comcast.net>
<53AC40CB.9020006@comcast.net>
<CAGTBQpZWsKF=Qjf_nrhoxGDhSda-G0HnRgRvwAZp12ENcEK-5g@mail.gmail.com>
<53AC491A.8090207@optionshouse.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 6/26/2014 12:23 PM, Shaun Thomas wrote:
> On 06/26/2014 11:19 AM, Claudio Freire wrote:
>
>> Try changing node_id in (...) into node.id in (...)
>
That looks much better to my untrained eye! (Am I right?)
Nested Loop (cost=218.29..21305.47 rows=53480 width=187) (actual
time=42.347..
43.617 rows=1071 loops=1)
Buffers: shared hit=487 read=15
-> Bitmap Heap Scan on alf_node node (cost=218.29..423.40 rows=51
width=16)
(actual time=42.334..42.413 rows=51 loops=1)
Recheck Cond: (id = ANY
('{175769,175771,175781,175825,175881,175893,17
5919,175932,175963,175999,176022,176079,176099,176115,176118,176171,176181,17621
7,176220,176243,176283,176312,176326,176335,176377,176441,176444,176475,176530,1
76570,176623,176674,176701,176730,176748,176763,176771,176808,176836,176851,1768
64,176881,176929,176945,176947,176960,177006,177039,177079,177131,177144}'::bigi
nt[]))
Buffers: shared hit=159 read=15
-> Bitmap Index Scan on alf_node_pkey (cost=0.00..218.28
rows=51 widt
h=0) (actual time=42.326..42.326 rows=51 loops=1)
Index Cond: (id = ANY
('{175769,175771,175781,175825,175881,17589
3,175919,175932,175963,175999,176022,176079,176099,176115,176118,176171,176181,1
76217,176220,176243,176283,176312,176326,176335,176377,176441,176444,176475,1765
30,176570,176623,176674,176701,176730,176748,176763,176771,176808,176836,176851,
176864,176881,176929,176945,176947,176960,177006,177039,177079,177131,177144}'::
bigint[]))
Buffers: shared hit=146 read=7
-> Index Scan using fk_alf_nprop_n on alf_node_properties prop
(cost=0.00..
396.34 rows=1049 width=179) (actual time=0.006..0.013 rows=21 loops=51)
Index Cond: (prop.node_id = node.id)
Buffers: shared hit=328
Total runtime: 43.747 ms
AM I RIGHT? (That it's much better -- I thank Claudio and Shaun for
being right!)
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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-performance@postgresql.org
Cc: aweber@comcast.net, sthomas@optionshouse.com, klaussfreire@gmail.com
Subject: Re: how to improve perf of 131MM row table?
In-Reply-To: <53AC6017.5010307@comcast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox