Thread: Huge query stalls at PARSE/BIND stage (1)

Huge query stalls at PARSE/BIND stage (1)

From

Matt

Date:

17 November 2005, 14:13:41

(-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,month,case_upload_date,
  num_live,amt_live,fbal_live,bal_live,fees_live,
  num_arrange,amt_arrange,fbal_arrange,bal_arrange,fees_arrange,
  num_fullypaid,amt_fullypaid,fbal_fullypaid,bal_fullypaid,fees_fullypaid,
  num_successful,amt_successful,fbal_successful,bal_successful,fees_successful,
  num_trace,amt_trace,fbal_trace,bal_trace,fees_trace,
  num_expired,amt_expired,fbal_expired,bal_expired,fees_expired,
  num_our_hold,amt_our_hold,fbal_our_hold,bal_our_hold,fees_our_hold,
  num_client_hold,amt_client_hold,fbal_client_hold,bal_client_hold,fees_client_hold,
  num_hold,amt_hold,fbal_hold,bal_hold,fees_hold,
  num_returned,amt_returned,fbal_returned,bal_returned,fees_returned,
  num_returned_err,amt_returned_err,fbal_returned_err,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,fees_365days,
  total_num_open,total_amt_open,total_fbal_open,total_bal_open,total_fees_open,total_collected_open,
  total_num_closed,total_amt_closed,total_fbal_closed,total_bal_closed,total_fees_closed,total_collected_closed,
  total_num,total_amt,total_fbal,total_bal,total_fees,total_collected,
  num_r_uncollectable,amt_r_uncollectable,fbal_r_uncollectable,bal_r_uncollectable,fees_r_uncollectable,
  num_r_collectable,amt_r_collectable,fbal_r_collectable,bal_r_collectable,fees_r_collectable,
  num_r_requested,amt_r_requested,fbal_r_requested,bal_r_requested,fees_r_requested,
  num_allocated,amt_allocated,fbal_allocated,bal_allocated,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_returned_err_open,amt_returned_err_open,fees_returned_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_requested_open,fbal_r_requested_open,fees_r_requested_open,
  num_returned_closed,amt_returned_closed,fbal_returned_closed,bal_returned_closed,fees_returned_closed,

num_returned_err_closed,bal_returned_err_closed,fbal_returned_err_closed,amt_returned_err_closed,fees_returned_err_closed,

num_r_uncollectable_closed,amt_r_uncollectable_closed,bal_r_uncollectable_closed,fbal_r_uncollectable_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_expired,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_successful,
  num_closed_successful,amt_closed_successful,fbal_closed_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)
ASfees_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
GROUPBY 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
GROUPBY 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).........

Re: Huge query stalls at PARSE/BIND stage (1)

From

Tom Lane

Date:

17 November 2005, 17:27:24

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

Re: Huge query stalls at PARSE/BIND stage (1)

From

Tom Lane

Date:

18 November 2005, 22:15:25

Matt <msubs@philips.org.uk> writes:
>> 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.

> 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(arg, lnext(list_head(cexpr->args)))
                  {
                      Node       *e = (Node *) lfirst(arg);

Re: Huge query stalls at PARSE/BIND stage (1)

From

Matt

Date:

21 November 2005, 08:01:06

> 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*!

Re: VERIFIED FIXED: Huge query stalls at PARSE/BIND stage

From

Matt

Date:

22 November 2005, 12:29:24

Patch applied to version 8.1. Large query now runs in seconds.
Thanks Tom. Regards, Matt Carter