Pessoal,
Estava com alguns problemas de lock e decidi atualizar minha versão do
PostgreSQL da 8.2 para a 8.4, onde muitos avanços foram feitos nesse campo.
Contudo, para minha surpresa, a performance caiu drasticamente. O curioso é
que as máquinas físicas são idênticas fisicamente e mantive exatamente os
mesmos parâmetros de configuração para o PostgreSQL. Para se ter uma ideia
da queda, vou mostrar a saída do EXPLAIN ANALYZE para a mesma consulta nos
diferentes bancos.
*Consulta no PostgreSQL 8.2*
Sort (cost=1029.63..1029.71 rows=33 width=42) (actual
time=3895.353..3895.361 rows=67 loops=1)
Sort Key:
acs_object__name(apm_package__parent_id(public.forums_forums.package_id)),
public.forums_forums.name
-> Nested Loop (cost=39.87..1028.80 rows=33 width=42) (actual
time=743.868..3832.291 rows=67 loops=1)
-> Bitmap Heap Scan on forums_forums (cost=39.87..464.01 rows=33
width=42) (actual time=260.795..1754.423 rows=67 loops=1)
Recheck Cond: ((package_id = ANY
('{0,840191,1486834,626929,520062,1101742,1160464,1161067,2750196,3500360,133998,3673774,3676596,368
6932,4860207,5986896,10050612,10157702,4645,93855,3186091,601355,22297512,6552691,21650654,8265465,23731964,33752302,15316177}'::integer[]))
AND (en
abled_p = 't'::bpchar))
Filter: (subplan)
-> Bitmap Index Scan on forums_forums_pkg_enable_idx
(cost=0.00..39.86 rows=66 width=0) (actual time=56.270..56.270 rows=191
loops=
1)
Index Cond: ((package_id = ANY
('{0,840191,1486834,626929,520062,1101742,1160464,1161067,2750196,3500360,133998,3673774,3676596
,3686932,4860207,5986896,10050612,10157702,4645,93855,3186091,601355,22297512,6552691,21650654,8265465,23731964,33752302,15316177}'::integer[]))
AND
(enabled_p = 't'::bpchar))
SubPlan
-> Nested Loop (cost=0.00..21724.67 rows=4088 width=0)
(actual time=25.336..25.336 rows=1 loops=67)
-> Nested Loop (cost=0.00..14348.63 rows=9292
width=4) (actual time=16.059..17.942 rows=12 loops=67)
Join Filter: ((pdm.privilege)::text =
(p.privilege)::text)
-> Seq Scan on acs_privilege_descendant_map
pdm (cost=0.00..9.23 rows=4 width=16) (actual time=0.103..0.103 rows=1 lo
ops=67)
Filter: ((descendant)::text =
'read_private_data'::text)
-> Nested Loop (cost=0.00..3038.60
rows=43700 width=17) (actual time=15.022..17.830 rows=16 loops=67)
-> Index Scan using
acs_obj_ctx_idx_object_id_idx on acs_object_context_index c
(cost=0.00..17.37 rows=48 width
=4) (actual time=10.316..11.088 rows=3 loops=67)
Index Cond: (object_id = $0)
-> Index Scan using
acs_permissions_object_id_idx on acs_permissions p (cost=0.00..62.32
rows=50 width=21) (actual time=1.726..1.995 rows=5 loops=226)
Index Cond: (c.ancestor_id =
p.object_id)
-> Index Scan using party_approved_member_map_pk on
party_approved_member_map pamm (cost=0.00..0.78 rows=1 width=4) (actual
time=0.637..0.637 rows=0 loops=776)
Index Cond: ((pamm.party_id = p.grantee_id)
AND (pamm.member_id = 3443))
-> Index Scan using acs_objects_pk on acs_objects
(cost=0.00..8.82 rows=1 width=4) (actual time=16.205..16.207 rows=1
loops=67)
Index Cond: (acs_objects.object_id =
public.forums_forums.forum_id)
SubPlan
-> Index Scan using site_nodes_object_id_idx on site_nodes
(cost=0.00..8.27 rows=1 width=4) (actual time=0.581..0.582 rows=1 loops=67)
Index Cond: (object_id = $0)
Total runtime: *3895.939* ms
(27 registros)
*Consulta no PostgreSQL 8.4*
*
*
Sort (cost=3616469.97..3616470.15 rows=72 width=39) (actual
time=95704.475..95704.486 rows=67 loops=1)
Sort Key:
(acs_object__name(apm_package__parent_id(public.forums_forums.package_id))),
public.forums_forums.name
Sort Method: quicksort Memory: 35kB
-> Nested Loop Semi Join (cost=0.00..3616467.75 rows=72 width=39)
(actual time=196.064..95703.983 rows=67 loops=1)
Join Filter: (public.forums_forums.package_id = c.object_id)
-> Nested Loop (cost=0.00..663.18 rows=72 width=39) (actual
time=0.042..2.175 rows=67 loops=1)
-> Seq Scan on forums_forums (cost=0.00..31.16 rows=72
width=39) (actual time=0.017..0.899 rows=67 loops=1)
Filter: ((enabled_p = 't'::bpchar) AND (package_id =
ANY
('{0,840191,1486834,626929,520062,1101742,1160464,1161067,2750196,3500360,133998,3673774,3676596,3686932,4860207,5986896,10050612,10157702,4645,93855,3186091,601355,22297512,6552691,21650654,8265465,23731964,33752302,15316177}'::integer[])))
-> Index Scan using acs_objects_pk on acs_objects
(cost=0.00..8.77 rows=1 width=4) (actual time=0.014..0.016 rows=1 loops=67)
Index Cond: (acs_objects.object_id =
public.forums_forums.forum_id)
-> Nested Loop (cost=0.00..982907.73 rows=397639583 width=4)
(actual time=0.102..1117.157 rows=1396734 loops=67)
-> Nested Loop (cost=0.00..12218.50 rows=45973 width=4)
(actual time=0.073..0.487 rows=92 loops=67)
Join Filter: ((p.privilege)::text =
(pdm.privilege)::text)
-> Seq Scan on acs_privilege_descendant_map pdm
(cost=0.00..9.23 rows=3 width=13) (actual time=0.025..0.025 rows=1
loops=67)
Filter: ((descendant)::text =
'read_private_data'::text)
-> Nested Loop (cost=0.00..549.81 rows=281596
width=14) (actual time=0.037..0.364 rows=231 loops=67)
-> Index Scan using party_member_member_idx on
party_approved_member_map pamm (cost=0.00..25.68 rows=18 width=4) (actual
time=0.018..0.018 rows=1 loops=67)
Index Cond: (member_id = 3443)
-> Index Scan using acs_permissions_grantee_idx
on acs_permissions p (cost=0.00..28.92 rows=16 width=18) (actual
time=0.015..0.255 rows=231 loops=67)
Index Cond: (p.grantee_id = pamm.party_id)
-> Index Scan using acs_obj_ctx_idx_ancestor_idx on
acs_object_context_index c (cost=0.00..13.14 rows=638 width=8) (actual
time=0.008..6.993 rows=15182 loops=6164)
Index Cond: (c.ancestor_id = p.object_id)
SubPlan 1
-> Index Scan using site_nodes_object_id_idx on site_nodes
(cost=0.00..8.52 rows=1 width=4) (actual time=0.266..0.268 rows=1 loops=67)
Index Cond: (object_id = $0)
Total runtime: *95704.632* ms
(26 registros)
As diferenças entre as decisões tomadas pelo otimizador são tão absurdas
que não sei nem por onde começar.
Será que alguém pode me dar uma luz?
--
Eduardo Santos
Analista de Sistemas
http://eduardosan.wordpress.com
http://twitter.com/eduardosan
_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
