Huge query stalls at PARSE/BIND stage (2) - Mailing list pgsql-bugs
| From | Matt Carter |
|---|---|
| Subject | Huge query stalls at PARSE/BIND stage (2) |
| Date | |
| Msg-id | 1132216020.1877.11.camel@localhost Whole thread Raw |
| List | pgsql-bugs |
... continued ...
So that is the query.
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
***********************************************
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.
***********************************************
pgsql-bugs by date: