Message-ID: <53AC6017.5010307@comcast.net>
Date: Thu, 26 Jun 2014 14:01:59 -0400
From: AJ Weber <aweber@comcast.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.3.0
MIME-Version: 1.0
To: Shaun Thomas <sthomas@optionshouse.com>,
 Claudio Freire <klaussfreire@gmail.com>
CC: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: how to improve perf of 131MM row table?
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>
In-Reply-To: <53AC491A.8090207@optionshouse.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


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