|
Home > Archive > PostgreSQL Bugs > November 2005 > Huge query stalls at PARSE/BIND stage (1)
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Huge query stalls at PARSE/BIND stage (1)
|
|
|
| (-resend)
Hi,
I have a nightly process which distills a range of statistics from a
third-party database into a set of temporary tables, and then from those
tables, aggregates and joins these figures into two main tables. Each
temporary table contains a moderate number of rows and few columns.
The main query is a large series of FULL JOINs and has worked perfectly
for a long time.
Yesterday I added 20 more join statements to the query (identical to
existing statements) and now the main query which would run in <30
seconds, runs indefinitely with the process stuck at "PARSE" in version
7.4, or "BIND" in version 8.0.3. The process is using all available CPU
but not a great deal of memory.
I've let this process run for over 50 minutes before it is killed by me
with signal ABRT. (Terminating the client connection does not stop the
process.)
If I remove these additional joins the statement perfectly again in <30
seconds.
I have upgraded Postgresql from version 7.4 to 8.0.3 but this does not
fix the behaviour.
The additional tables being full joined are generated with the same java
function as the other tables and the only variable being the names of
the non-key columns (different temporary tables for different columns of
data in the resulting table).
The data in the source tables of the additional statements I am adding
has an identical size and layout to the source tables from the existing
FULL JOIN statements.
The only difference with these new tables is that not all of the joined
columns are used in the select clause of the main query, some are
ignored, whereas in previous tables all columns available are used.
Here is the main query:
INSERT INTO pws_stats_case (
--- Destination columns
batch_id,csid,year,m
onth,case_upload_dat
e,
num_live,amt_live,fb
al_live,bal_live,fee
s_live,
num_arrange,amt_arra
nge,fbal_arrange,bal
_arrange,fees_arrang
e,
num_fullypaid,amt_fu
llypaid,fbal_fullypa
id,bal_fullypaid,fee
s_fullypaid,
num_successful,amt_s
uccessful,fbal_succe
ssful,bal_successful
,fees_successful,
num_trace,amt_trace,
fbal_trace,bal_trace
,fees_trace,
num_expired,amt_expi
red,fbal_expired,bal
_expired,fees_expire
d,
num_our_hold,amt_our
_hold,fbal_our_hold,
bal_our_hold,fees_ou
r_hold,
num_client_hold,amt_
client_hold,fbal_cli
ent_hold,bal_client_
hold,fees_client_hol
d,
num_hold,amt_hold,fb
al_hold,bal_hold,fee
s_hold,
num_returned,amt_ret
urned,fbal_returned,
bal_returned,fees_re
turned,
num_returned_err,amt
_returned_err,fbal_r
eturned_err,bal_retu
rned_err,fees_return
ed_err,
num_30days,amt_30day
s,fbal_30days,bal_30
days,fees_30days,
num_60days,amt_60day
s,fbal_60days,bal_60
days,fees_60days,
num_90days,amt_90day
s,fbal_90days,bal_90
days,fees_90days,
num_365days,amt_365d
ays,fbal_365days,bal
_365days,fees_365day
s,
total_num_open,total
_amt_open,total_fbal
_open,total_bal_open
,total_fees_open,tot
al_collected_open,
total_num_closed,tot
al_amt_closed,total_
fbal_closed,total_ba
l_closed,total_fees_
closed,total_collect
ed_closed,
total_num,total_amt,
total_fbal,total_bal
,total_fees,total_co
llected,
num_r_uncollectable,
amt_r_uncollectable,
fbal_r_uncollectable
,bal_r_uncollectable
,fees_r_uncollectabl
e,
num_r_collectable,am
t_r_collectable,fbal
_r_collectable,bal_r
_collectable,fees_r_
collectable,
num_r_requested,amt_
r_requested,fbal_r_r
equested,bal_r_reque
sted,fees_r_requeste
d,
num_allocated,amt_al
located,fbal_allocat
ed,bal_allocated,fee
s_allocated,
num_returned_open,am
t_returned_open,fbal
_returned_open,bal_r
eturned_open,fees_re
turned_open,
num_returned_err_ope
n,bal_returned_err_o
pen,fbal_returned_er
r_open,amt_returned_
err_open,fees_return
ed_err_open,
num_r_uncollectable_
open,amt_r_uncollect
able_open,bal_r_unco
llectable_open,fbal_
r_uncollectable_open
,fees_r_uncollectabl
e_open,
num_r_collectable_op
en,amt_r_collectable
_open,bal_r_collecta
ble_open,fbal_r_coll
ectable_open,fees_r_
collectable_open,
num_r_requested_open
,amt_r_requested_ope
n,bal_r_requested_op
en,fbal_r_requested_
open,fees_r_requeste
d_open,
num_returned_closed,
amt_returned_closed,
fbal_returned_closed
,bal_returned_closed
,fees_returned_close
d,
num_returned_err_clo
sed,bal_returned_err
_closed,fbal_returne
d_err_closed,amt_ret
urned_err_closed,fee
s_returned_err_close
d,
num_r_uncollectable_
closed,amt_r_uncolle
ctable_closed,bal_r_
uncollectable_closed
,fbal_r_uncollectabl
e_closed,fees_r_unco
llectable_closed,
num_r_collectable_cl
osed,amt_r_collectab
le_closed,bal_r_coll
ectable_closed,fbal_
r_collectable_closed
,fees_r_collectable_
closed,
num_r_requested_clos
ed,amt_r_requested_c
losed,bal_r_requeste
d_closed,fbal_r_requ
ested_closed,fees_r_
requested_closed,
num_open_expired,amt
_open_expired,fbal_o
pen_expired,bal_open
_expired,fees_open_e
xpired,
num_closed_expired,a
mt_closed_expired,fb
al_closed_expired,ba
l_closed_expired,fee
s_closed_expired,
num_open_successful,
amt_open_successful,
fbal_open_successful
,bal_open_successful
,fees_open_successfu
l,
num_closed_successfu
l,amt_closed_success
ful,fbal_closed_succ
essful,bal_closed_su
ccessful,fees_closed
_successful
)
-- Select query, huge union in two parts.
-- part one's tables are keyed on (csid=?,year=?,month=? ,case_upload_date=nu
ll),
-- part two's tables are keyed on (csid=? ,year=0,month=0,case
_upload_date=?)
SELECT ? ,csid,year,month,NUL
L::date AS case_upload_date,
-- Same list of destination columns, but all coalesced with 0 to prevent NULLs
COALESCE(num_live, 0), COALESCE(amt_live, 0), COALESCE(fbal_live, 0), COALESCE(bal_live, 0), COALESCE(fees_live, 0),
COALESCE(num_arrange
, 0), COALESCE(amt_arrange
, 0), COALESCE(fbal_arrang
e, 0), COALESCE(bal_arrange
, 0),
COALESCE(fees_arrang
e, 0), COALESCE(num_fullypa
id, 0), COALESCE(amt_fullypa
id, 0), COALESCE(fbal_fullyp
aid, 0),
COALESCE(bal_fullypa
id, 0), COALESCE(fees_fullyp
aid, 0), COALESCE(num_success
ful, 0), COALESCE(amt_success
ful, 0),
COALESCE(fbal_succes
sful, 0), COALESCE(bal_success
ful, 0), COALESCE(fees_succes
sful, 0), COALESCE(num_trace, 0),
COALESCE(amt_trace, 0), COALESCE(fbal_trace,
0), COALESCE(bal_trace, 0), COALESCE(fees_trace,
0), COALESCE(num_expired
, 0),
COALESCE(amt_expired
, 0), COALESCE(fbal_expire
d, 0), COALESCE(bal_expired
, 0), COALESCE(fees_expire
d, 0),
COALESCE(num_our_hol
d, 0), COALESCE(amt_our_hol
d, 0), COALESCE(fbal_our_ho
ld, 0), COALESCE(bal_our_hol
d, 0),
COALESCE(fees_our_ho
ld, 0), COALESCE(num_client_
hold, 0), COALESCE(amt_client_
hold, 0), COALESCE(fbal_client
_hold, 0),
COALESCE(bal_client_
hold, 0), COALESCE(fees_client
_hold, 0), COALESCE(num_hold, 0), COALESCE(amt_hold, 0),
COALESCE(fbal_hold, 0), COALESCE(bal_hold, 0), COALESCE(fees_hold, 0), COALESCE(num_returne
d, 0),
COALESCE(amt_returne
d, 0), COALESCE(fbal_return
ed, 0), COALESCE(bal_returne
d, 0), COALESCE(fees_return
ed, 0),
COALESCE(num_returne
d_err, 0), COALESCE(amt_returne
d_err, 0), COALESCE(fbal_return
ed_err, 0),
COALESCE(bal_returne
d_err, 0), COALESCE(fees_return
ed_err, 0), COALESCE(num_30days,
0), COALESCE(amt_30days,
0),
COALESCE(fbal_30days
, 0), COALESCE(bal_30days,
0), COALESCE(fees_30days
, 0), COALESCE(num_60days,
0),
COALESCE(amt_60days,
0), COALESCE(fbal_60days
, 0), COALESCE(bal_60days,
0), COALESCE(fees_60days
, 0),
COALESCE(num_90days,
0), COALESCE(amt_90days,
0), COALESCE(fbal_90days
, 0), COALESCE(bal_90days,
0),
COALESCE(fees_90days
, 0), COALESCE(num_365days
, 0), COALESCE(amt_365days
, 0), COALESCE(fbal_365day
s, 0),
COALESCE(bal_365days
, 0), COALESCE(fees_365day
s, 0), COALESCE(total_num_o
pen, 0), COALESCE(total_amt_o
pen, 0),
COALESCE(total_fbal_
open, 0), COALESCE(total_bal_o
pen, 0), COALESCE(total_fees_
open, 0), COALESCE(total_colle
cted_open, 0),
COALESCE(total_num_c
losed, 0), COALESCE(total_amt_c
losed, 0), COALESCE(total_fbal_
closed, 0), COALESCE(total_bal_c
losed, 0),
COALESCE(total_fees_
closed, 0), COALESCE(total_colle
cted_closed, 0), COALESCE(total_num, 0), COALESCE(total_amt, 0),
COALESCE(total_fbal,
0), COALESCE(total_bal, 0), COALESCE(total_fees,
0), COALESCE(total_colle
cted, 0),
COALESCE(num_r_uncol
lectable, 0), COALESCE(amt_r_uncol
lectable, 0), COALESCE(fbal_r_unco
llectable, 0),
COALESCE(bal_r_uncol
lectable, 0), COALESCE(fees_r_unco
llectable, 0), COALESCE(num_r_colle
ctable, 0),
COALESCE(amt_r_colle
ctable, 0), COALESCE(fbal_r_coll
ectable, 0), COALESCE(bal_r_colle
ctable, 0),
COALESCE(fees_r_coll
ectable, 0), COALESCE(num_r_reque
sted, 0), COALESCE(amt_r_reque
sted, 0), COALESCE(fbal_r_requ
ested, 0),
COALESCE(bal_r_reque
sted, 0), COALESCE(fees_r_requ
ested, 0), COALESCE(num_allocat
ed, 0), COALESCE(amt_allocat
ed, 0),
COALESCE(fbal_alloca
ted, 0), COALESCE(bal_allocat
ed, 0), COALESCE(fees_alloca
ted, 0), COALESCE(num_returne
d_open, 0),
COALESCE(amt_returne
d_open, 0), COALESCE(fbal_return
ed_open, 0), COALESCE(bal_returne
d_open, 0),
COALESCE(fees_return
ed_open, 0), COALESCE(num_returne
d_err_open, 0), COALESCE(bal_returne
d_err_open, 0),
COALESCE(fbal_return
ed_err_open, 0), COALESCE(amt_returne
d_err_open, 0), COALESCE(fees_return
ed_err_open, 0),
COALESCE(num_r_uncol
lectable_open, 0), COALESCE(amt_r_uncol
lectable_open, 0), COALESCE(bal_r_uncol
lectable_open, 0),
COALESCE(fbal_r_unco
llectable_open, 0), COALESCE(fees_r_unco
llectable_open, 0), COALESCE(num_r_colle
ctable_open, 0),
COALESCE(amt_r_colle
ctable_open, 0), COALESCE(bal_r_colle
ctable_open, 0), COALESCE(fbal_r_coll
ectable_open, 0),
COALESCE(fees_r_coll
ectable_open, 0), COALESCE(num_r_reque
sted_open, 0), COALESCE(amt_r_reque
sted_open, 0),
COALESCE(bal_r_reque
sted_open, 0), COALESCE(fbal_r_requ
ested_open, 0), COALESCE(fees_r_requ
ested_open, 0),
COALESCE(num_returne
d_closed, 0), COALESCE(amt_returne
d_closed, 0), COALESCE(fbal_return
ed_closed, 0),
COALESCE(bal_returne
d_closed, 0), COALESCE(fees_return
ed_closed, 0), COALESCE(num_returne
d_err_closed, 0),
COALESCE(bal_returne
d_err_closed, 0), COALESCE(fbal_return
ed_err_closed, 0), COALESCE(amt_returne
d_err_closed, 0),
COALESCE(fees_return
ed_err_closed, 0), COALESCE(num_r_uncol
lectable_closed, 0), COALESCE(amt_r_uncol
lectable_closed, 0), COALESCE(bal_r_uncol
lectable_closed, 0), COALESCE(fbal_r_unco
llectable_closed, 0), COALESCE(fees_r_unco
llectable_closed, 0),
COALESCE(num_r_colle
ctable_closed, 0), COALESCE(amt_r_colle
ctable_closed, 0), COALESCE(bal_r_colle
ctable_closed, 0),
COALESCE(fbal_r_coll
ectable_closed, 0), COALESCE(fees_r_coll
ectable_closed, 0), COALESCE(num_r_reque
sted_closed, 0),
COALESCE(amt_r_reque
sted_closed, 0), COALESCE(bal_r_reque
sted_closed, 0), COALESCE(fbal_r_requ
ested_closed, 0),
COALESCE(fees_r_requ
ested_closed, 0), COALESCE(num_open_ex
pired, 0), COALESCE(amt_open_ex
pired, 0),
COALESCE(fbal_open_e
xpired, 0), COALESCE(bal_open_ex
pired, 0), COALESCE(fees_open_e
xpired, 0),
COALESCE(num_closed_
expired, 0), COALESCE(amt_closed_
expired, 0), COALESCE(fbal_closed
_expired, 0),
COALESCE(bal_closed_
expired, 0), COALESCE(fees_closed
_expired, 0), COALESCE(num_open_su
ccessful, 0),
COALESCE(amt_open_su
ccessful, 0), COALESCE(fbal_open_s
uccessful, 0), COALESCE(bal_open_su
ccessful, 0),
COALESCE(fees_open_s
uccessful, 0), COALESCE(num_closed_
successful, 0), COALESCE(amt_closed_
successful, 0),
COALESCE(fbal_closed
_successful, 0), COALESCE(bal_closed_
successful, 0), COALESCE(fees_closed
_successful, 0)
--- Join account status crosstabs
FROM tmp_stats_dsm_crosst
ab_num s
{(2) ...
FULL JOIN tmp_stats_dsm_crosst
ab_amt USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosst
ab_fbal USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosst
ab_bal USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosst
ab_fees USING (csid,year,month)
.... }
{(1) ...
FULL JOIN tmp_stats_dsm_crosst
ab_num_open USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosst
ab_amt_open USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosst
ab_fbal_open USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosst
ab_bal_open USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosst
ab_fees_open USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosst
ab_num_closed USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosst
ab_amt_closed USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosst
ab_fbal_closed USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosst
ab_bal_closed USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosst
ab_fees_closed USING (csid,year,month)
.... }
-- Join account hold crosstabs
FULL JOIN tmp_stats_dhm_crosst
ab_num USING (csid,year,month)
FULL JOIN tmp_stats_dhm_crosst
ab_amt USING (csid,year,month)
FULL JOIN tmp_stats_dhm_crosst
ab_fbal USING (csid,year,month)
FULL JOIN tmp_stats_dhm_crosst
ab_bal USING (csid,year,month)
FULL JOIN tmp_stats_dhm_crosst
ab_fees USING (csid,year,month)
-- Join account return totals
FULL JOIN (
SELECT csid,year,month,
sum(num) AS num_returned,sum(amt
) AS amt_returned,sum(fba
l) AS fbal_returned, sum(bal) AS bal_returned,sum(fee
s) AS fees_returned,
sum(num_open) AS num_returned_open,su
m(amt_open) AS amt_returned_open,su
m(fbal_open) AS fbal_returned_open, sum(bal) AS bal_returned_open,su
m(fees_open) AS fees_returned_open,
sum(num_closed) AS num_returned_closed,
sum(amt_closed) AS amt_returned_closed,
sum(fbal_closed) AS fbal_returned_closed
, sum(bal) AS bal_returned_closed,
sum(fees_closed) AS fees_returned_closed
FROM pws_stats_returns WHERE batch_id = ? AND case_upload_date IS NULL AND year IS NOT NULL AND return_code != 20 GROUP BY csid,year,month
) total_drm USING (csid,year,month)
FULL JOIN (
SELECT csid,year,month,
sum(num) AS num_returned_err,sum
(amt) AS amt_returned_err,sum
(fbal) AS fbal_returned_err, sum(bal) AS bal_returned_err,sum
(fees) AS fees_returned_err,
sum(num_open) AS num_returned_err_ope
n,sum(amt_open) AS amt_returned_err_ope
n,sum(fbal_open) AS fbal_returned_err_op
en, sum(bal) AS bal_returned_err_ope
n,sum(fees_open) AS fees_returned_err_op
en,
sum(num_closed) AS num_returned_err_clo
sed,sum(amt_closed) AS amt_returned_err_clo
sed,sum(fbal_closed)
AS fbal_returned_err_cl
osed, sum(bal) AS bal_returned_err_clo
sed,sum(fees_closed)
AS fees_returned_err_cl
osed
FROM pws_stats_returns WHERE batch_id = ? AND case_upload_date IS NULL AND year IS NOT NULL AND return_code = 20 GROUP BY csid,year,month
) total_drme USING (csid,year,month)
-- Join account uncollectable/collectable/requested
FULL JOIN (
SELECT csid,year,month,
sum(num) AS num_r_uncollectable,
sum(amt) AS amt_r_uncollectable,
sum(fbal) AS fbal_r_uncollectable
, sum(bal) AS bal_r_uncollectable,
sum(fees) AS fees_r_uncollectable
,
sum(num_open) AS num_r_uncollectable_
open,sum(amt_open) AS amt_r_uncollectable_
open,sum(fbal_open) AS fbal_r_uncollectable
_open, sum(bal) AS bal_r_uncollectable_
open,sum(fees_open) AS fees_r_uncollectable
_open,
sum(num_closed) AS num_r_uncollectable_
closed,sum(amt_close
d) AS amt_r_uncollectable_
closed,sum(fbal_clos
ed) AS fbal_r_uncollectable
_closed, sum(bal) AS bal_r_uncollectable_
closed,sum(fees_clos
ed) AS fees_r_uncollectable
_closed
FROM pws_stats_returns, pws_return_codes rc WHERE batch_id = ? AND case_upload_date IS NULL AND year IS NOT NULL
AND return_code = rc.id AND rc.nature = 'U' AND return_code != 20 GROUP BY csid,year,month
) total_rum USING (csid,year,month)
FULL JOIN (
SELECT csid,year,month,
sum(num) AS num_r_collectable,su
m(amt) AS amt_r_collectable,su
m(fbal) AS fbal_r_collectable, sum(bal) AS bal_r_collectable,su
m(fees) AS fees_r_collectable,
sum(num_open) AS num_r_collectable_op
en,sum(amt_open) AS amt_r_collectable_op
en,sum(fbal_open) AS fbal_r_collectable_o
pen, sum(bal) AS bal_r_collectable_op
en,sum(fees_open) AS fees_r_collectable_o
pen,
sum(num_closed) AS num_r_collectable_cl
osed,sum(amt_closed)
AS amt_r_collectable_cl
osed,sum(fbal_closed
) AS fbal_r_collectable_c
losed, sum(bal) AS bal_r_collectable_cl
osed,sum(fees_closed
) AS fees_r_collectable_c
losed
FROM pws_stats_returns, pws_return_codes rc WHERE batch_id = ? AND case_upload_date IS NULL AND year IS NOT NULL
AND return_code = rc.id AND rc.nature = 'F' AND return_code != 20 GROUP BY csid,year,month
) total_rcm USING (csid,year,month)
FULL JOIN (
SELECT csid,year,month,
sum(num) AS num_r_requested,sum(
amt) AS amt_r_requested,sum(
fbal) AS fbal_r_requested, sum(bal) AS bal_r_requested,sum(
fees) AS fees_r_requested,
sum(num_open) AS num_r_requested_open
,sum(amt_open) AS amt_r_requested_open
,sum(fbal_open) AS fbal_r_requested_ope
n, sum(bal) AS bal_r_requested_open
,sum(fees_open) AS fees_r_requested_ope
n,
sum(num_closed) AS num_r_requested_clos
ed,sum(amt_closed) AS amt_r_requested_clos
ed,sum(fbal_closed) AS fbal_r_requested_clo
sed, sum(bal) AS bal_r_requested_clos
ed,sum(fees_closed) AS fees_r_requested_clo
sed
FROM pws_stats_returns, pws_return_codes rc WHERE batch_id = ? AND case_upload_date IS NULL AND year IS NOT NULL
AND return_code = rc.id AND rc.req_by_client = true AND return_code != 20 GROUP BY csid,year,month
) total_rrm USING (csid,year,month)
-- Join account age bands
FULL JOIN (
SELECT csid,year,month,
num AS num_30days,amt AS amt_30days,fbal AS fbal_30days,bal AS bal_30days,fees AS fees_30days
FROM tmp_stats_dam30
) total_dam30 USING (csid,year,month)
FULL JOIN (
SELECT csid,year,month,
num AS num_60days,amt AS amt_60days,fbal AS fbal_60days,bal AS bal_60days,fees AS fees_60days
FROM tmp_stats_dam60
) total_dam60 USING (csid,year,month)
FULL JOIN (
SELECT csid,year,month,
num AS num_90days,amt AS amt_90days,fbal AS fbal_90days,bal AS bal_90days,fees AS fees_90days
FROM tmp_stats_dam90
) total_dam90 USING (csid,year,month)
FULL JOIN (
SELECT csid,year,month,
num AS num_365days,amt AS amt_365days,fbal AS fbal_365days,bal AS bal_365days,fees AS fees_365days
FROM tmp_stats_dam365
) total_dam365 USING (csid,year,month)
-- Join summary totals
FULL JOIN (
SELECT csid,year,month,
num AS total_num_open,amt AS total_amt_open,fbal AS total_fbal_open,bal AS total_bal_open,fees AS total_fees_open,(amt
-bal) AS total_collected_open
FROM tmp_stats_zom
) zom USING (csid,year,month)
FULL JOIN (
SELECT csid,year,month,
num AS total_num_closed,amt
AS total_amt_closed,fba
l AS total_fbal_closed,ba
l AS total_bal_closed,fee
s AS total_fees_closed,(a
mt-bal) AS total_collected_clos
ed
FROM tmp_stats_zcm
) zcm USING (csid,year,month)
FULL JOIN (
SELECT csid,year,month,
num AS total_num,amt AS total_amt,fbal AS total_fbal,bal AS total_bal,fees AS total_fees,(amt-bal) AS total_collected
FROM tmp_stats_zam
) zam USING (csid,year,month)
-- Join agent totals
FULL JOIN (
SELECT csid,year,month,
num AS num_allocated,amt AS amt_allocated,fbal AS fbal_allocated,bal AS bal_allocated,fees AS fees_allocated
FROM tmp_stats_bam
) bam USING (csid,year,month)
UNION -- ---- End per-month/year/total stats, begin per-batch ----
SELECT ?,csid,NULL::integer AS year,NULL::integer AS month,case_upload_da
te,
-- Same destination column list as above, coalesced with 0
COALESCE(num_live, 0), COALESCE(amt_live, 0), COALESCE(fbal_live, 0), COALESCE(bal_live, 0), COALESCE(fees_live, 0),
COALESCE(num_arrange
, 0), COALESCE(amt_arrange
, 0), COALESCE(fbal_arrang
e, 0), COALESCE(bal_arrange
, 0),
COALESCE(fees_arrang
e, 0), COALESCE(num_fullypa
id, 0), COALESCE(amt_fullypa
id, 0), COALESCE(fbal_fullyp
aid, 0),
COALESCE(bal_fullypa
id, 0), COALESCE(fees_fullyp
aid, 0), COALESCE(num_success
ful, 0), COALESCE(amt_success
ful, 0),
COALESCE(fbal_succes
sful, 0), COALESCE(bal_success
ful, 0), COALESCE(fees_succes
sful, 0), COALESCE(num_trace, 0),
COALESCE(amt_trace, 0), COALESCE(fbal_trace,
0), COALESCE(bal_trace, 0), COALESCE(fees_trace,
0), COALESCE(num_expired
, 0),
COALESCE(amt_expired
, 0), COALESCE(fbal_expire
d, 0), COALESCE(bal_expired
, 0), COALESCE(fees_expire
d, 0),
COALESCE(num_our_hol
d, 0), COALESCE(amt_our_hol
d, 0), COALESCE(fbal_our_ho
ld, 0), COALESCE(bal_our_hol
d, 0),
COALESCE(fees_our_ho
ld, 0), COALESCE(num_client_
hold, 0), COALESCE(amt_client_
hold, 0), COALESCE(fbal_client
_hold, 0),
COALESCE(bal_client_
hold, 0), COALESCE(fees_client
_hold, 0), COALESCE(num_hold, 0), COALESCE(amt_hold, 0),
COALESCE(fbal_hold, 0), COALESCE(bal_hold, 0), COALESCE(fees_hold, 0), COALESCE(num_returne
d, 0),
COALESCE(amt_returne
d, 0), COALESCE(fbal_return
ed, 0), COALESCE(bal_returne
d, 0), COALESCE(fees_return
ed, 0),
COALESCE(num_returne
d_err, 0), COALESCE(amt_returne
d_err, 0), COALESCE(fbal_return
ed_err, 0),
COALESCE(bal_returne
d_err, 0), COALESCE(fees_return
ed_err, 0), COALESCE(num_30days,
0), COALESCE(amt_30days,
0),
COALESCE(fbal_30days
, 0), COALESCE(bal_30days,
0), COALESCE(fees_30days
, 0), COALESCE(num_60days,
0),
COALESCE(amt_60days,
0), COALESCE(fbal_60days
, 0), COALESCE(bal_60days,
0), COALESCE(fees_60days
, 0),
COALESCE(num_90days,
0), COALESCE(amt_90days,
0), COALESCE(fbal_90days
, 0), COALESCE(bal_90days,
0),
COALESCE(fees_90days
, 0), COALESCE(num_365days
, 0), COALESCE(amt_365days
, 0), COALESCE(fbal_365day
s, 0),
COALESCE(bal_365days
, 0), COALESCE(fees_365day
s, 0), COALESCE(total_num_o
pen, 0), COALESCE(total_amt_o
pen, 0),
COALESCE(total_fbal_
open, 0), COALESCE(total_bal_o
pen, 0), COALESCE(total_fees_
open, 0), COALESCE(total_colle
cted_open, 0),
COALESCE(total_num_c
losed, 0), COALESCE(total_amt_c
losed, 0), COALESCE(total_fbal_
closed, 0), COALESCE(total_bal_c
losed, 0),
COALESCE(total_fees_
closed, 0), COALESCE(total_colle
cted_closed, 0), COALESCE(total_num, 0), COALESCE(total_amt, 0),
COALESCE(total_fbal,
0), COALESCE(total_bal, 0), COALESCE(total_fees,
0), COALESCE(total_colle
cted, 0),
COALESCE(num_r_uncol
lectable, 0), COALESCE(amt_r_uncol
lectable, 0), COALESCE(fbal_r_unco
llectable, 0),
COALESCE(bal_r_uncol
lectable, 0), COALESCE(fees_r_unco
llectable, 0), COALESCE(num_r_colle
ctable, 0),
COALESCE(amt_r_colle
ctable, 0), COALESCE(fbal_r_coll
ectable, 0), COALESCE(bal_r_colle
ctable, 0),
COALESCE(fees_r_coll
ectable, 0), COALESCE(num_r_reque
sted, 0), COALESCE(amt_r_reque
sted, 0), COALESCE(fbal_r_requ
ested, 0),
COALESCE(bal_r_reque
sted, 0), COALESCE(fees_r_requ
ested, 0), COALESCE(num_allocat
ed, 0), COALESCE(amt_allocat
ed, 0),
COALESCE(fbal_alloca
ted, 0), COALESCE(bal_allocat
ed, 0), COALESCE(fees_alloca
ted, 0), COALESCE(num_returne
d_open, 0),
COALESCE(amt_returne
d_open, 0), COALESCE(fbal_return
ed_open, 0), COALESCE(bal_returne
d_open, 0),
COALESCE(fees_return
ed_open, 0), COALESCE(num_returne
d_err_open, 0), COALESCE(bal_returne
d_err_open, 0),
COALESCE(fbal_return
ed_err_open, 0), COALESCE(amt_returne
d_err_open, 0), COALESCE(fees_return
ed_err_open, 0),
COALESCE(num_r_uncol
lectable_open, 0), COALESCE(amt_r_uncol
lectable_open, 0), COALESCE(bal_r_uncol
lectable_open, 0),
COALESCE(fbal_r_unco
llectable_open, 0), COALESCE(fees_r_unco
llectable_open, 0), COALESCE(num_r_colle
ctable_open, 0),
COALESCE(amt_r_colle
ctable_open, 0), COALESCE(bal_r_colle
ctable_open, 0), COALESCE(fbal_r_coll
ectable_open, 0),
COALESCE(fees_r_coll
ectable_open, 0), COALESCE(num_r_reque
sted_open, 0), COALESCE(amt_r_reque
sted_open, 0),
COALESCE(bal_r_reque
sted_open, 0), COALESCE(fbal_r_requ
ested_open, 0), COALESCE(fees_r_requ
ested_open, 0),
COALESCE(num_returne
d_closed, 0), COALESCE(amt_returne
d_closed, 0), COALESCE(fbal_return
ed_closed, 0),
COALESCE(bal_returne
d_closed, 0), COALESCE(fees_return
ed_closed, 0), COALESCE(num_returne
d_err_closed, 0),
COALESCE(bal_returne
d_err_closed, 0), COALESCE(fbal_return
ed_err_closed, 0), COALESCE(amt_returne
d_err_closed, 0),
COALESCE(fees_return
ed_err_closed, 0), COALESCE(num_r_uncol
lectable_closed, 0), COALESCE(amt_r_uncol
lectable_closed, 0), COALESCE(bal_r_uncol
lectable_closed, 0), COALESCE(fbal_r_unco
llectable_closed, 0), COALESCE(fees_r_unco
llectable_closed, 0),
COALESCE(num_r_colle
ctable_closed, 0), COALESCE(amt_r_colle
ctable_closed, 0), COALESCE(bal_r_colle
ctable_closed, 0),
COALESCE(fbal_r_coll
ectable_closed, 0), COALESCE(fees_r_coll
ectable_closed, 0), COALESCE(num_r_reque
sted_closed, 0),
COALESCE(amt_r_reque
sted_closed, 0), COALESCE(bal_r_reque
sted_closed, 0), COALESCE(fbal_r_requ
ested_closed, 0),
COALESCE(fees_r_requ
ested_closed, 0), COALESCE(num_open_ex
pired, 0), COALESCE(amt_open_ex
pired, 0),
COALESCE(fbal_open_e
xpired, 0), COALESCE(bal_open_ex
pired, 0), COALESCE(fees_open_e
xpired, 0),
COALESCE(num_closed_
expired, 0), COALESCE(amt_closed_
expired, 0), COALESCE(fbal_closed
_expired, 0),
COALESCE(bal_closed_
expired, 0), COALESCE(fees_closed
_expired, 0), COALESCE(num_open_su
ccessful, 0),
COALESCE(amt_open_su
ccessful, 0), COALESCE(fbal_open_s
uccessful, 0), COALESCE(bal_open_su
ccessful, 0),
COALESCE(fees_open_s
uccessful, 0), COALESCE(num_closed_
successful, 0), COALESCE(amt_closed_
successful, 0),
COALESCE(fbal_closed
_successful, 0), COALESCE(bal_closed_
successful, 0), COALESCE(fees_closed
_successful, 0)
-- Join account status crosstabs
FROM tmp_stats_dsb_crosst
ab_num s
{(2) ...
FULL JOIN tmp_stats_dsb_crosst
ab_amt USING (csid,case_upload_da
te)
FULL JOIN tmp_stats_dsb_crosst
ab_fbal USING (csid,case_upload_da
te)
FULL JOIN tmp_stats_dsb_crosst
ab_bal USING (csid,case_upload_da
te)
FULL JOIN tmp_stats_dsb_crosst
ab_fees USING (csid,case_upload_da
te)
.... }
{(1) ...
FULL JOIN tmp_stats_dsb_crosst
ab_num_open USING (csid,case_upload_da
te)
FULL JOIN tmp_stats_dsb_crosst
ab_amt_open USING (csid,case_upload_da
te)
FULL JOIN tmp_stats_dsb_crosst
ab_fbal_open USING (csid,case_upload_da
te)
FULL JOIN tmp_stats_dsb_crosst
ab_bal_open USING (csid,case_upload_da
te)
FULL JOIN tmp_stats_dsb_crosst
ab_fees_open USING (csid,case_upload_da
te)
FULL JOIN tmp_stats_dsb_crosst
ab_num_closed USING (csid,case_upload_da
te)
FULL JOIN tmp_stats_dsb_crosst
ab_amt_closed USING (csid,case_upload_da
te)
FULL JOIN tmp_stats_dsb_crosst
ab_fbal_closed USING (csid,case_upload_da
te)
FULL JOIN tmp_stats_dsb_crosst
ab_bal_closed USING (csid,case_upload_da
te)
FULL JOIN tmp_stats_dsb_crosst
ab_fees_closed USING (csid,case_upload_da
te)
.... }
... Please see email #2 (MAIL TOO LONG for pg-bugs LIST).........
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Tom Lane 2005-11-17, 1:24 pm |
| Matt <msubs@philips.org.uk> writes:
> Yesterday I added 20 more join statements to the query (identical to
> existing statements) and now the main query which would run in <30
> seconds, runs indefinitely with the process stuck at "PARSE" in version
> 7.4, or "BIND" in version 8.0.3.
You *really* need to rethink your database design :-( ... thirty-way
JOINs are just not a very good idea.
Having said that, though, the fact that they're all outer joins should
save you from an exponential blowup in planning time, because there's
only one legal join order. I'm not sure where the time is going.
If you'd be willing to send me a test case off-list, I'd be willing to
take a look. A convenient test case from my point of view would be a
SQL script that sets up the database plus another one containing the
slow query.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| Tom Lane 2005-11-18, 8:24 pm |
| Matt <msubs@philips.org.uk> writes:
[color=darkred]
> I've created a standalone test case, sending it privately.
Thanks for the test case. The problem turns out to be sloppy coding in
exprTypmod(): it recurses twice on the first argument of a COALESCE,
making for exponential growth in the time needed to process a deep nest
of COALESCE expressions ... which is exactly what your deeply nested
FULL JOINs produce for the join USING variables. The patch attached
fixes it for 8.0.* --- I've committed equivalent fixes as far back as
7.4, where the problem originated.
Moral: sometimes an apparently trivial inefficiency isn't so trivial.
regards, tom lane
Index: src/backend/parser/parse_expr.c
====================
====================
====================
=======
RCS file: /cvsroot/pgsql/src/backend/parser/parse_expr.c,v
retrieving revision 1.179.4.2
diff -c -r1.179.4.2 parse_expr.c
*** src/backend/parser/parse_expr.c 25 May 2005 02:17:55 -0000 1.179.4.2
--- src/backend/parser/parse_expr.c 18 Nov 2005 23:05:21 -0000
***************
*** 1562,1569 ****
int32 typmod;
ListCell *arg;
typmod = exprTypmod((Node *) linitial(cexpr->args));
! foreach(arg, cexpr->args)
{
Node *e = (Node *) lfirst(arg);
--- 1562,1573 ----
int32 typmod;
ListCell *arg;
+ if (exprType((Node *) linitial(cexpr->args)) != coalescetype)
+ return -1;
typmod = exprTypmod((Node *) linitial(cexpr->args));
! if (typmod < 0)
! return -1; /* no point in trying harder */
! for_each_cell(ar
g, lnext(list_head(cexp
r->args)))
{
Node *e = (Node *) lfirst(arg);
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
|
| > Thanks for the test case. The problem turns out to be sloppy coding in
> exprTypmod(): it recurses twice on the first argument of a COALESCE,
> making for exponential growth in the time needed to process a deep nest
> of COALESCE expressions ... which is exactly what your deeply nested
> FULL JOINs produce for the join USING variables. The patch attached
> fixes it for 8.0.* --- I've committed equivalent fixes as far back as
> 7.4, where the problem originated.
>
> Moral: sometimes an apparently trivial inefficiency isn't so trivial.
Interesting. Thanks Tom. You've saved me an unpleasant workaround.
Glad I could contribute *something*!
|
|
|
|
|