(Resending)

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,month,case_upload_date,
num_live,amt_live,fbal_live,bal_live,fees_live,
num_arrange,amt_arrange,fbal_arrange,bal_arrange,f ees_arrange,
num_fullypaid,amt_fullypaid,fbal_fullypaid,bal_ful lypaid,fees_fullypaid,
num_successful,amt_successful,fbal_successful,bal_ successful,fees_successful,
num_trace,amt_trace,fbal_trace,bal_trace,fees_trac e,
num_expired,amt_expired,fbal_expired,bal_expired,f ees_expired,
num_our_hold,amt_our_hold,fbal_our_hold,bal_our_ho ld,fees_our_hold,
num_client_hold,amt_client_hold,fbal_client_hold,b al_client_hold,fees_client_hold,
num_hold,amt_hold,fbal_hold,bal_hold,fees_hold,
num_returned,amt_returned,fbal_returned,bal_return ed,fees_returned,
num_returned_err,amt_returned_err,fbal_returned_er r,bal_returned_err,fees_returned_err,
num_30days,amt_30days,fbal_30days,bal_30days,fees_ 30days,
num_60days,amt_60days,fbal_60days,bal_60days,fees_ 60days,
num_90days,amt_90days,fbal_90days,bal_90days,fees_ 90days,
num_365days,amt_365days,fbal_365days,bal_365days,f ees_365days,
total_num_open,total_amt_open,total_fbal_open,tota l_bal_open,total_fees_open,total_collected_open,
total_num_closed,total_amt_closed,total_fbal_close d,total_bal_closed,total_fees_closed,total_collect ed_closed,
total_num,total_amt,total_fbal,total_bal,total_fee s,total_collected,
num_r_uncollectable,amt_r_uncollectable,fbal_r_unc ollectable,bal_r_uncollectable,fees_r_uncollectabl e,
num_r_collectable,amt_r_collectable,fbal_r_collect able,bal_r_collectable,fees_r_collectable,
num_r_requested,amt_r_requested,fbal_r_requested,b al_r_requested,fees_r_requested,
num_allocated,amt_allocated,fbal_allocated,bal_all ocated,fees_allocated,
num_returned_open,amt_returned_open,fbal_returned_ open,bal_returned_open,fees_returned_open,
num_returned_err_open,bal_returned_err_open,fbal_r eturned_err_open,amt_returned_err_open,fees_return ed_err_open,
num_r_uncollectable_open,amt_r_uncollectable_open, bal_r_uncollectable_open,fbal_r_uncollectable_open ,fees_r_uncollectable_open,
num_r_collectable_open,amt_r_collectable_open,bal_ r_collectable_open,fbal_r_collectable_open,fees_r_ collectable_open,
num_r_requested_open,amt_r_requested_open,bal_r_re quested_open,fbal_r_requested_open,fees_r_requeste d_open,
num_returned_closed,amt_returned_closed,fbal_retur ned_closed,bal_returned_closed,fees_returned_close d,
num_returned_err_closed,bal_returned_err_closed,fb al_returned_err_closed,amt_returned_err_closed,fee s_returned_err_closed,
num_r_uncollectable_closed,amt_r_uncollectable_clo sed,bal_r_uncollectable_closed,fbal_r_uncollectabl e_closed,fees_r_uncollectable_closed,
num_r_collectable_closed,amt_r_collectable_closed, bal_r_collectable_closed,fbal_r_collectable_closed ,fees_r_collectable_closed,
num_r_requested_closed,amt_r_requested_closed,bal_ r_requested_closed,fbal_r_requested_closed,fees_r_ requested_closed,
num_open_expired,amt_open_expired,fbal_open_expire d,bal_open_expired,fees_open_expired,
num_closed_expired,amt_closed_expired,fbal_closed_ expired,bal_closed_expired,fees_closed_expired,
num_open_successful,amt_open_successful,fbal_open_ successful,bal_open_successful,fees_open_successfu l,
num_closed_successful,amt_closed_successful,fbal_c losed_successful,bal_closed_successful,fees_closed _successful
)

-- Select query, huge union in two parts.
-- part one's tables are keyed on (csid=?,year=?,month=?,case_upload_date=null),
-- part two's tables are keyed on (csid=?,year=0,month=0,case_upload_date=?)

SELECT ?,csid,year,month,NULL::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_arrange, 0), COALESCE(bal_arrange, 0),
COALESCE(fees_arrange, 0), COALESCE(num_fullypaid, 0), COALESCE(amt_fullypaid, 0), COALESCE(fbal_fullypaid, 0),
COALESCE(bal_fullypaid, 0), COALESCE(fees_fullypaid, 0), COALESCE(num_successful, 0), COALESCE(amt_successful, 0),
COALESCE(fbal_successful, 0), COALESCE(bal_successful, 0), COALESCE(fees_successful, 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_expired, 0), COALESCE(bal_expired, 0), COALESCE(fees_expired, 0),
COALESCE(num_our_hold, 0), COALESCE(amt_our_hold, 0), COALESCE(fbal_our_hold, 0), COALESCE(bal_our_hold, 0),
COALESCE(fees_our_hold, 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_returned, 0),
COALESCE(amt_returned, 0), COALESCE(fbal_returned, 0), COALESCE(bal_returned, 0), COALESCE(fees_returned, 0),
COALESCE(num_returned_err, 0), COALESCE(amt_returned_err, 0), COALESCE(fbal_returned_err, 0),
COALESCE(bal_returned_err, 0), COALESCE(fees_returned_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_365days, 0),
COALESCE(bal_365days, 0), COALESCE(fees_365days, 0), COALESCE(total_num_open, 0), COALESCE(total_amt_open, 0),
COALESCE(total_fbal_open, 0), COALESCE(total_bal_open, 0), COALESCE(total_fees_open, 0), COALESCE(total_collected_open, 0),
COALESCE(total_num_closed, 0), COALESCE(total_amt_closed, 0), COALESCE(total_fbal_closed, 0), COALESCE(total_bal_closed, 0),
COALESCE(total_fees_closed, 0), COALESCE(total_collected_closed, 0), COALESCE(total_num, 0), COALESCE(total_amt, 0),
COALESCE(total_fbal, 0), COALESCE(total_bal, 0), COALESCE(total_fees, 0), COALESCE(total_collected, 0),
COALESCE(num_r_uncollectable, 0), COALESCE(amt_r_uncollectable, 0), COALESCE(fbal_r_uncollectable, 0),
COALESCE(bal_r_uncollectable, 0), COALESCE(fees_r_uncollectable, 0), COALESCE(num_r_collectable, 0),
COALESCE(amt_r_collectable, 0), COALESCE(fbal_r_collectable, 0), COALESCE(bal_r_collectable, 0),
COALESCE(fees_r_collectable, 0), COALESCE(num_r_requested, 0), COALESCE(amt_r_requested, 0), COALESCE(fbal_r_requested, 0),
COALESCE(bal_r_requested, 0), COALESCE(fees_r_requested, 0), COALESCE(num_allocated, 0), COALESCE(amt_allocated, 0),
COALESCE(fbal_allocated, 0), COALESCE(bal_allocated, 0), COALESCE(fees_allocated, 0), COALESCE(num_returned_open, 0),
COALESCE(amt_returned_open, 0), COALESCE(fbal_returned_open, 0), COALESCE(bal_returned_open, 0),
COALESCE(fees_returned_open, 0), COALESCE(num_returned_err_open, 0), COALESCE(bal_returned_err_open, 0),
COALESCE(fbal_returned_err_open, 0), COALESCE(amt_returned_err_open, 0), COALESCE(fees_returned_err_open, 0),
COALESCE(num_r_uncollectable_open, 0), COALESCE(amt_r_uncollectable_open, 0), COALESCE(bal_r_uncollectable_open, 0),
COALESCE(fbal_r_uncollectable_open, 0), COALESCE(fees_r_uncollectable_open, 0), COALESCE(num_r_collectable_open, 0),
COALESCE(amt_r_collectable_open, 0), COALESCE(bal_r_collectable_open, 0), COALESCE(fbal_r_collectable_open, 0),
COALESCE(fees_r_collectable_open, 0), COALESCE(num_r_requested_open, 0), COALESCE(amt_r_requested_open, 0),
COALESCE(bal_r_requested_open, 0), COALESCE(fbal_r_requested_open, 0), COALESCE(fees_r_requested_open, 0),
COALESCE(num_returned_closed, 0), COALESCE(amt_returned_closed, 0), COALESCE(fbal_returned_closed, 0),
COALESCE(bal_returned_closed, 0), COALESCE(fees_returned_closed, 0), COALESCE(num_returned_err_closed, 0),
COALESCE(bal_returned_err_closed, 0), COALESCE(fbal_returned_err_closed, 0), COALESCE(amt_returned_err_closed, 0),
COALESCE(fees_returned_err_closed, 0), COALESCE(num_r_uncollectable_closed, 0), COALESCE(amt_r_uncollectable_closed, 0), COALESCE(bal_r_uncollectable_closed, 0), COALESCE(fbal_r_uncollectable_closed, 0), COALESCE(fees_r_uncollectable_closed, 0),
COALESCE(num_r_collectable_closed, 0), COALESCE(amt_r_collectable_closed, 0), COALESCE(bal_r_collectable_closed, 0),
COALESCE(fbal_r_collectable_closed, 0), COALESCE(fees_r_collectable_closed, 0), COALESCE(num_r_requested_closed, 0),
COALESCE(amt_r_requested_closed, 0), COALESCE(bal_r_requested_closed, 0), COALESCE(fbal_r_requested_closed, 0),
COALESCE(fees_r_requested_closed, 0), COALESCE(num_open_expired, 0), COALESCE(amt_open_expired, 0),
COALESCE(fbal_open_expired, 0), COALESCE(bal_open_expired, 0), COALESCE(fees_open_expired, 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_successful, 0),
COALESCE(amt_open_successful, 0), COALESCE(fbal_open_successful, 0), COALESCE(bal_open_successful, 0),
COALESCE(fees_open_successful, 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_crosstab_num s

{(2) ...
FULL JOIN tmp_stats_dsm_crosstab_amt USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosstab_fbal USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosstab_bal USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosstab_fees USING (csid,year,month)
.... }

{(1) ...
FULL JOIN tmp_stats_dsm_crosstab_num_open USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosstab_amt_open USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosstab_fbal_open USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosstab_bal_open USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosstab_fees_open USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosstab_num_closed USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosstab_amt_closed USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosstab_fbal_closed USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosstab_bal_closed USING (csid,year,month)
FULL JOIN tmp_stats_dsm_crosstab_fees_closed USING (csid,year,month)
.... }

-- Join account hold crosstabs
FULL JOIN tmp_stats_dhm_crosstab_num USING (csid,year,month)
FULL JOIN tmp_stats_dhm_crosstab_amt USING (csid,year,month)
FULL JOIN tmp_stats_dhm_crosstab_fbal USING (csid,year,month)
FULL JOIN tmp_stats_dhm_crosstab_bal USING (csid,year,month)
FULL JOIN tmp_stats_dhm_crosstab_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(fbal) AS fbal_returned, sum(bal) AS bal_returned,sum(fees) AS fees_returned,
sum(num_open) AS num_returned_open,sum(amt_open) AS amt_returned_open,sum(fbal_open) AS fbal_returned_open, sum(bal) AS bal_returned_open,sum(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_open,sum(amt_open) AS amt_returned_err_open,sum(fbal_open) AS fbal_returned_err_open, sum(bal) AS bal_returned_err_open,sum(fees_open) AS fees_returned_err_open,
sum(num_closed) AS num_returned_err_closed,sum(amt_closed) AS amt_returned_err_closed,sum(fbal_closed) AS fbal_returned_err_closed, sum(bal) AS bal_returned_err_closed,sum(fees_closed) AS fees_returned_err_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_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_closed) AS amt_r_uncollectable_closed,sum(fbal_closed) AS fbal_r_uncollectable_closed, sum(bal) AS bal_r_uncollectable_closed,sum(fees_closed) 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,sum(amt) AS amt_r_collectable,sum(fbal) AS fbal_r_collectable, sum(bal) AS bal_r_collectable,sum(fees) AS fees_r_collectable,
sum(num_open) AS num_r_collectable_open,sum(amt_open) AS amt_r_collectable_open,sum(fbal_open) AS fbal_r_collectable_open, sum(bal) AS bal_r_collectable_open,sum(fees_open) AS fees_r_collectable_open,
sum(num_closed) AS num_r_collectable_closed,sum(amt_closed) AS amt_r_collectable_closed,sum(fbal_closed) AS fbal_r_collectable_closed, sum(bal) AS bal_r_collectable_closed,sum(fees_closed) AS fees_r_collectable_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 = '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_open, sum(bal) AS bal_r_requested_open,sum(fees_open) AS fees_r_requested_open,
sum(num_closed) AS num_r_requested_closed,sum(amt_closed) AS amt_r_requested_closed,sum(fbal_closed) AS fbal_r_requested_closed, sum(bal) AS bal_r_requested_closed,sum(fees_closed) AS fees_r_requested_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.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,fbal AS total_fbal_closed,bal AS total_bal_closed,fees AS total_fees_closed,(amt-bal) AS total_collected_closed
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_date,

-- 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_arrange, 0), COALESCE(bal_arrange, 0),
COALESCE(fees_arrange, 0), COALESCE(num_fullypaid, 0), COALESCE(amt_fullypaid, 0), COALESCE(fbal_fullypaid, 0),
COALESCE(bal_fullypaid, 0), COALESCE(fees_fullypaid, 0), COALESCE(num_successful, 0), COALESCE(amt_successful, 0),
COALESCE(fbal_successful, 0), COALESCE(bal_successful, 0), COALESCE(fees_successful, 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_expired, 0), COALESCE(bal_expired, 0), COALESCE(fees_expired, 0),
COALESCE(num_our_hold, 0), COALESCE(amt_our_hold, 0), COALESCE(fbal_our_hold, 0), COALESCE(bal_our_hold, 0),
COALESCE(fees_our_hold, 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_returned, 0),
COALESCE(amt_returned, 0), COALESCE(fbal_returned, 0), COALESCE(bal_returned, 0), COALESCE(fees_returned, 0),
COALESCE(num_returned_err, 0), COALESCE(amt_returned_err, 0), COALESCE(fbal_returned_err, 0),
COALESCE(bal_returned_err, 0), COALESCE(fees_returned_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_365days, 0),
COALESCE(bal_365days, 0), COALESCE(fees_365days, 0), COALESCE(total_num_open, 0), COALESCE(total_amt_open, 0),
COALESCE(total_fbal_open, 0), COALESCE(total_bal_open, 0), COALESCE(total_fees_open, 0), COALESCE(total_collected_open, 0),
COALESCE(total_num_closed, 0), COALESCE(total_amt_closed, 0), COALESCE(total_fbal_closed, 0), COALESCE(total_bal_closed, 0),
COALESCE(total_fees_closed, 0), COALESCE(total_collected_closed, 0), COALESCE(total_num, 0), COALESCE(total_amt, 0),
COALESCE(total_fbal, 0), COALESCE(total_bal, 0), COALESCE(total_fees, 0), COALESCE(total_collected, 0),
COALESCE(num_r_uncollectable, 0), COALESCE(amt_r_uncollectable, 0), COALESCE(fbal_r_uncollectable, 0),
COALESCE(bal_r_uncollectable, 0), COALESCE(fees_r_uncollectable, 0), COALESCE(num_r_collectable, 0),
COALESCE(amt_r_collectable, 0), COALESCE(fbal_r_collectable, 0), COALESCE(bal_r_collectable, 0),
COALESCE(fees_r_collectable, 0), COALESCE(num_r_requested, 0), COALESCE(amt_r_requested, 0), COALESCE(fbal_r_requested, 0),
COALESCE(bal_r_requested, 0), COALESCE(fees_r_requested, 0), COALESCE(num_allocated, 0), COALESCE(amt_allocated, 0),
COALESCE(fbal_allocated, 0), COALESCE(bal_allocated, 0), COALESCE(fees_allocated, 0), COALESCE(num_returned_open, 0),
COALESCE(amt_returned_open, 0), COALESCE(fbal_returned_open, 0), COALESCE(bal_returned_open, 0),
COALESCE(fees_returned_open, 0), COALESCE(num_returned_err_open, 0), COALESCE(bal_returned_err_open, 0),
COALESCE(fbal_returned_err_open, 0), COALESCE(amt_returned_err_open, 0), COALESCE(fees_returned_err_open, 0),
COALESCE(num_r_uncollectable_open, 0), COALESCE(amt_r_uncollectable_open, 0), COALESCE(bal_r_uncollectable_open, 0),
COALESCE(fbal_r_uncollectable_open, 0), COALESCE(fees_r_uncollectable_open, 0), COALESCE(num_r_collectable_open, 0),
COALESCE(amt_r_collectable_open, 0), COALESCE(bal_r_collectable_open, 0), COALESCE(fbal_r_collectable_open, 0),
COALESCE(fees_r_collectable_open, 0), COALESCE(num_r_requested_open, 0), COALESCE(amt_r_requested_open, 0),
COALESCE(bal_r_requested_open, 0), COALESCE(fbal_r_requested_open, 0), COALESCE(fees_r_requested_open, 0),
COALESCE(num_returned_closed, 0), COALESCE(amt_returned_closed, 0), COALESCE(fbal_returned_closed, 0),
COALESCE(bal_returned_closed, 0), COALESCE(fees_returned_closed, 0), COALESCE(num_returned_err_closed, 0),
COALESCE(bal_returned_err_closed, 0), COALESCE(fbal_returned_err_closed, 0), COALESCE(amt_returned_err_closed, 0),
COALESCE(fees_returned_err_closed, 0), COALESCE(num_r_uncollectable_closed, 0), COALESCE(amt_r_uncollectable_closed, 0), COALESCE(bal_r_uncollectable_closed, 0), COALESCE(fbal_r_uncollectable_closed, 0), COALESCE(fees_r_uncollectable_closed, 0),
COALESCE(num_r_collectable_closed, 0), COALESCE(amt_r_collectable_closed, 0), COALESCE(bal_r_collectable_closed, 0),
COALESCE(fbal_r_collectable_closed, 0), COALESCE(fees_r_collectable_closed, 0), COALESCE(num_r_requested_closed, 0),
COALESCE(amt_r_requested_closed, 0), COALESCE(bal_r_requested_closed, 0), COALESCE(fbal_r_requested_closed, 0),
COALESCE(fees_r_requested_closed, 0), COALESCE(num_open_expired, 0), COALESCE(amt_open_expired, 0),
COALESCE(fbal_open_expired, 0), COALESCE(bal_open_expired, 0), COALESCE(fees_open_expired, 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_successful, 0),
COALESCE(amt_open_successful, 0), COALESCE(fbal_open_successful, 0), COALESCE(bal_open_successful, 0),
COALESCE(fees_open_successful, 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_crosstab_num s

{(2) ...
FULL JOIN tmp_stats_dsb_crosstab_amt USING (csid,case_upload_date)
FULL JOIN tmp_stats_dsb_crosstab_fbal USING (csid,case_upload_date)
FULL JOIN tmp_stats_dsb_crosstab_bal USING (csid,case_upload_date)
FULL JOIN tmp_stats_dsb_crosstab_fees USING (csid,case_upload_date)
.... }

{(1) ...
FULL JOIN tmp_stats_dsb_crosstab_num_open USING (csid,case_upload_date)
FULL JOIN tmp_stats_dsb_crosstab_amt_open USING (csid,case_upload_date)
FULL JOIN tmp_stats_dsb_crosstab_fbal_open USING (csid,case_upload_date)
FULL JOIN tmp_stats_dsb_crosstab_bal_open USING (csid,case_upload_date)
FULL JOIN tmp_stats_dsb_crosstab_fees_open USING (csid,case_upload_date)
FULL JOIN tmp_stats_dsb_crosstab_num_closed USING (csid,case_upload_date)
FULL JOIN tmp_stats_dsb_crosstab_amt_closed USING (csid,case_upload_date)
FULL JOIN tmp_stats_dsb_crosstab_fbal_closed USING (csid,case_upload_date)
FULL JOIN tmp_stats_dsb_crosstab_bal_closed USING (csid,case_upload_date)
FULL JOIN tmp_stats_dsb_crosstab_fees_closed USING (csid,case_upload_date)
.... }


... Please see email #2 (MAIL TOO LONG for pg-bugs LIST).........



***********************************************
IMPORTANT: This email and any attachments may be confidential and/or privileged. Everything is intended for use of the addressee only. If you are not the named addressee you must not disseminate, distribute or copy this email. If you receive this email in error please notify the sender by replying to this email or by telephoning (+44)(0)1325 383876 then delete this message from your system. Philips Collection Services Ltd. ("Philips") routinely monitors the content of email sent and received on its network, to ensure compliance with its policies and procedures. Although Philips have taken reasonable precautions to ensure no viruses are present in this email or any files attached to it, it cannot accept any responsibility for any loss or damage arising from the use of this email or its attachments and advises you to carry out appropriate virus checks. Philips are not responsible for any changes made to the message after it has been sent nor any files attached to it after it wa
s sent. Emails that contain encrypted material, program files, are obscene, inflammatory, criminal, offensive, in breach of copyright, contain a virus or threat to computer systems, appear to be a threat to the company or in breach of company policy may be intercepted and/or deleted. Philips does not accept any liability for any statements made which are clearly the sender's own and not made on behalf of Philips.
***********************************************

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Matt <msubs (AT) philips (DOT) org.uk> writes:
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(arg, lnext(list_head(cexpr->args)))
{
Node *e = (Node *) lfirst(arg);


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster