sharafjaffri
Reputation: 2214
Max number of tables in postgresql select Query
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:
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>397233018
If there is any restriction on max number of tables in single query or max number of columns?
I have tried reducing columns but no use.
postgresql configurations.
postgres 8.4
shared_buffers = 128MB
work_mem = 32MB
maintenance_work_mem = 64MB
Calls Table:
Create Table calls
(
i_call serial Not null,
setup_time timestamp Not Null,
cli character varying(64),
cld character varying(64)
)
cdrs Table:
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
)
Accounts Table:
Create Table accounts
(
i_account serial Not null,
i_customer bigint Not Null,
username character varying(128)
)
Customers Table:
Create Table customers
(
i_customer serial Not null,
name character varying(128)
)
cdrs_connections:
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)
)
Connections:
Create Table connections
(
i_connection serial Not null,
i_vendor bigint Not null,
name varying character(64),
destination character varying(64)
)
Vendors:
Create Table vendors
(
i_vendor serial Not null,
name character varying(64)
);
Explain Trace:
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.
QUERY PLAN
Nested Loop (cost=3388.21..3202797988160500.00 rows=6369283937505514496 width=122)
Join Filter: (c.i_call = ca.i_call)
-> Nested Loop (cost=3388.21..29281315906299.16 rows=12704473261254 width=122)
-> Hash Join (cost=3388.21..310816005731.24 rows=12704473261254 width=121)
Hash Cond: (c.i_account = a.i_account)
-> Nested Loop (cost=0.00..24965353964.82 rows=12704473261254 width=112)
Join Filter: (v.i_call = c.i_call)
-> Nested Loop (cost=0.00..1708130302.64 rows=61256189 width=69)
-> Nested Loop (cost=0.00..465977965.21 rows=61256189 width=60)
-> Append (cost=0.00..6600178.80 rows=61256189 width=24)
-> Index Scan using cdrs_connections_i_call on cdrs_connections v (cost=0.00..22.03 rows=310 width=24)
-> Index Scan using cdrs_connections1_i_call on cdrs_connections1 v (cost=0.00..219415.43 rows=568528 width=24)
-> Index Scan using cdrs_connections2_i_call on cdrs_connections2 v (cost=0.00..34433.40 rows=536331 width=24)
-> Index Scan using cdrs_connections3_i_call on cdrs_connections3 v (cost=0.00..359871.77 rows=885115 width=24)
-> Index Scan using cdrs_connections4_i_call on cdrs_connections4 v (cost=0.00..84725.16 rows=1316695 width=24)
-> Index Scan using cdrs_connections5_i_call on cdrs_connections5 v (cost=0.00..66291.88 rows=1046102 width=24)
-> Index Scan using cdrs_connections6_i_call on cdrs_connections6 v (cost=0.00..74410.59 rows=1043184 width=24)
-> Index Scan using cdrs_connections7_i_call on cdrs_connections7 v (cost=0.00..79614.35 rows=1244914 width=24)
-> Index Scan using cdrs_connections8_i_call on cdrs_connections8 v (cost=0.00..78292.13 rows=1228840 width=24)
-> Index Scan using cdrs_connections9_i_call on cdrs_connections9 v (cost=0.00..23.84 rows=320 width=24)
-> Index Scan using cdrs_connections10_i_call on cdrs_connections10 v (cost=0.00..869491.71 rows=1326456 width=24)
-> Index Scan using cdrs_connections11_i_call on cdrs_connections11 v (cost=0.00..84478.96 rows=1329600 width=24)
-> Index Scan using cdrs_connections12_i_call on cdrs_connections12 v (cost=0.00..34373.13 rows=529498 width=24)
-> Index Scan using cdrs_connections13_i_call on cdrs_connections13 v (cost=0.00..47.28 rows=330 width=24)
-> Index Scan using cdrs_connections14_i_call on cdrs_connections14 v (cost=0.00..33623.46 rows=506174 width=24)
-> Index Scan using cdrs_connections15_i_call on cdrs_connections15 v (cost=0.00..31732.57 rows=485078 width=24)
-> Index Scan using cdrs_connections16_i_call on cdrs_connections16 v (cost=0.00..29434.18 rows=460046 width=24)
-> Index Scan using cdrs_connections17_i_call on cdrs_connections17 v (cost=0.00..32157.93 rows=500074 width=24)
-> Index Scan using cdrs_connections18_i_call on cdrs_connections18 v (cost=0.00..35811.81 rows=561528 width=24)
-> Index Scan using cdrs_connections19_i_call on cdrs_connections19 v (cost=0.00..157742.69 rows=835540 width=24)
-> Index Scan using cdrs_connections20_i_call on cdrs_connections20 v (cost=0.00..104175.88 rows=1638302 width=24)
-> Index Scan using cdrs_connections21_i_call on cdrs_connections21 v (cost=0.00..232173.00 rows=1859055 width=24)
-> Index Scan using cdrs_connections22_i_call on cdrs_connections22 v (cost=0.00..99680.31 rows=1549844 width=24)
-> Index Scan using cdrs_connections23_i_call on cdrs_connections23 v (cost=0.00..913103.99 rows=1597071 width=24)
-> Index Scan using cdrs_connections24_i_call on cdrs_connections24 v (cost=0.00..151771.48 rows=1547203 width=24)
-> Index Scan using cdrs_connections25_i_call on cdrs_connections25 v (cost=0.00..48.24 rows=320 width=24)
-> Index Scan using cdrs_connections26_i_call on cdrs_connections26 v (cost=0.00..94067.11 rows=1472598 width=24)
-> Index Scan using cdrs_connections27_i_call on cdrs_connections27 v (cost=0.00..110525.50 rows=1083587 width=24)
-> Index Scan using cdrs_connections28_i_call on cdrs_connections28 v (cost=0.00..352381.02 rows=1068230 width=24)
-> Index Scan using cdrs_connections29_i_call on cdrs_connections29 v (cost=0.00..78430.93 rows=1234409 width=24)
-> Index Scan using cdrs_connections30_i_call on cdrs_connections30 v (cost=0.00..44659.37 rows=696125 width=24)
-> 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)
-> 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)
-> Append (cost=0.00..378.91 rows=61 width=43)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> 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 > 39726118)
-> Hash (cost=3001.41..3001.41 rows=30944 width=25)
-> Index Scan using accounts_i_customer on accounts a (cost=0.00..3001.41 rows=30944 width=25)
-> 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)
-> Append (cost=0.00..249.41 rows=31 width=16)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
-> 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)
Hint:
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.
Solution: I have solved this problem by changing order of join. From:
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>397233018
To:
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>397233018
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.
Upvotes: 0
Views: 3051
Answers (2)
Craig Ringer
Reputation: 324781
See the Limits heading in About PostgreSQL for max columns.
Whether there's a max number of tables in a join I'm not sure. In practice, the real limit will be time and your computer's performance and RAM+disk space.
You really need to examine the query plans you're getting using EXPLAIN ANALYZE to get some idea of what's slow and why. explain.depesz.com can help make the resulting plans more readable.
Watch out to make sure you aren't getting any unwanted cartesian product produced. You probably aren't given you're using INNER JOIN ... USING syntax, though.
Upvotes: 2
Related Questions
- What is the maximum number of columns in a PostgreSQL select query
- PostgreSQL query multiple tables using JOIN statement
- How to select from multiple tables when you have a join?
- Not able to join two tables with limit in Postgres
- Does PostgreSQL have a limit on # of tables in a join?
- Select limited rows from multiple tables
- Find names of all tables with more than x rows
- SQL Queries from multiple tables
- Select rows of multiple tables via joins
- sql max of count 2 tables