Home > Archive > PostgreSQL Bugs > November 2005 > Huge query stalls at PARSE/BIND stage (2)









You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

 

Author Huge query stalls at PARSE/BIND stage (2)
Matt

2005-11-17, 9:24 am

.... continued ...

-- Join account hold crosstabs
FULL JOIN tmp_stats_dhb_crosst
ab_num USING (csid,case_upload_da
te)
FULL JOIN tmp_stats_dhb_crosst
ab_amt USING (csid,case_upload_da
te)
FULL JOIN tmp_stats_dhb_crosst
ab_fbal USING (csid,case_upload_da
te)
FULL JOIN tmp_stats_dhb_crosst
ab_bal USING (csid,case_upload_da
te)
FULL JOIN tmp_stats_dhb_crosst
ab_fees USING (csid,case_upload_da
te)
-- Join account return totals
FULL JOIN (
SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_da
te,
sum(num) AS num_returned,sum(amt
) AS amt_returned,sum(fba
l) AS fbal_returned, sum(bal) AS bal_returned,sum(fee
s) AS fees_returned,
sum(num_open) AS num_returned_open,su
m(amt_open) AS amt_returned_open,su
m(fbal_open) AS fbal_returned_open, sum(bal) AS bal_returned_open,su
m(fees_open) AS fees_returned_open,
sum(num_closed) AS num_returned_closed,
sum(amt_closed) AS amt_returned_closed,
sum(fbal_closed) AS fbal_returned_closed
, sum(bal) AS bal_returned_closed,
sum(fees_closed) AS fees_returned_closed

FROM pws_stats_returns WHERE batch_id = ? AND case_upload_date IS NOT NULL AND return_code != 20 GROUP BY csid,case_upload_dat
e
) total_drb USING (csid,case_upload_da
te)
FULL JOIN (
SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_da
te,
sum(num) AS num_returned_err,sum
(amt) AS amt_returned_err,sum
(fbal) AS fbal_returned_err,su
m(bal) AS bal_returned_err,sum
(fees) AS fees_returned_err,
sum(num_open) AS num_returned_err_ope
n,sum(amt_open) AS amt_returned_err_ope
n,sum(fbal_open) AS fbal_returned_err_op
en, sum(bal) AS bal_returned_err_ope
n,sum(fees_open) AS fees_returned_err_op
en,
sum(num_closed) AS num_returned_err_clo
sed,sum(amt_closed) AS amt_returned_err_clo
sed,sum(fbal_closed)
AS fbal_returned_err_cl
osed, sum(bal) AS bal_returned_err_clo
sed,sum(fees_closed)
AS fees_returned_err_cl
osed
FROM pws_stats_returns WHERE batch_id = ? AND case_upload_date IS NOT NULL AND return_code = 20 GROUP BY csid,case_upload_dat
e
) total_drbe USING (csid,case_upload_da
te)
-- Join account uncollectable/collectable/requested
FULL JOIN (
SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_da
te,
sum(num) AS num_r_uncollectable,
sum(amt) AS amt_r_uncollectable,
sum(fbal) AS fbal_r_uncollectable
, sum(bal) AS bal_r_uncollectable,
sum(fees) AS fees_r_uncollectable
,
sum(num_open) AS num_r_uncollectable_
open,sum(amt_open) AS amt_r_uncollectable_
open,sum(fbal_open) AS fbal_r_uncollectable
_open, sum(bal) AS bal_r_uncollectable_
open,sum(fees_open) AS fees_r_uncollectable
_open,
sum(num_closed) AS num_r_uncollectable_
closed,sum(amt_close
d) AS amt_r_uncollectable_
closed,sum(fbal_clos
ed) AS fbal_r_uncollectable
_closed, sum(bal) AS bal_r_uncollectable_
closed,sum(fees_clos
ed) AS fees_r_uncollectable
_closed
FROM pws_stats_returns, pws_return_codes rc WHERE batch_id = ? AND case_upload_date IS NULL AND year IS NOT NULL
AND return_code = rc.id AND rc.nature = 'U' AND return_code != 20 GROUP BY csid,case_upload_dat
e
) total_rub USING (csid,case_upload_da
te)
FULL JOIN (
SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_da
te,
sum(num) AS num_r_collectable,su
m(amt) AS amt_r_collectable,su
m(fbal) AS fbal_r_collectable, sum(bal) AS bal_r_collectable,su
m(fees) AS fees_r_collectable,
sum(num_open) AS num_r_collectable_op
en,sum(amt_open) AS amt_r_collectable_op
en,sum(fbal_open) AS fbal_r_collectable_o
pen, sum(bal) AS bal_r_collectable_op
en,sum(fees_open) AS fees_r_collectable_o
pen,
sum(num_closed) AS num_r_collectable_cl
osed,sum(amt_closed)
AS amt_r_collectable_cl
osed,sum(fbal_closed
) AS fbal_r_collectable_c
losed, sum(bal) AS bal_r_collectable_cl
osed,sum(fees_closed
) AS fees_r_collectable_c
losed
FROM pws_stats_returns, pws_return_codes rc WHERE batch_id = ? AND case_upload_date IS NULL AND year IS NOT NULL
AND return_code = rc.id AND rc.nature = 'F' AND return_code != 20 GROUP BY csid,case_upload_dat
e
) total_rcb USING (csid,case_upload_da
te)
FULL JOIN (
SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_da
te,
sum(num) AS num_r_requested,sum(
amt) AS amt_r_requested,sum(
fbal) AS fbal_r_requested, sum(bal) AS bal_r_requested,sum(
fees) AS fees_r_requested,
sum(num_open) AS num_r_requested_open
,sum(amt_open) AS amt_r_requested_open
,sum(fbal_open) AS fbal_r_requested_ope
n, sum(bal) AS bal_r_requested_open
,sum(fees_open) AS fees_r_requested_ope
n,
sum(num_closed) AS num_r_requested_clos
ed,sum(amt_closed) AS amt_r_requested_clos
ed,sum(fbal_closed) AS fbal_r_requested_clo
sed, sum(bal) AS bal_r_requested_clos
ed,sum(fees_closed) AS fees_r_requested_clo
sed
FROM pws_stats_returns, pws_return_codes rc WHERE batch_id = ? AND case_upload_date IS NULL AND year IS NOT NULL
AND return_code = rc.id AND rc.req_by_client = true AND return_code != 20 GROUP BY csid,case_upload_dat
e
) total_rrb USING (csid,case_upload_da
te)
-- Join account age bands
FULL JOIN (
SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_da
te,
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_da
te)
FULL JOIN (
SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_da
te,
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_da
te)
FULL JOIN (
SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_da
te,
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_da
te)
FULL JOIN (
SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_da
te,
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_da
te)
-- Join summary totals
FULL JOIN (
SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_da
te,
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_da
te)
FULL JOIN (
SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_da
te,
num AS total_num_closed,amt
AS total_amt_closed,fba
l AS total_fbal_closed,ba
l AS total_bal_closed,fee
s AS total_fees_closed,(a
mt-bal) AS total_collected_clos
ed
FROM tmp_stats_zcb
) zcb USING (csid,case_upload_da
te)
FULL JOIN (
SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_da
te,
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_da
te)
-- Join agent totals
FULL JOIN (
SELECT csid,NULL::integer AS year,NULL::integer AS month,case_upload_da
te,
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_da
te)


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_remit
ted | integer |
bal_successful_remit
ted | numeric(12,2) |
amt_successful_remit
ted | numeric(12,2) |
fees_successful_remi
tted | 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_clos
ed | 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_clo
sed | integer |
bal_returned_err_clo
sed | numeric(12,2) |
fbal_returned_err_cl
osed | numeric(12,2) |
amt_returned_err_clo
sed | numeric(12,2) |
fees_returned_err_cl
osed | 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_cl
osed | integer |
amt_r_collectable_cl
osed | numeric(12,2) |
bal_r_collectable_cl
osed | numeric(12,2) |
fbal_r_collectable_c
losed | numeric(12,2) |
fees_r_collectable_c
losed | numeric(12,2) |
num_r_requested_clos
ed | integer |
amt_r_requested_clos
ed | numeric(12,2) |
bal_r_requested_clos
ed | numeric(12,2) |
fbal_r_requested_clo
sed | numeric(12,2) |
fees_r_requested_clo
sed | numeric(12,2) |
num_returned_err_ope
n | integer |
bal_returned_err_ope
n | numeric(12,2) |
fbal_returned_err_op
en | numeric(12,2) |
amt_returned_err_ope
n | numeric(12,2) |
fees_returned_err_op
en | 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_op
en | integer |
amt_r_collectable_op
en | numeric(12,2) |
bal_r_collectable_op
en | numeric(12,2) |
fbal_r_collectable_o
pen | numeric(12,2) |
fees_r_collectable_o
pen | 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_ope
n | numeric(12,2) |
fees_r_requested_ope
n | 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_successfu
l | integer |
amt_closed_successfu
l | numeric(12,2) |
bal_closed_successfu
l | numeric(12,2) |
fbal_closed_successf
ul | numeric(12,2) |
fees_closed_successf
ul | 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(i
d)



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_crosst
ab_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_crosst
ab_amt_open
Table "public. tmp_stats_dsm_crosst
ab_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


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Matt Carter

2005-11-18, 9:24 am

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 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 networ
k, 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 fro
m 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.
********************
********************
*******

Sponsored Links





Also available: Server administration forum archive | Web Design forum archive | Software forum archive | Hardware reviews archive | Programming forum archive

Copyright 2008 droptable.com