Huge query stalls at PARSE/BIND stage (2) - Mailing list pgsql-bugs
| From | Matt |
|---|---|
| Subject | Huge query stalls at PARSE/BIND stage (2) |
| Date | |
| Msg-id | 1132216392.1877.17.camel@localhost Whole thread Raw |
| Responses |
Re: Huge query stalls at PARSE/BIND stage (2)
|
| 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
pgsql-bugs by date: