Hi,
I have the following problem. A week ago we've migrated from PGv7.2.3 to 7.4.6. There were a lot of things in the apps to chenge but we made them. But one query doesn't want to run. In the old PGv7.2.3 it passes for 10 min. In the new one it gaves:
DBD::Pg::st execute failed: ERROR: out of memory
So the Server was not upgrated or preconfigured, so I suppose that the problem is somewhere in the configuration of the Postgres. Here I post the query and the explain. I can't post the explain analyze, because:))... the query can't execute:)
I also post the result of SHOW ALL to give a view of the server configuration.
Thanks in advance for all sugestions.
Kaloyan Iliev
SHOW ALL
name setting add_missing_from on australian_timezones off authentication_timeout 60 check_function_bodies on checkpoint_segments 16 checkpoint_timeout 300 checkpoint_warning 30 client_encoding SQL_ASCII client_min_messages notice commit_delay 0 commit_siblings 5 cpu_index_tuple_cost 0.001 cpu_operator_cost 0.0025 cpu_tuple_cost 0.01 DateStyle ISO, DMY db_user_namespace off deadlock_timeout 1000 debug_pretty_print off debug_print_parse off debug_print_plan off debug_print_rewritten off default_statistics_target 10 default_transaction_isolation read committed default_transaction_read_only off dynamic_library_path $libdir effective_cache_size 13000 enable_hashagg on enable_hashjoin on enable_indexscan on enable_mergejoin on enable_nestloop on enable_seqscan on enable_sort on enable_tidscan on explain_pretty_print on extra_float_digits 0 from_collapse_limit 8 fsync on geqo on geqo_effort 1 geqo_generations 0 geqo_pool_size 0 geqo_selection_bias 2 geqo_threshold 11 join_collapse_limit 8 krb_server_keyfile unset lc_collate C lc_ctype CP1251 lc_messages C lc_monetary C lc_numeric C lc_time C log_connections off log_duration off log_error_verbosity default log_executor_stats off log_hostname off log_min_duration_statement -1 log_min_error_statement panic log_min_messages notice log_parser_stats off log_pid off log_planner_stats off log_source_port off log_statement off log_statement_stats off log_timestamp on max_connections 256 max_expr_depth 10000 max_files_per_process 1000 max_fsm_pages 20000 max_fsm_relations 1000 max_locks_per_transaction 64 password_encryption on port 5432 pre_auth_delay 0 preload_libraries unset random_page_cost 4 regex_flavor advanced rendezvous_name unset search_path $user,public server_encoding SQL_ASCII server_version 7.4.6 shared_buffers 1000 silent_mode off sort_mem 1024 sql_inheritance off ssl off statement_timeout 0 stats_block_level on stats_command_string on stats_reset_on_server_start off stats_row_level on stats_start_collector on superuser_reserved_connections 2 syslog 0 syslog_facility LOCAL0 syslog_ident postgres tcpip_socket on TimeZone unknown trace_notify off transaction_isolation read committed transaction_read_only off transform_null_equals off unix_socket_directory unset unix_socket_group unset unix_socket_permissions 511 vacuum_mem 8192 virtual_host unset wal_buffers 8 wal_debug 0 wal_sync_method fsync zero_damaged_pages off
(113 rows)
And now the query:
explain select UNPAID.ino,
I.idate,
round(UNPAID.saldo - ( select round(coalesce(sum(total),0),5)
from invoices I1 where I1.iino = I.ino AND
I1.istatus = 0 AND
I1.itype = 2 )
,2) AS saldo,
C.name AS client_name,
SC.branch AS client_branch,
I.total,
I.nomenclature_no AS nom,
I.subnom_no AS subnom,
OF.description AS office, coalesce((select 1.2 * sum(AD.bgl_amount)::float / AC.amount
from acc_clients AC, config C,
acc_debts AD, debts_desc D
where C.office = OF.officeid AND
not AC.credit AND
AC.ino = I.ino AND
AC.transact_no = AD.transact_no AND
AD.credit AND
AD.debtid = D.debtid AND
C.confid = D.refid AND
C.oid = (select max(oid) from config where confid=D.refid )
group by AC.amount ),0) AS perc, 1
from invoices I,
offices OF,
(
select nomenclature_no, subnom_no, ino, sum(saldo) as saldo
from
(
select nomenclature_no, subnom_no, ino, round(sum(saldo_sign(not credit)*amount),5) AS saldo
from acc_clients
group by ino, nomenclature_no, subnom_no
UNION ALL
select c.nomenclature_no, c.subnom_no, c.ino, round(COALESCE(sum(p.bgl_amount), 0),5) AS saldo
from acc_clients c, acc_payments p where c.transact_no = p.transact_no AND p.fisc_status = 4
group by c.ino, c.nomenclature_no, c.subnom_no
) TTUNPAID
group by ino, nomenclature_no, subnom_no
) UNPAID,
clients C,
subnom SC
where I.idate >= '01-01-2004' AND I.idate <= '01-01-2005' AND UNPAID.ino = I.ino AND
I.istatus = 0 AND
I.itype <> 2 AND
I.nomenclature_no = C.nomenclature_no AND
I.nomenclature_no = SC.nomenclature_no AND
I.subnom_no = SC.subnom_no
union all
select UNPAID.ino,
I.idate,
round(UNPAID.saldo - ( select round(coalesce(sum(total),0),5)
from invoices I1 where I1.iino = I.ino AND
I1.istatus = 0 AND
I1.itype = 2 )
,2) AS saldo,
C.name AS client_name,
SC.branch AS client_branch,
I.total,
I.nomenclature_no AS nom,
I.subnom_no AS subnom,
'Неизвестен' AS office,
coalesce((select 1.2 * sum(AD.bgl_amount)::float / AC.amount
from acc_clients AC, acc_debts AD, debts_desc D
where not AC.credit AND
AC.ino = I.ino AND
AC.transact_no = AD.transact_no AND
AD.credit AND
AD.debtid = D.debtid AND
D.refid is null
group by AC.amount ),0) AS perc, 1
from invoices I,
( select nomenclature_no, subnom_no, ino, round(sum(saldo_sign(not credit)*amount),5) AS saldo
from acc_clients
group by ino, nomenclature_no, subnom_no ) UNPAID,
clients C,
subnom SC
where I.idate >= '01-01-2004' AND I.idate <= '01-01-2005' AND UNPAID.ino = I.ino AND
I.istatus = 0 AND
I.itype <> 2 AND
I.nomenclature_no = C.nomenclature_no AND
I.nomenclature_no = SC.nomenclature_no AND
exists (select 1
from acc_clients AC,
acc_debts AD,
debts_desc DD
where AC.ino = I.ino AND
AD.transact_no = AC.transact_no AND
AD.debtid = DD.debtid AND
DD.refid is null ) AND I.subnom_no = SC.subnom_no order by office, ino DESC
QUERY PLAN
Sort (cost=453579405.72..453585516.16 rows=2444177 width=108)
Sort Key: office, ino
-> Append (cost=93725.37..452807307.33 rows=2444177 width=108)
-> Subquery Scan "*SELECT* 1" (cost=93725.37..447433349.67 rows=2418773 width=108)
-> Nested Loop (cost=93725.37..447409161.94 rows=2418773 width=108)
-> Merge Join (cost=93723.86..101789.54 rows=50867 width=94)
Merge Cond: ("outer".ino = "inner".ino)
-> Subquery Scan unpaid (cost=82961.98..89647.68 rows=267428 width=36)
-> GroupAggregate (cost=82961.98..86973.40 rows=267428 width=44)
-> Sort (cost=82961.98..83630.55 rows=267428 width=44)
Sort Key: ino, nomenclature_no, subnom_no
-> Subquery Scan ttunpaid (cost=35143.93..49845.48 rows=267428 width=44)
-> Append (cost=35143.93..47171.20 rows=267428 width=21)
-> Subquery Scan "*SELECT* 1" (cost=35143.93..44492.88 rows=267113 width=21)
-> GroupAggregate (cost=35143.93..41821.75 rows=267113 width=21)
-> Sort (cost=35143.93..35811.71 rows=267113 width=21)
Sort Key: ino, nomenclature_no, subnom_no
-> Seq Scan on acc_clients (cost=0.00..4758.13 rows=267113 width=21)
-> Subquery Scan "*SELECT* 2" (cost=2672.80..2678.32 rows=315 width=20)
-> HashAggregate (cost=2672.80..2675.17 rows=315 width=20)
-> Nested Loop (cost=0.00..2669.65 rows=315 width=20)
-> Index Scan using acc_payments_fisc_status_idx on acc_payments p (cost=0.00..892.52 rows=315 width=12)
Index Cond: (fisc_status = 4)
-> Index Scan using acc_clients_transact_no_uidx on acc_clients c (cost=0.00..5.63 rows=1 width=16)
Index Cond: (c.transact_no = "outer".transact_no)
-> Sort (cost=10761.89..10817.21 rows=22128 width=58)
Sort Key: i.ino
-> Hash Join (cost=1774.86..8710.88 rows=22128 width=58)
Hash Cond: (("outer".nomenclature_no = "inner".nomenclature_no) AND ("outer".subnom_no = "inner".subnom_no))
-> Seq Scan on invoices i (cost=0.00..5556.52 rows=22292 width=24)
Filter: ((idate >= '2004-01-01'::date) AND (idate <= '2005-01-01'::date) AND (istatus = 0) AND (itype <> 2))
-> Hash (cost=1592.90..1592.90 rows=13193 width=46)
-> Hash Join (cost=577.25..1592.90 rows=13193 width=46)
Hash Cond: ("outer".nomenclature_no = "inner".nomenclature_no)
-> Seq Scan on subnom sc (cost=0.00..393.93 rows=13193 width=19)
-> Hash (cost=463.20..463.20 rows=12820 width=27)
-> Seq Scan on clients c (cost=0.00..463.20 rows=12820 width=27)
-> Materialize (cost=1.51..2.02 rows=51 width=14)
-> Seq Scan on offices "of" (cost=0.00..1.51 rows=51 width=14)
SubPlan
-> HashAggregate (cost=179.30..179.31 rows=1 width=19)
-> Nested Loop (cost=0.00..179.30 rows=1 width=19)
Join Filter: ("inner".oid = (subplan))
-> Nested Loop (cost=0.00..77.57 rows=2 width=23)
-> Nested Loop (cost=0.00..66.58 rows=2 width=23)
-> Index Scan using acc_clients_ino on acc_clients ac (cost=0.00..25.47 rows=4 width=12)
Index Cond: (ino = $0)
Filter: (NOT credit)
-> Index Scan using acc_debts_transact_no_idx on acc_debts ad (cost=0.00..9.71 rows=45 width=19)
Index Cond: ("outer".transact_no = ad.transact_no)
Filter: credit
-> Index Scan using debts_desc_pkey on debts_desc d (cost=0.00..5.48 rows=1 width=8)
Index Cond: ("outer".debtid = d.debtid)
-> Index Scan using config_confid_idx on config c (cost=0.00..25.42 rows=1 width=8)
Index Cond: (c.confid = "outer".refid)
Filter: (office = $2)
SubPlan
-> Aggregate (cost=25.43..25.43 rows=1 width=4)
-> Index Scan using config_confid_idx on config (cost=0.00..25.40 rows=9 width=4)
Index Cond: (confid = $1)
-> Aggregate (cost=5.59..5.59 rows=1 width=8)
-> Index Scan using invoices_iino_idx on invoices i1 (cost=0.00..5.58 rows=1 width=8)
Index Cond: (iino = $0)
Filter: ((istatus = 0) AND (itype = 2))
-> Subquery Scan "*SELECT* 2" (cost=3250111.65..5373957.66 rows=25404 width=94)
-> Merge Join (cost=3250111.65..5373703.62 rows=25404 width=94)
Merge Cond: ("outer".ino = "inner".ino)
-> Subquery Scan unpaid (cost=35143.93..44492.88 rows=267113 width=36)
-> GroupAggregate (cost=35143.93..41821.75 rows=267113 width=21)
-> Sort (cost=35143.93..35811.71 rows=267113 width=21)
Sort Key: ino, nomenclature_no, subnom_no
-> Seq Scan on acc_clients (cost=0.00..4758.13 rows=267113 width=21)
-> Sort (cost=3214967.73..3214995.39 rows=11064 width=58)
Sort Key: i.ino
-> Hash Join (cost=3212283.98..3214224.58 rows=11064 width=58)
Hash Cond: ("outer".nomenclature_no = "inner".nomenclature_no)
-> Merge Join (cost=3211706.73..3213082.65 rows=11867 width=39)
Merge Cond: ("outer".nomenclature_no = "inner".nomenclature_no)
Join Filter: ("inner".subnom_no = "outer".subnom_no)
-> Index Scan using subnom_nom_idx on subnom sc (cost=0.00..1135.01 rows=13193 width=19)
-> Sort (cost=3211706.73..3211734.59 rows=11146 width=24)
Sort Key: i.nomenclature_no
-> Index Scan using invoices_idate_idx on invoices i (cost=0.00..3210957.48 rows=11146 width=24)
Index Cond: ((idate >= '2004-01-01'::date) AND (idate <= '2005-01-01'::date))
Filter: ((istatus = 0) AND (itype <> 2) AND (subplan))
SubPlan
-> Nested Loop (cost=0.00..140.00 rows=1 width=0)
-> Nested Loop (cost=0.00..101.54 rows=7 width=4)
-> Index Scan using acc_clients_ino on acc_clients ac (cost=0.00..25.47 rows=7 width=4)
Index Cond: (ino = $0)
-> Index Scan using acc_debts_transact_no_idx on acc_debts ad (cost=0.00..9.71 rows=93 width=8)
Index Cond: (ad.transact_no = "outer".transact_no)
-> Index Scan using debts_desc_pkey on debts_desc dd (cost=0.00..5.48 rows=1 width=4)
Index Cond: ("outer".debtid = dd.debtid)
Filter: (refid IS NULL)
-> Hash (cost=463.20..463.20 rows=12820 width=27)
-> Seq Scan on clients c (cost=0.00..463.20 rows=12820 width=27)
SubPlan
-> HashAggregate (cost=77.58..77.59 rows=1 width=19)
-> Nested Loop (cost=0.00..77.57 rows=1 width=19)
-> Nested Loop (cost=0.00..66.58 rows=2 width=23)
-> Index Scan using acc_clients_ino on acc_clients ac (cost=0.00..25.47 rows=4 width=12)
Index Cond: (ino = $0)
Filter: (NOT credit)
-> Index Scan using acc_debts_transact_no_idx on acc_debts ad (cost=0.00..9.71 rows=45 width=19)
Index Cond: ("outer".transact_no = ad.transact_no)
Filter: credit
-> Index Scan using debts_desc_pkey on debts_desc d (cost=0.00..5.48 rows=1 width=4)
Index Cond: ("outer".debtid = d.debtid)
Filter: (refid IS NULL)
-> Aggregate (cost=5.59..5.59 rows=1 width=8)
-> Index Scan using invoices_iino_idx on invoices i1 (cost=0.00..5.58 rows=1 width=8)
Index Cond: (iino = $0)
Filter: ((istatus = 0) AND (itype = 2))
(114 rows)
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
