Huge query stalls at PARSE/BIND stage (2) - Mailing list pgsql-bugs

... continued ...

   -- Join account hold crosstabs
   FULL JOIN tmp_stats_dhb_crosstab_num USING (csid,case_upload_date)
   FULL JOIN tmp_stats_dhb_crosstab_amt USING (csid,case_upload_date)
   FULL JOIN tmp_stats_dhb_crosstab_fbal USING (csid,case_upload_date)
   FULL JOIN tmp_stats_dhb_crosstab_bal USING (csid,case_upload_date)
   FULL JOIN tmp_stats_dhb_crosstab_fees USING (csid,case_upload_date)
   -- Join account return totals
   FULL JOIN (
     SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_date,
     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 NOT NULL AND return_code != 20 GROUP BY
csid,case_upload_date 
   ) total_drb USING (csid,case_upload_date)
   FULL JOIN (
     SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_date,
     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 NOT NULL AND return_code = 20 GROUP BY
csid,case_upload_date 
   ) total_drbe USING (csid,case_upload_date)
   -- Join account uncollectable/collectable/requested
   FULL JOIN (
     SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_date,
     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,case_upload_date
   ) total_rub USING (csid,case_upload_date)
   FULL JOIN (
     SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_date,
     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,case_upload_date
   ) total_rcb USING (csid,case_upload_date)
   FULL JOIN (
     SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_date,
     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,case_upload_date
   ) total_rrb USING (csid,case_upload_date)
   -- Join account age bands
   FULL JOIN (
     SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_date,
     num AS num_30days,amt AS amt_30days,fbal AS fbal_30days,bal AS bal_30days,fees AS fees_30days
     FROM tmp_stats_dab30
   ) total_dab30 USING (csid,case_upload_date)
   FULL JOIN (
     SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_date,
     num AS num_60days,amt AS amt_60days,fbal AS fbal_60days,bal AS bal_60days,fees AS fees_60days
     FROM tmp_stats_dab60
   ) total_dab60 USING (csid,case_upload_date)
   FULL JOIN (
     SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_date,
     num AS num_90days,amt AS amt_90days,fbal AS fbal_90days,bal AS bal_90days,fees AS fees_90days
     FROM tmp_stats_dab90
   ) total_dab90 USING (csid,case_upload_date)
   FULL JOIN (
     SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_date,
     num AS num_365days,amt AS amt_365days,fbal AS fbal_365days,bal AS bal_365days,fees AS fees_365days
     FROM tmp_stats_dab365
   ) total_dab365 USING (csid,case_upload_date)
   -- Join summary totals
   FULL JOIN (
     SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_date,
     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_zob
   ) zob USING (csid,case_upload_date)
   FULL JOIN (
     SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_date,
     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_zcb
   ) zcb USING (csid,case_upload_date)
   FULL JOIN (
     SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_date,
     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_zab
   ) zab USING (csid,case_upload_date)
   -- Join agent totals
   FULL JOIN (
     SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_date,
     num AS num_allocated,amt AS amt_allocated,fbal AS fbal_allocated,bal AS bal_allocated,fees AS fees_allocated
     FROM tmp_stats_bab
   ) bab USING (csid,case_upload_date)


   ORDER BY csid,year,month,case_upload_date;

The destination table looks like this:

              Table "public.pws_stats_case"
           Column            |     Type      | Modifiers
-----------------------------+---------------+-----------
 batch_id                    | integer       |
 csid                        | integer       | not null
 year                        | integer       |
 month                       | integer       |
 case_upload_date            | date          |
 num_live                    | integer       |
 bal_live                    | numeric(12,2) |
 amt_live                    | numeric(12,2) |
 fees_live                   | numeric(12,2) |
 num_arrange                 | integer       |
 bal_arrange                 | numeric(12,2) |
 amt_arrange                 | numeric(12,2) |
 fees_arrange                | numeric(12,2) |
 num_trace                   | integer       |
 bal_trace                   | numeric(12,2) |
 amt_trace                   | numeric(12,2) |
 fees_trace                  | numeric(12,2) |
 num_successful_remitted     | integer       |
 bal_successful_remitted     | numeric(12,2) |
 amt_successful_remitted     | numeric(12,2) |
 fees_successful_remitted    | numeric(12,2) |
 num_expired                 | integer       |
 bal_expired                 | numeric(12,2) |
 amt_expired                 | numeric(12,2) |
 fees_expired                | numeric(12,2) |
 num_fullypaid               | integer       |
 bal_fullypaid               | numeric(12,2) |
 amt_fullypaid               | numeric(12,2) |
 fees_fullypaid              | numeric(12,2) |
 num_successful              | integer       |
 bal_successful              | numeric(12,2) |
 amt_successful              | numeric(12,2) |
 fees_successful             | numeric(12,2) |
 num_our_hold                | integer       |
 bal_our_hold                | numeric(12,2) |
 amt_our_hold                | numeric(12,2) |
 fees_our_hold               | numeric(12,2) |
 num_client_hold             | integer       |
 bal_client_hold             | numeric(12,2) |
 amt_client_hold             | numeric(12,2) |
 fees_client_hold            | numeric(12,2) |
 num_hold                    | integer       |
 bal_hold                    | numeric(12,2) |
 amt_hold                    | numeric(12,2) |
 fees_hold                   | numeric(12,2) |
 num_allocated               | integer       |
 bal_allocated               | numeric(12,2) |
 amt_allocated               | numeric(12,2) |
 fees_allocated              | numeric(12,2) |
 num_30days                  | integer       |
 bal_30days                  | numeric(12,2) |
 amt_30days                  | numeric(12,2) |
 fees_30days                 | numeric(12,2) |
 num_60days                  | integer       |
 bal_60days                  | numeric(12,2) |
 amt_60days                  | numeric(12,2) |
 fees_60days                 | numeric(12,2) |
 num_90days                  | integer       |
 bal_90days                  | numeric(12,2) |
 amt_90days                  | numeric(12,2) |
 fees_90days                 | numeric(12,2) |
 num_365days                 | integer       |
 bal_365days                 | numeric(12,2) |
 amt_365days                 | numeric(12,2) |
 fees_365days                | numeric(12,2) |
 num_returned                | integer       |
 bal_returned                | numeric(12,2) |
 amt_returned                | numeric(12,2) |
 fees_returned               | numeric(12,2) |
 num_returned_err            | integer       |
 bal_returned_err            | numeric(12,2) |
 amt_returned_err            | numeric(12,2) |
 fees_returned_err           | numeric(12,2) |
 total_num                   | integer       |
 total_amt                   | numeric(12,2) |
 total_bal                   | numeric(12,2) |
 total_fees                  | numeric(12,2) |
 total_collected             | numeric(12,2) |
 total_num_open              | integer       |
 total_amt_open              | numeric(12,2) |
 total_bal_open              | numeric(12,2) |
 total_fees_open             | numeric(12,2) |
 total_collected_open        | numeric(12,2) |
 total_num_closed            | integer       |
 total_amt_closed            | numeric(12,2) |
 total_bal_closed            | numeric(12,2) |
 total_fees_closed           | numeric(12,2) |
 total_collected_closed      | numeric(12,2) |
 num_r_uncollectable         | integer       |
 amt_r_uncollectable         | numeric(12,2) |
 bal_r_uncollectable         | numeric(12,2) |
 fees_r_uncollectable        | numeric(12,2) |
 num_r_collectable           | integer       |
 amt_r_collectable           | numeric(12,2) |
 bal_r_collectable           | numeric(12,2) |
 fees_r_collectable          | numeric(12,2) |
 num_r_requested             | integer       |
 amt_r_requested             | numeric(12,2) |
 bal_r_requested             | numeric(12,2) |
 fees_r_requested            | numeric(12,2) |
 fbal_client_hold            | numeric(12,2) |
 fbal_60days                 | numeric(12,2) |
 fbal_90days                 | numeric(12,2) |
 fbal_365days                | numeric(12,2) |
 fbal_live                   | numeric(12,2) |
 fbal_arrange                | numeric(12,2) |
 fbal_trace                  | numeric(12,2) |
 fbal_expired                | numeric(12,2) |
 fbal_fullypaid              | numeric(12,2) |
 fbal_successful             | numeric(12,2) |
 fbal_our_hold               | numeric(12,2) |
 fbal_hold                   | numeric(12,2) |
 fbal_allocated              | numeric(12,2) |
 fbal_30days                 | numeric(12,2) |
 fbal_returned               | numeric(12,2) |
 fbal_returned_err           | numeric(12,2) |
 total_fbal                  | numeric(12,2) |
 total_fbal_open             | numeric(12,2) |
 total_fbal_closed           | numeric(12,2) |
 fbal_r_uncollectable        | numeric(12,2) |
 fbal_r_collectable          | numeric(12,2) |
 fbal_r_requested            | numeric(12,2) |
 series                      | character(1)  |
 num_returned_closed         | integer       |
 num_returned_err_closed     | integer       |
 bal_returned_err_closed     | numeric(12,2) |
 fbal_returned_err_closed    | numeric(12,2) |
 amt_returned_err_closed     | numeric(12,2) |
 fees_returned_err_closed    | numeric(12,2) |
 num_r_uncollectable_closed  | integer       |
 amt_r_uncollectable_closed  | numeric(12,2) |
 bal_r_uncollectable_closed  | numeric(12,2) |
 fbal_r_uncollectable_closed | numeric(12,2) |
 fees_r_uncollectable_closed | numeric(12,2) |
 num_r_collectable_closed    | integer       |
 amt_r_collectable_closed    | numeric(12,2) |
 bal_r_collectable_closed    | numeric(12,2) |
 fbal_r_collectable_closed   | numeric(12,2) |
 fees_r_collectable_closed   | numeric(12,2) |
 num_r_requested_closed      | integer       |
 amt_r_requested_closed      | numeric(12,2) |
 bal_r_requested_closed      | numeric(12,2) |
 fbal_r_requested_closed     | numeric(12,2) |
 fees_r_requested_closed     | numeric(12,2) |
 num_returned_err_open       | integer       |
 bal_returned_err_open       | numeric(12,2) |
 fbal_returned_err_open      | numeric(12,2) |
 amt_returned_err_open       | numeric(12,2) |
 fees_returned_err_open      | numeric(12,2) |
 num_r_uncollectable_open    | integer       |
 amt_r_uncollectable_open    | numeric(12,2) |
 bal_r_uncollectable_open    | numeric(12,2) |
 fbal_r_uncollectable_open   | numeric(12,2) |
 fees_r_uncollectable_open   | numeric(12,2) |
 num_r_collectable_open      | integer       |
 amt_r_collectable_open      | numeric(12,2) |
 bal_r_collectable_open      | numeric(12,2) |
 fbal_r_collectable_open     | numeric(12,2) |
 fees_r_collectable_open     | numeric(12,2) |
 num_r_requested_open        | integer       |
 amt_r_requested_open        | numeric(12,2) |
 bal_r_requested_open        | numeric(12,2) |
 fbal_r_requested_open       | numeric(12,2) |
 fees_r_requested_open       | numeric(12,2) |
 num_returned_open           | integer       |
 bal_returned_open           | numeric(12,2) |
 fbal_returned_open          | numeric(12,2) |
 amt_returned_open           | numeric(12,2) |
 fees_returned_open          | numeric(12,2) |
 amt_returned_closed         | numeric(12,2) |
 bal_returned_closed         | numeric(12,2) |
 fbal_returned_closed        | numeric(12,2) |
 fees_returned_closed        | numeric(12,2) |
 num_open_expired            | integer       |
 amt_open_expired            | numeric(12,2) |
 bal_open_expired            | numeric(12,2) |
 fbal_open_expired           | numeric(12,2) |
 fees_open_expired           | numeric(12,2) |
 amt_closed_expired          | numeric(12,2) |
 bal_closed_expired          | numeric(12,2) |
 fbal_closed_expired         | numeric(12,2) |
 fees_closed_expired         | numeric(12,2) |
 num_closed_expired          | integer       |
 num_open_successful         | integer       |
 amt_open_successful         | numeric(12,2) |
 bal_open_successful         | numeric(12,2) |
 fbal_open_successful        | numeric(12,2) |
 fees_open_successful        | numeric(12,2) |
 num_closed_successful       | integer       |
 amt_closed_successful       | numeric(12,2) |
 bal_closed_successful       | numeric(12,2) |
 fbal_closed_successful      | numeric(12,2) |
 fees_closed_successful      | numeric(12,2) |
Indexes:
    "pws_stats_case_batch_id_key" UNIQUE, btree (batch_id, csid, "year", "month", case_upload_date)
    "pws_stats_case_link_idx" btree (batch_id, csid, "month", "year")
Foreign-key constraints:
    "$1" FOREIGN KEY (batch_id) REFERENCES pws_stats(id)
    "$2" FOREIGN KEY (csid) REFERENCES pws_client_schemes(id)



The added statements above are marked up with {(1) ...  } and
the similar existing statements are marked up with {(2) ...  }.


Here are some of the temporary source tables:


--- One of the existing, working join source tables
Table "public.tmp_stats_dsm_crosstab_amt"
     Column     |  Type   | Modifiers
----------------+---------+-----------
 csid           | integer |
 year           | integer |
 month          | integer |
 amt_arrange    | numeric |
 amt_cancelled  | numeric |
 amt_fullypaid  | numeric |
 amt_live       | numeric |
 amt_successful | numeric |
 amt_trace      | numeric |
 amt_expired    | numeric |


-- One of the additional, similar join source tables
pws=# \d tmp_stats_dsm_crosstab_amt_open
Table "public.tmp_stats_dsm_crosstab_amt_open"
       Column        |  Type   | Modifiers
---------------------+---------+-----------
 csid                | integer |
 year                | integer |
 month               | integer |
 amt_open_arrange    | numeric |
 amt_open_cancelled  | numeric |
 amt_open_fullypaid  | numeric |
 amt_open_live       | numeric |
 amt_open_successful | numeric |
 amt_open_trace      | numeric |
 amt_open_expired    | numeric |


Please help.. I'm stuck for ideas and need the new columns.
Am I hitting some kind of limit that is not error-handled?

More data/source code/table dumps are available to developers on request.

Regards,
Matt Carter