(Resending)
Hi
I have a nightly process which distills a range of statistics from a
third-party database into a set of temporary tables, and then from those
tables, aggregates and joins these figures into two main tables. Each
temporary table contains a moderate number of rows and few columns.
The main query is a large series of FULL JOINs and has worked perfectly
for a long time.
Yesterday I added 20 more join statements to the query (identical to
existing statements) and now the main query which would run in <30
seconds, runs indefinitely with the process stuck at "PARSE" in version
7.4, or "BIND" in version 8.0.3. The process is using all available CPU
but not a great deal of memory.
I've let this process run for over 50 minutes before it is killed by me
with signal ABRT. (Terminating the client connection does not stop the
process.)
If I remove these additional joins the statement perfectly again in <30
seconds.
I have upgraded Postgresql from version 7.4 to 8.0.3 but this does not
fix the behaviour.
The additional tables being full joined are generated with the same java
function as the other tables and the only variable being the names of
the non-key columns (different temporary tables for different columns of
data in the resulting table).
The data in the source tables of the additional statements I am adding
has an identical size and layout to the source tables from the existing
FULL JOIN statements.
The only difference with these new tables is that not all of the joined
columns are used in the select clause of the main query, some are
ignored, whereas in previous tables all columns available are used.
Here is the main query:
INSERT INTO pws_stats_case (
--- Destination columns
batch_id,csid,year,month,case_upload_date,
num_live,amt_live,fbal_live,bal_live,fees_live,
num_arrange,amt_arrange,fbal_arrange,bal_arrange,fees_arrange,
num_fullypaid,amt_fullypaid,fbal_fullypaid,bal_fullypaid,fees_fullypaid,
num_successful,amt_successful,fbal_successful,bal_successful,fees_successful,
num_trace,amt_trace,fbal_trace,bal_trace,fees_trace,
num_expired,amt_expired,fbal_expired,bal_expired,fees_expired,
num_our_hold,amt_our_hold,fbal_our_hold,bal_our_hold,fees_our_hold,
num_client_hold,amt_client_hold,fbal_client_hold,bal_client_hold,fees_client_hold,
num_hold,amt_hold,fbal_hold,bal_hold,fees_hold,
num_returned,amt_returned,fbal_returned,bal_returned,fees_returned,
num_returned_err,amt_returned_err,fbal_returned_err,bal_returned_err,fees_returned_err,
num_30days,amt_30days,fbal_30days,bal_30days,fees_30days,
num_60days,amt_60days,fbal_60days,bal_60days,fees_60days,
num_90days,amt_90days,fbal_90days,bal_90days,fees_90days,
num_365days,amt_365days,fbal_365days,bal_365days,fees_365days,
total_num_open,total_amt_open,total_fbal_open,total_bal_open,total_fees_open,total_collected_open,
total_num_closed,total_amt_closed,total_fbal_closed,total_bal_closed,total_fees_closed,total_collected_closed,
total_num,total_amt,total_fbal,total_bal,total_fees,total_collected,
num_r_uncollectable,amt_r_uncollectable,fbal_r_uncollectable,bal_r_uncollectable,fees_r_uncollectable,
num_r_collectable,amt_r_collectable,fbal_r_collectable,bal_r_collectable,fees_r_collectable,
num_r_requested,amt_r_requested,fbal_r_requested,bal_r_requested,fees_r_requested,
num_allocated,amt_allocated,fbal_allocated,bal_allocated,fees_allocated,
num_returned_open,amt_returned_open,fbal_returned_open,bal_returned_open,fees_returned_open,
num_returned_err_open,bal_returned_err_open,fbal_returned_err_open,amt_returned_err_open,fees_returned_err_open,
num_r_uncollectable_open,amt_r_uncollectable_open,bal_r_uncollectable_open,fbal_r_uncollectable_open,fees_r_uncollectable_open,
num_r_collectable_open,amt_r_collectable_open,bal_r_collectable_open,fbal_r_collectable_open,fees_r_collectable_open,
num_r_requested_open,amt_r_requested_open,bal_r_requested_open,fbal_r_requested_open,fees_r_requested_open,
num_returned_closed,amt_returned_closed,fbal_returned_closed,bal_returned_closed,fees_returned_closed,
num_returned_err_closed,bal_returned_err_closed,fbal_returned_err_closed,amt_returned_err_closed,fees_returned_err_closed,
num_r_uncollectable_closed,amt_r_uncollectable_closed,bal_r_uncollectable_closed,fbal_r_uncollectable_closed,fees_r_uncollectable_closed,
num_r_collectable_closed,amt_r_collectable_closed,bal_r_collectable_closed,fbal_r_collectable_closed,fees_r_collectable_closed,
num_r_requested_closed,amt_r_requested_closed,bal_r_requested_closed,fbal_r_requested_closed,fees_r_requested_closed,
num_open_expired,amt_open_expired,fbal_open_expired,bal_open_expired,fees_open_expired,
num_closed_expired,amt_closed_expired,fbal_closed_expired,bal_closed_expired,fees_closed_expired,
num_open_successful,amt_open_successful,fbal_open_successful,bal_open_successful,fees_open_successful,
num_closed_successful,amt_closed_successful,fbal_closed_successful,b
