Re: how to improve perf of 131MM row table?

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