title: Max number of tables in postgresql select Query
body: <p>I have 7 tables to join in single select query. however when I join any of 5 tables it works fine but joining 7 tables makes Query unresponsive. while there are only around 2k records to be selected. here is my query:</p>
  
  <pre><code>select c.i_cdr, v.i_cdrs_connection, c.i_call,
  cs.name AS customer_name, a.username AS client_name_id,
  con.name AS connection_name, vn.name AS vendor_name_id,
  ca.setup_time,
  c.remote_ip AS c_remote_ip_id, con.destination AS v_remote_ip_id
  FROM cdrs c
  JOIN Calls ca using (i_call)
  JOIN cdrs_connections v using (i_call)
  JOIN accounts a using(i_account)
  JOIN customers cs using (i_customer)
  JOIN connections con using(i_connection)
  JOIN vendors vn using (i_vendor)
  where i_cdr&gt;397233018
  </code></pre>
  
  <p>If there is any restriction on max number of tables in single query or max number of columns?</p>
  
  <p>I have tried reducing columns but no use.</p>
  
  <p>postgresql configurations.</p>
  
  <pre><code>postgres 8.4
  shared_buffers = 128MB
  work_mem = 32MB
  maintenance_work_mem = 64MB
  </code></pre>
  
  <p>Calls Table:</p>
  
  <pre><code>Create Table calls
  (
  i_call serial Not null,
  setup_time timestamp Not Null,
  cli character varying(64),
  cld character varying(64)
  )
  </code></pre>
  
  <p>cdrs Table:</p>
  
  <pre><code>Create Table cdrs
  (
  i_cdr serial Not null,
  i_call bigint Not Null,
  remote_ip character varying(64),
  i_account bigint Not Null
  //few more not used fields
  )
  </code></pre>
  
  <p>Accounts Table:</p>
  
  <pre><code>Create Table accounts
  (
  i_account serial Not null,
  i_customer bigint Not Null,
  username character varying(128)
  )
  </code></pre>
  
  <p>Customers Table:</p>
  
  <pre><code>Create Table customers
  (
  i_customer serial Not null,
  name character varying(128)
  )
  </code></pre>
  
  <p>cdrs_connections:</p>
  
  <pre><code>Create Table cdrs_connections
  (
  i_cdrs_connection serial Not null,
  i_call bigint Not null,
  i_connection bigint Not null,
  connect_time timestamp,
  cld_out character varying(64),
  cli_out character varying(128)
  )
  </code></pre>
  
  <p>Connections:</p>
  
  <pre><code>Create Table connections
  (
  i_connection serial Not null,
  i_vendor bigint Not null,
  name varying character(64),
  destination character varying(64)
  )
  </code></pre>
  
  <p>Vendors:</p>
  
  <pre><code>Create Table vendors
  (
  i_vendor serial Not null,
  name character varying(64)
  );
  </code></pre>
  
  <p>Explain Trace:</p>
  
  <p>Major tables traversed are partitioned one and are fine even if I exclude accounts having 500rows and customers having 26 rows it just works fine.</p>
  
  <h2>  QUERY PLAN</h2>
  
  <pre><code>Nested Loop  (cost=3388.21..3202797988160500.00 rows=6369283937505514496 width=122)
     Join Filter: (c.i_call = ca.i_call)
     -&gt;  Nested Loop  (cost=3388.21..29281315906299.16 rows=12704473261254 width=122)
           -&gt;  Hash Join  (cost=3388.21..310816005731.24 rows=12704473261254 width=121)
                 Hash Cond: (c.i_account = a.i_account)
                 -&gt;  Nested Loop  (cost=0.00..24965353964.82 rows=12704473261254 width=112)
                       Join Filter: (v.i_call = c.i_call)
                       -&gt;  Nested Loop  (cost=0.00..1708130302.64 rows=61256189 width=69)
                             -&gt;  Nested Loop  (cost=0.00..465977965.21 rows=61256189 width=60)
                                   -&gt;  Append  (cost=0.00..6600178.80 rows=61256189 width=24)
                                         -&gt;  Index Scan using cdrs_connections_i_call on cdrs_connections v  (cost=0.00..22.03 rows=310 width=24)
                                         -&gt;  Index Scan using cdrs_connections1_i_call on cdrs_connections1 v  (cost=0.00..219415.43 rows=568528 width=24)
                                         -&gt;  Index Scan using cdrs_connections2_i_call on cdrs_connections2 v  (cost=0.00..34433.40 rows=536331 width=24)
                                         -&gt;  Index Scan using cdrs_connections3_i_call on cdrs_connections3 v  (cost=0.00..359871.77 rows=885115 width=24)
                                         -&gt;  Index Scan using cdrs_connections4_i_call on cdrs_connections4 v  (cost=0.00..84725.16 rows=1316695 width=24)
                                         -&gt;  Index Scan using cdrs_connections5_i_call on cdrs_connections5 v  (cost=0.00..66291.88 rows=1046102 width=24)
                                         -&gt;  Index Scan using cdrs_connections6_i_call on cdrs_connections6 v  (cost=0.00..74410.59 rows=1043184 width=24)
                                         -&gt;  Index Scan using cdrs_connections7_i_call on cdrs_connections7 v  (cost=0.00..79614.35 rows=1244914 width=24)
                                         -&gt;  Index Scan using cdrs_connections8_i_call on cdrs_connections8 v  (cost=0.00..78292.13 rows=1228840 width=24)
                                         -&gt;  Index Scan using cdrs_connections9_i_call on cdrs_connections9 v  (cost=0.00..23.84 rows=320 width=24)
                                         -&gt;  Index Scan using cdrs_connections10_i_call on cdrs_connections10 v  (cost=0.00..869491.71 rows=1326456 width=24)
                                         -&gt;  Index Scan using cdrs_connections11_i_call on cdrs_connections11 v  (cost=0.00..84478.96 rows=1329600 width=24)
                                         -&gt;  Index Scan using cdrs_connections12_i_call on cdrs_connections12 v  (cost=0.00..34373.13 rows=529498 width=24)
                                         -&gt;  Index Scan using cdrs_connections13_i_call on cdrs_connections13 v  (cost=0.00..47.28 rows=330 width=24)
                                         -&gt;  Index Scan using cdrs_connections14_i_call on cdrs_connections14 v  (cost=0.00..33623.46 rows=506174 width=24)
                                         -&gt;  Index Scan using cdrs_connections15_i_call on cdrs_connections15 v  (cost=0.00..31732.57 rows=485078 width=24)
                                         -&gt;  Index Scan using cdrs_connections16_i_call on cdrs_connections16 v  (cost=0.00..29434.18 rows=460046 width=24)
                                         -&gt;  Index Scan using cdrs_connections17_i_call on cdrs_connections17 v  (cost=0.00..32157.93 rows=500074 width=24)
                                         -&gt;  Index Scan using cdrs_connections18_i_call on cdrs_connections18 v  (cost=0.00..35811.81 rows=561528 width=24)
                                         -&gt;  Index Scan using cdrs_connections19_i_call on cdrs_connections19 v  (cost=0.00..157742.69 rows=835540 width=24)
                                         -&gt;  Index Scan using cdrs_connections20_i_call on cdrs_connections20 v  (cost=0.00..104175.88 rows=1638302 width=24)
                                         -&gt;  Index Scan using cdrs_connections21_i_call on cdrs_connections21 v  (cost=0.00..232173.00 rows=1859055 width=24)
                                         -&gt;  Index Scan using cdrs_connections22_i_call on cdrs_connections22 v  (cost=0.00..99680.31 rows=1549844 width=24)
                                         -&gt;  Index Scan using cdrs_connections23_i_call on cdrs_connections23 v  (cost=0.00..913103.99 rows=1597071 width=24)
                                         -&gt;  Index Scan using cdrs_connections24_i_call on cdrs_connections24 v  (cost=0.00..151771.48 rows=1547203 width=24)
                                         -&gt;  Index Scan using cdrs_connections25_i_call on cdrs_connections25 v  (cost=0.00..48.24 rows=320 width=24)
                                         -&gt;  Index Scan using cdrs_connections26_i_call on cdrs_connections26 v  (cost=0.00..94067.11 rows=1472598 width=24)
                                         -&gt;  Index Scan using cdrs_connections27_i_call on cdrs_connections27 v  (cost=0.00..110525.50 rows=1083587 width=24)
                                         -&gt;  Index Scan using cdrs_connections28_i_call on cdrs_connections28 v  (cost=0.00..352381.02 rows=1068230 width=24)
                                         -&gt;  Index Scan using cdrs_connections29_i_call on cdrs_connections29 v  (cost=0.00..78430.93 rows=1234409 width=24)
                                         -&gt;  Index Scan using cdrs_connections30_i_call on cdrs_connections30 v  (cost=0.00..44659.37 rows=696125 width=24)
                                   -&gt;  Index Scan using connections_pkey on connections con  (cost=0.00..7.49 rows=1 width=52)
                                         Index Cond: (con.i_connection = v.i_connection)
                             -&gt;  Index Scan using vendors_pkey on vendors vn  (cost=0.00..20.27 rows=1 width=25)
                                   Index Cond: (vn.i_vendor = con.i_vendor)
                       -&gt;  Append  (cost=0.00..378.91 rows=61 width=43)
                             -&gt;  Index Scan using cdrs_i_call on cdrs c  (cost=0.00..3.87 rows=1 width=37)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs1_i_call on cdrs1 c  (cost=0.00..8.32 rows=1 width=37)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs2_i_call on cdrs2 c  (cost=0.00..8.32 rows=1 width=37)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs3_i_call on cdrs3 c  (cost=0.00..8.38 rows=1 width=37)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs4_i_call on cdrs4 c  (cost=0.00..8.45 rows=1 width=37)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs5_i_call on cdrs5 c  (cost=0.00..8.42 rows=1 width=37)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs6_i_call on cdrs6 c  (cost=0.00..8.43 rows=1 width=36)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs7_i_call on cdrs7 c  (cost=0.00..8.48 rows=1 width=36)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs8_i_call on cdrs8 c  (cost=0.00..8.44 rows=1 width=37)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs9_i_call on cdrs9 c  (cost=0.00..2.27 rows=1 width=38)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs10_i_call on cdrs10 c  (cost=0.00..8.45 rows=1 width=36)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs11_i_call on cdrs11 c  (cost=0.00..8.44 rows=1 width=37)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs12_i_call on cdrs12 c  (cost=0.00..8.32 rows=1 width=37)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs13_i_call on cdrs13 c  (cost=0.00..3.87 rows=1 width=38)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs14_i_call on cdrs14 c  (cost=0.00..8.32 rows=1 width=36)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs15_i_call on cdrs15 c  (cost=0.00..8.31 rows=1 width=36)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs16_i_call on cdrs16 c  (cost=0.00..8.31 rows=1 width=37)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs17_i_call on cdrs17 c  (cost=0.00..8.32 rows=1 width=37)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs18_i_call on cdrs18 c  (cost=0.00..8.32 rows=1 width=37)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs19_i_call on cdrs19 c  (cost=0.00..8.36 rows=1 width=37)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs20_i_call on cdrs20 c  (cost=0.00..8.48 rows=1 width=37)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs21_i_call on cdrs21 c  (cost=0.00..8.51 rows=1 width=37)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs22_i_call on cdrs22 c  (cost=0.00..8.47 rows=1 width=36)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs23_i_call on cdrs23 c  (cost=0.00..8.47 rows=1 width=36)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs24_i_call on cdrs24 c  (cost=0.00..8.47 rows=1 width=37)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs25_i_call on cdrs25 c  (cost=0.00..3.87 rows=1 width=37)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs26_i_call on cdrs26 c  (cost=0.00..8.49 rows=1 width=36)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs27_i_call on cdrs27 c  (cost=0.00..8.42 rows=1 width=37)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs28_i_call on cdrs28 c  (cost=0.00..8.41 rows=1 width=36)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs29_i_call on cdrs29 c  (cost=0.00..8.43 rows=1 width=36)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                             -&gt;  Index Scan using cdrs30_i_call on cdrs30 c  (cost=0.00..8.35 rows=1 width=36)
                                   Index Cond: (c.i_call = v.i_call)
                                   Filter: (c.i_cdr &gt; 39726118)
                 -&gt;  Hash  (cost=3001.41..3001.41 rows=30944 width=25)
                       -&gt;  Index Scan using accounts_i_customer on accounts a  (cost=0.00..3001.41 rows=30944 width=25)
           -&gt;  Index Scan using customers_pkey on customers cs  (cost=0.00..2.27 rows=1 width=17)
                 Index Cond: (cs.i_customer = a.i_customer)
     -&gt;  Append  (cost=0.00..249.41 rows=31 width=16)
           -&gt;  Index Scan using calls_pkey on calls ca  (cost=0.00..3.87 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls1_pkey on calls1 ca  (cost=0.00..8.42 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls2_pkey on calls2 ca  (cost=0.00..8.42 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls3_pkey on calls3 ca  (cost=0.00..8.63 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls4_pkey on calls4 ca  (cost=0.00..9.07 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls5_pkey on calls5 ca  (cost=0.00..8.84 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls6_pkey on calls6 ca  (cost=0.00..8.90 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls7_pkey on calls7 ca  (cost=0.00..8.95 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls8_pkey on calls8 ca  (cost=0.00..8.65 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls9_pkey on calls9 ca  (cost=0.00..3.87 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls10_pkey on calls10 ca  (cost=0.00..8.78 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls11_pkey on calls11 ca  (cost=0.00..8.75 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls12_pkey on calls12 ca  (cost=0.00..8.40 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls13_pkey on calls13 ca  (cost=0.00..3.87 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls14_pkey on calls14 ca  (cost=0.00..8.40 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls15_pkey on calls15 ca  (cost=0.00..8.42 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls16_pkey on calls16 ca  (cost=0.00..8.39 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls17_pkey on calls17 ca  (cost=0.00..8.40 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls18_pkey on calls18 ca  (cost=0.00..8.40 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls19_pkey on calls19 ca  (cost=0.00..8.53 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls20_pkey on calls20 ca  (cost=0.00..8.78 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls21_pkey on calls21 ca  (cost=0.00..8.84 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls22_pkey on calls22 ca  (cost=0.00..8.78 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls23_pkey on calls23 ca  (cost=0.00..8.79 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls24_pkey on calls24 ca  (cost=0.00..8.74 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls25_pkey on calls25 ca  (cost=0.00..3.87 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls26_pkey on calls26 ca  (cost=0.00..9.45 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls27_pkey on calls27 ca  (cost=0.00..8.56 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls28_pkey on calls28 ca  (cost=0.00..8.60 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls29_pkey on calls29 ca  (cost=0.00..8.60 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
           -&gt;  Index Scan using calls30_pkey on calls30 ca  (cost=0.00..8.44 rows=1 width=16)
                 Index Cond: (ca.i_call = c.i_call)
  (326 rows)
  </code></pre>
  
  <p>Hint:</p>
  
  <p>I don't know exactly why but when I create View using given query without last two tables connections and vendors then join this View with remaining tables query works just fine.</p>
  
  <p>Solution:
  I have solved this problem by changing order of join.
  From:</p>
  
  <pre><code>select c.i_cdr, v.i_cdrs_connection, c.i_call,
  cs.name AS customer_name, a.username AS client_name_id,
  con.name AS connection_name, vn.name AS vendor_name_id,
  ca.setup_time,
  c.remote_ip AS c_remote_ip_id, con.destination AS v_remote_ip_id
  FROM cdrs c
  JOIN Calls ca using (i_call)
  JOIN cdrs_connections v using (i_call)
  JOIN accounts a using(i_account)
  JOIN customers cs using (i_customer)
  JOIN connections con using(i_connection)
  JOIN vendors vn using (i_vendor)
  where i_cdr&gt;397233018
  </code></pre>
  
  <p>To:</p>
  
  <pre><code>select c.i_cdr, v.i_cdrs_connection, c.i_call,
  cs.name AS customer_name, a.username AS client_name_id,
  con.name AS connection_name, vn.name AS vendor_name_id,
  ca.setup_time,
  c.remote_ip AS c_remote_ip_id, con.destination AS v_remote_ip_id
  FROM cdrs c
  JOIN accounts a using(i_account)
  JOIN customers cs using (i_customer)
  JOIN Calls ca using (i_call)
  JOIN cdrs_connections v using (i_call)
  JOIN connections con using(i_connection)
  JOIN vendors vn using (i_vendor)
  where i_cdr&gt;397233018
  </code></pre>
  
  <p>This is because cdrs table is linked to accounts table which is in turn connected to customers table. while vendors table is connected to cdrs_connections through connections table. So by placing these tables in order remove loops. Now Response time is really good.</p>