Performance problem from migrating between versions! - Mailing list pgsql-performance

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)