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

Huge query stalls at PARSE/BIND stage (2)

From

Matt

Date:

17 November 2005, 10:38:31

... 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

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

From

Matt Carter

Date:

18 November 2005, 12:59:56

I've been investigating this problem further.

I've tried adding first just 2, then 4 more FULL JOINs to the statement.
This works and the statement
gets to INSERT and completes successfully.

If I add 16 further FULL JOINs however, it does not.

Regards,
Matt

P.S. Apologies for the grammar errors in the previous email. I was
exhausted yesterday.

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