title: adding more indexes to postgres causes "out of shared memory" error
body: <p>i have a rather complex query that i'm trying to optimize in postgres 9.2 - the explain analyze gives <a href="http://explain.depesz.com/s/NL83" rel="nofollow">this plan (explain.depesz.com)</a>:</p>
  
  <pre><code>        Merge Right Join  (cost=194965639.35..211592151.26 rows=420423258 width=616) (actual time=15898.283..15920.603 rows=17 loops=1)
         Merge Cond: ((((p.context -&gt; 'device'::text)) = ((s.context -&gt; 'device'::text))) AND (((p.context -&gt; 'physical_port'::text)) = ((s.context -&gt; 'physical_port'::text))))
         -&gt;  Sort  (cost=68925.49..69073.41 rows=59168 width=393) (actual time=872.289..877.818 rows=39898 loops=1)
               Sort Key: ((p.context -&gt; 'device'::text)), ((p.context -&gt; 'physical_port'::text))
               Sort Method: quicksort  Memory: 27372kB
               -&gt;  Seq Scan on ports__status p  (cost=0.00..64235.68 rows=59168 width=393) (actual time=0.018..60.931 rows=41395 loops=1)
         -&gt;  Materialize  (cost=194896713.86..199620346.93 rows=284223403 width=299) (actual time=15023.710..15024.779 rows=17 loops=1)
               -&gt;  Merge Left Join  (cost=194896713.86..198909788.42 rows=284223403 width=299) (actual time=15023.705..15024.765 rows=17 loops=1)
                     Merge Cond: ((((s.context -&gt; 'device'::text)) = ((l1.context -&gt; 'device'::text))) AND (((s.context -&gt; 'physical_port'::text)) = ((l1.context -&gt; 'physical_port'::text))))
                     -&gt;  Sort  (cost=194894861.42..195605419.92 rows=284223403 width=224) (actual time=14997.225..14997.230 rows=17 loops=1)
                           Sort Key: ((s.context -&gt; 'device'::text)), ((s.context -&gt; 'physical_port'::text))
                           Sort Method: quicksort  Memory: 33kB
                           -&gt;  GroupAggregate  (cost=100001395.98..122028709.71 rows=284223403 width=389) (actual time=14997.120..14997.186 rows=17 loops=1)
                                 -&gt;  Sort  (cost=100001395.98..100711954.49 rows=284223403 width=389) (actual time=14997.080..14997.080 rows=17 loops=1)
                                       Sort Key: ((d.context -&gt; 'hostname'::text)), ((a.context -&gt; 'ip_address'::text)), ((a.context -&gt; 'mac_address'::text)), ((s.context -&gt; 'device'::text)), ((s.context -&gt; 'physical_port'::text)), s.created_at, s.updated_at, d.created_at, d.updated_at
                                       Sort Method: quicksort  Memory: 33kB
                                       -&gt;  Merge Join  (cost=339026.99..9576678.30 rows=284223403 width=389) (actual time=14996.710..14996.749 rows=17 loops=1)
                                             Merge Cond: (((a.context -&gt; 'mac_address'::text)) = ((s.context -&gt; 'mac_address'::text)))
                                             -&gt;  Sort  (cost=15038.32..15136.00 rows=39072 width=255) (actual time=23.556..23.557 rows=1 loops=1)
                                                   Sort Key: ((a.context -&gt; 'mac_address'::text))
                                                   Sort Method: quicksort  Memory: 25kB
                                                   -&gt;  Hash Join  (cost=471.88..12058.33 rows=39072 width=255) (actual time=13.482..23.548 rows=1 loops=1)
                                                         Hash Cond: ((a.context -&gt; 'ip_address'::text) = (d.context -&gt; 'ip_address'::text))
                                                         -&gt;  Seq Scan on arps__arps a  (cost=0.00..8132.39 rows=46239 width=157) (actual time=0.007..11.191 rows=46259 loops=1)
                                                         -&gt;  Hash  (cost=469.77..469.77 rows=169 width=98) (actual time=0.035..0.035 rows=1 loops=1)
                                                               Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                                               -&gt;  Bitmap Heap Scan on ipam__dns d  (cost=9.57..469.77 rows=169 width=98) (actual time=0.023..0.023 rows=1 loops=1)
                                                                     Recheck Cond: ((context -&gt; 'hostname'::text) = 'zglast-oracle03.slac.stanford.edu'::text)
                                                                     -&gt;  Bitmap Index Scan on ipam__dns_hostname_index  (cost=0.00..9.53 rows=169 width=0) (actual time=0.017..0.017 rows=1 loops=1)
                                                                           Index Cond: ((context -&gt; 'hostname'::text) = 'blah'::text)
                                             -&gt;  Sort  (cost=323988.67..327625.84 rows=1454870 width=134) (actual time=14973.118..14973.120 rows=18 loops=1)
                                                   Sort Key: ((s.context -&gt; 'mac_address'::text))
                                                   Sort Method: external sort  Disk: 214176kB
                                                   -&gt;  Result  (cost=0.00..175064.84 rows=1454870 width=134) (actual time=0.016..1107.604 rows=1265154 loops=1)
                                                         -&gt;  Append  (cost=0.00..175064.84 rows=1454870 width=134) (actual time=0.013..796.578 rows=1265154 loops=1)
                                                               -&gt;  Seq Scan on spanning_tree__neighbour s  (cost=0.00..0.00 rows=1 width=98) (actual time=0.000..0.000 rows=0 loops=1)
                                                                     Filter: ((context -&gt; 'physical_port'::text) IS NOT NULL)
                                                               -&gt;  Seq Scan on spanning_tree__neighbour__vlan38 s  (cost=0.00..469.32 rows=1220 width=129) (actual time=0.011..1.019 rows=823 loops=1)
                                                                     Filter: ((context -&gt; 'physical_port'::text) IS NOT NULL)
                                                                     Rows Removed by Filter: 403
                                                               -&gt;  Seq Scan on spanning_tree__neighbour__vlan3 s  (cost=0.00..270.20 rows=1926 width=139) (actual time=0.017..0.971 rows=1882 loops=1)
                                                                     Filter: ((context -&gt; 'physical_port'::text) IS NOT NULL)
                                                                     Rows Removed by Filter: 54
                                                               -&gt;  Seq Scan on spanning_tree__neighbour__vlan466 s  (cost=0.00..131.85 rows=306 width=141) (actual time=0.032..0.340 rows=276 loops=1)
                                                                     Filter: ((context -&gt; 'physical_port'::text) IS NOT NULL)
                                                                     Rows Removed by Filter: 32
                                                               -&gt;  Seq Scan on spanning_tree__neighbour__vlan465 s  (cost=0.00..208.57 rows=842 width=142) (actual time=0.005..0.622 rows=768 loops=1)
                                                                     Filter: ((context -&gt; 'physical_port'::text) IS NOT NULL)
                                                                     Rows Removed by Filter: 78
                                                               -&gt;  Seq Scan on spanning_tree__neighbour__vlan499 s  (cost=0.00..245.04 rows=481 width=142) (actual time=0.017..0.445 rows=483 loops=1)
                                                                     Filter: ((context -&gt; 'physical_port'::text) IS NOT NULL)
                                                               -&gt;  Seq Scan on spanning_tree__neighbour__vlan176 s  (cost=0.00..346.36 rows=2576 width=131) (actual time=0.008..1.443 rows=2051 loops=1)
                                                                     Filter: ((context -&gt; 'physical_port'::text) IS NOT NULL)
                                                                     Rows Removed by Filter: 538
  </code></pre>
  
  <p>i'm a bit of a novice at reading the plan, but i think it's all down to the fact that i have the table <code>spanning_tree__neighbour</code> (which i've partitioned into numerous 'vlan' tables). as you can see it's performing a seq scan.</p>
  
  <p>so i write a quick and dirty bash script to create indexes for the child tables:</p>
  
  <pre><code>create index spanning_tree__neighbour__vlan1_physical_port_index ON spanning_tree__neighbour__vlan1((context-&gt;'physical_port')) wHERE ((context-&gt;'physical_port') IS NOT NULL);
  create index spanning_tree__neighbour__vlan2_physical_port_index ON spanning_tree__neighbour__vlan2((context-&gt;'physical_port')) wHERE ((context-&gt;'physical_port') IS NOT NULL);
  create index spanning_tree__neighbour__vlan3_physical_port_index ON spanning_tree__neighbour__vlan3((context-&gt;'physical_port')) wHERE ((context-&gt;'physical_port') IS NOT NULL);
  ...
  </code></pre>
  
  <p>but after i create a hundred or so of them, any query gives:</p>
  
  <pre><code>=&gt; explain analyze select * from hosts where hostname='blah';
  WARNING:  out of shared memory
  ERROR:  out of shared memory
  HINT:  You might need to increase max_locks_per_transaction.
  Time: 34.757 ms
  </code></pre>
  
  <p>will setting <code>max_locks_per_transaction</code> actually help? what value should i use given that my partitioned table has upto 4096 child tables?</p>
  
  <p>or have i read the plan wrong?</p>