[heise News]: Zum Testen frei: Erste Beta von PostgreSQL 8.4

Zum Testen frei: Erste Beta von PostgreSQL 8.4

Weiterlesen…

Diese News wurde aus dem Heise RSS-Feed importiert.

Gibts Erfahrungen? Wie “Beta” ists denn?

Funktioniert gut, kann ich nur sagen.
Ein paar Kleinigkeiten, die mir während der Entwicklung aufgefallen sind, wurden schon beseitigt.

Größere Tests kommen demnächst.

im Vgl zu 8.3

viele Queries sind sehr sehr langsam …

ich hatte gehofft, dass ich mit den defaults von Pg 84 wenigstens so performant arbeiten koennte wie mit 83

ist dir da was aufgefallen?


Installation war Problemlos … auch Restore einer 83 DB ging ohne Probleme

Da ist mir bisher nichts aufgefallen.

ich bring gleich mal ne recht komplexe query … (dauer noch a weng) … und den plan von 83 und 84 …

vacuum analyse hab ich auf der 84 laufen lassen - bringt nix …

die query wird von nem generator erzeugt - ich hoffe die table qualifier stoeren net so

Die Query - etwas gekürzt - Plans folgen

select
	T28561893.id_obj As Id,
	T28561893.id_rec,
	T28561893.rec_class,
	T10041136.id_rec As parent_rec,
	COUNT((select 1 where (EXISTS ((select  T.id_obj As Id from archiv9."BS_OBJECTS_SUP" As T
		where T.id_parent = T28561893.id_obj)) OR (true = false AND EXISTS ((select
			T.id_link As Id from archiv9."BS_LINKS" As T where T.id_obj = T28561893.id_obj)))))) As count_chld,
	......
	COUNT(T16142180.id_obj) As count_chld2
from archiv9."BS_OBJECTS_SUP" As T28561893
	left outer join archiv9."BS_OBJECTS_SUP" As T10041136 on T10041136.id_obj = T28561893.id_parent
	inner join archiv9."BS_TREE" As T14051109 on T28561893.id_obj = T14051109.id_parent
	inner join archiv9."BS_OBJECTS_SUP" As T910837 on T910837.id_obj = T14051109.id_obj
	left outer join archiv9."BS_OBJECTS_SUP" As T16142180 on T28561893.id_obj = T16142180.id_parent

where (T28561893.id_parent = 2059187) and (T14051109.id_obj = T14051109.id_parent)
group by T28561893.id_obj, T28561893.id_rec, T28561893.rec_class, T10041136.id_rec, T28561893.title, T28561893.signum, T28561893.sec_level, T28561893.signature, T28561893.hd_from::text, T28561893.hd_to::text, T28561893.hd_info, T10041136.id_rec, .............
order by T28561893.title asc, T28561893.rec_class asc
LIMIT 10000

Plan 8.3 - gekürzt - dauer ca 1.5 sec
ich habe nur das 1. drittel des Plans - aber man sieht gleich deutlich den Unterschied …

"Limit  (cost=6914.91..7032.47 rows=1 width=218) (actual time=756.690..1520.019 rows=44 loops=1)"
"  ->  GroupAggregate  (cost=6914.91..7032.47 rows=1 width=218) (actual time=756.688..1519.977 rows=44 loops=1)"
"        ->  Sort  (cost=6914.91..6914.91 rows=1 width=218) (actual time=755.154..758.807 rows=6277 loops=1)"
"              Sort Key: o.title, o.rec_class, o.id_obj, o.id_rec, o.id_rec, o.signum, o.sec_level, o.signature, ((o.hd_from)::text), ((o.hd_to)::text), o.hd_info, o.id_home"
"              Sort Method:  quicksort  Memory: 965kB"
"              ->  Nested Loop Left Join  (cost=0.00..6914.90 rows=1 width=218) (actual time=0.830..708.605 rows=6277 loops=1)"
"                    ->  Nested Loop Left Join  (cost=0.00..2704.38 rows=1 width=214) (actual time=0.621..16.476 rows=44 loops=1)"
"                          ->  Nested Loop  (cost=0.00..2642.19 rows=1 width=214) (actual time=0.438..11.073 rows=44 loops=1)"
"                                ->  Nested Loop  (cost=0.00..2579.80 rows=1 width=222) (actual time=0.252..5.782 rows=44 loops=1)"
"                                      ->  Index Scan using idx_objects_signum on "BS_OBJECTS" o  (cost=0.00..2322.81 rows=20 width=214) (actual time=0.237..5.114 rows=44 loops=1)"
"                                            Index Cond: (id_parent = 2059187)"
"                                            Filter: ((sec_level <= 4) AND (subplan))"
"                                            SubPlan"
"                                              ->  Nested Loop  (cost=0.00..54.10 rows=1 width=0) (actual time=0.109..0.109 rows=1 loops=44)"
"                                                    ->  Nested Loop  (cost=0.00..46.47 rows=1 width=4) (actual time=0.096..0.096 rows=1 loops=44)"
"                                                          ->  Nested Loop  (cost=0.00..41.88 rows=1 width=4) (actual time=0.085..0.085 rows=1 loops=44)"
"                                                                Join Filter: (lo.id_lock = l.id_lock)"
"                                                                ->  Index Scan using fki_ref_objects on "BS_LOCKOBJ" lo  (cost=0.00..39.43 rows=1 width=4) (actual time=0.055..0.055 rows=1 loops=44)"
"                                                                      Index Cond: (id_obj = $2)"
"                                                                      Filter: (subplan)"
"                                                                      SubPlan"
"                                                                        ->  Nested Loop  (cost=1.23..31.13 rows=1 width=0) (actual time=0.045..0.045 rows=1 loops=44)"
"                                                                              ->  Nested Loop  (cost=1.23..23.50 rows=1 width=4) (actual time=0.032..0.032 rows=1 loops=44)"
"                                                                                    ->  Hash Join  (cost=1.23..2.73 rows=3 width=4) (actual time=0.020..0.020 rows=1 loops=44)"
"                                                                                          Hash Cond: (le.id_exc = e.id_exc)"
"                                                                                          ->  Seq Scan on "BS_LOCKEXCEPT" le  (cost=0.00..1.46 rows=3 width=4) (actual time=0.010..0.010 rows=1 loops=44)"
"                                                                                                Filter: (id_lock = $4)"
"                                                                                          ->  Hash  (cost=1.10..1.10 rows=10 width=8) (actual time=0.030..0.030 rows=10 loops=1)"
"                                                                                                ->  Seq Scan on "BS_EXCEPTION" e  (cost=0.00..1.10 rows=10 width=8) (actual time=0.007..0.018 rows=10 loops=1)"
"                                                                                    ->  Index Scan using fki_ref_action_resource on "RS_ACTION" a  (cost=0.00..6.91 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=44)"
"                                                                                          Index Cond: (a.id_res = e.id_res)"
"                                                                                          Filter: ((a.act_name)::text = 'SELECT'::text)"
"                                                                              ->  Index Scan using fki_ref_acl_user on "RS_ACL" acl  (cost=0.00..7.62 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=44)"
"                                                                                    Index Cond: ((acl.id_usr = 2) AND (acl.id_act = a.id_act))"
"                                                                                    Filter: acl.acl_grant"
"                                                                ->  Seq Scan on "BS_LOCK" l  (cost=0.00..2.20 rows=20 width=8) (actual time=0.003..0.010 rows=15 loops=44)"
"                                                          ->  Index Scan using fki_ref_action_resource on "RS_ACTION" a  (cost=0.00..4.57 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=44)"
"                                                                Index Cond: (a.id_res = l.id_res)"
"                                                                Filter: ((a.act_name)::text = 'SELECT'::text)"
"                                                    ->  Index Scan using fki_ref_acl_user on "RS_ACL" acl  (cost=0.00..7.62 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=44)"
"                                                          Index Cond: ((acl.id_usr = 2) AND (acl.id_act = a.id_act))"
"                                                          Filter: acl.acl_grant"
"                                      ->  Index Scan using fki_ref_tree_object on "BS_TREE" t14051109  (cost=0.00..12.84 rows=1 width=8) (actual time=0.007..0.011 rows=1 loops=44)"
"                                            Index Cond: (t14051109.id_obj = o.id_obj)"
"                                            Filter: (t14051109.id_obj = t14051109.id_parent)"

Plan 8.4 - ca 700 sec

"Limit  (cost=601203.12..601203.13 rows=1 width=225) (actual time=689214.771..689214.823 rows=44 loops=1)"
"  ->  Sort  (cost=601203.12..601203.13 rows=1 width=225) (actual time=689214.768..689214.786 rows=44 loops=1)"
"        Sort Key: o.title, o.rec_class"
"        Sort Method:  quicksort  Memory: 23kB"
"        ->  HashAggregate  (cost=597399.44..601203.11 rows=1 width=225) (actual time=689214.368..689214.422 rows=44 loops=1)"
"              ->  Nested Loop Left Join  (cost=197143.20..597398.39 rows=30 width=225) (actual time=156.163..2141.120 rows=6277 loops=1)"
"                    Join Filter: (o.id_obj = o.id_parent)"
"                    ->  Nested Loop Left Join  (cost=197.72..399317.31 rows=1 width=221) (actual time=79.082..343.174 rows=44 loops=1)"
"                          Join Filter: (o.id_obj = o.id_parent)"
"                          ->  Nested Loop Semi Join  (cost=197.72..399269.82 rows=1 width=221) (actual time=78.923..335.232 rows=44 loops=1)"
"                                Join Filter: (o.id_obj = lo.id_obj)"
"                                ->  Nested Loop  (cost=197.72..202393.10 rows=1 width=237) (actual time=72.008..87.818 rows=44 loops=1)"
"                                      ->  Nested Loop  (cost=197.72..202227.99 rows=13 width=229) (actual time=71.062..85.846 rows=44 loops=1)"
"                                            ->  Merge Semi Join  (cost=197.72..202128.30 rows=13 width=225) (actual time=71.034..84.736 rows=44 loops=1)"
"                                                  Merge Cond: (o.id_obj = lo.id_obj)"
"                                                  ->  Index Scan using "BS_BES_pkey" on "BS_OBJECTS" o  (cost=0.00..2889.31 rows=13 width=221) (actual time=18.061..30.156 rows=44 loops=1)"
"                                                        Filter: ((sec_level <= 4) AND (id_parent = 2059187))"
"                                                  ->  Materialize  (cost=197.72..199223.78 rows=6019 width=4) (actual time=3.078..52.650 rows=2667 loops=1)"
"                                                        ->  Nested Loop  (cost=197.72..199163.59 rows=6019 width=4) (actual time=3.072..50.129 rows=2667 loops=1)"
"                                                              Join Filter: (l.id_lock = lo.id_lock)"
"                                                              ->  Index Scan using fki_ref_objects on "BS_LOCKOBJ" lo  (cost=0.00..196257.32 rows=6019 width=8) (actual time=2.031..7.288 rows=2667 loops=1)"
"                                                                    Filter: (alternatives: SubPlan 5 or hashed SubPlan 6)"
"                                                                    SubPlan 5"
"                                                                      ->  Nested Loop  (cost=0.00..48.79 rows=3 width=0) (never executed)"
"                                                                            ->  Nested Loop  (cost=0.00..25.90 rows=3 width=4) (never executed)"
"                                                                                  ->  Nested Loop  (cost=0.00..5.13 rows=3 width=4) (never executed)"
"                                                                                        Join Filter: (le.id_exc = e.id_exc)"
"                                                                                        ->  Seq Scan on "BS_LOCKEXCEPT" le  (cost=0.00..1.45 rows=3 width=4) (never executed)"
"                                                                                              Filter: (id_lock = $4)"
"                                                                                        ->  Seq Scan on "BS_EXCEPTION" e  (cost=0.00..1.10 rows=10 width=8) (never executed)"
"                                                                                  ->  Index Scan using fki_ref_action_resource on "RS_ACTION" a  (cost=0.00..6.91 rows=1 width=8) (never executed)"
"                                                                                        Index Cond: (a.id_res = e.id_res)"
"                                                                                        Filter: ((a.act_name)::text = 'SELECT'::text)"
"                                                                            ->  Index Scan using fki_ref_acl_user on "RS_ACL" acl  (cost=0.00..7.62 rows=1 width=4) (never executed)"
"                                                                                  Index Cond: ((acl.id_usr = 2) AND (acl.id_act = a.id_act))"
"                                                                                  Filter: acl.acl_grant"
"                                                                    SubPlan 6"
"                                                                      ->  Hash Join  (cost=3.04..122.36 rows=36 width=4) (actual time=1.351..1.752 rows=27 loops=1)"
"                                                                            Hash Cond: (e.id_exc = le.id_exc)"
"                                                                            ->  Nested Loop  (cost=1.23..120.07 rows=10 width=4) (actual time=1.226..1.596 rows=6 loops=1)"
"                                                                                  ->  Hash Join  (cost=1.23..43.77 rows=10 width=8) (actual time=1.031..1.159 rows=10 loops=1)"
"                                                                                        Hash Cond: (a.id_res = e.id_res)"
"                                                                                        ->  Seq Scan on "RS_ACTION" a  (cost=0.00..42.17 rows=72 width=8) (actual time=0.062..0.982 rows=72 loops=1)"
"                                                                                              Filter: ((act_name)::text = 'SELECT'::text)"
"                                                                                        ->  Hash  (cost=1.10..1.10 rows=10 width=8) (actual time=0.074..0.074 rows=10 loops=1)"
"                                                                                              ->  Seq Scan on "BS_EXCEPTION" e  (cost=0.00..1.10 rows=10 width=8) (actual time=0.052..0.062 rows=10 loops=1)"
"                                                                                  ->  Index Scan using fki_ref_acl_user on "RS_ACL" acl  (cost=0.00..7.62 rows=1 width=4) (actual time=0.039..0.040 rows=1 loops=10)"
"                                                                                        Index Cond: ((acl.id_usr = 2) AND (acl.id_act = a.id_act))"
"                                                                                        Filter: acl.acl_grant"
"                                                                            ->  Hash  (cost=1.36..1.36 rows=36 width=8) (actual time=0.101..0.101 rows=36 loops=1)"
"                                                                                  ->  Seq Scan on "BS_LOCKEXCEPT" le  (cost=0.00..1.36 rows=36 width=8) (actual time=0.051..0.074 rows=36 loops=1)"
"                                                              ->  Materialize  (cost=197.72..197.92 rows=20 width=4) (actual time=0.001..0.007 rows=14 loops=2667)"
"                                                                    ->  Nested Loop  (cost=2.45..197.70 rows=20 width=4) (actual time=0.700..1.279 rows=14 loops=1)"
"                                                                          ->  Hash Join  (cost=2.45..45.10 rows=20 width=8) (actual time=0.685..0.817 rows=20 loops=1)"
"                                                                                Hash Cond: (a.id_res = l.id_res)"
"                                                                                ->  Seq Scan on "RS_ACTION" a  (cost=0.00..42.17 rows=72 width=8) (actual time=0.007..0.607 rows=72 loops=1)"
"                                                                                      Filter: ((act_name)::text = 'SELECT'::text)"
"                                                                                ->  Hash  (cost=2.20..2.20 rows=20 width=8) (actual time=0.114..0.114 rows=20 loops=1)"
"                                                                                      ->  Seq Scan on "BS_LOCK" l  (cost=0.00..2.20 rows=20 width=8) (actual time=0.054..0.097 rows=20 loops=1)"
"                                                                          ->  Index Scan using fki_ref_acl_user on "RS_ACL" acl  (cost=0.00..7.62 rows=1 width=4) (actual time=0.019..0.020 rows=1 loops=20)"
"                                                                                Index Cond: ((acl.id_usr = 2) AND (acl.id_act = a.id_act))"
"                                                                                Filter: acl.acl_grant"
"                                            ->  Index Scan using "BS_BES_pkey" on "BS_OBJECTS" o  (cost=0.00..7.66 rows=1 width=4) (actual time=0.017..0.019 rows=1 loops=44)"
"                                                  Index Cond: (o.id_obj = o.id_obj)"
"                                                  Filter: (o.sec_level <= 4)"
"                                      ->  Index Scan using fki_ref_tree_object on "BS_TREE" t14051109  (cost=0.00..12.69 rows=1 width=8) (actual time=0.037..0.040 rows=1 loops=44)"
"                                            Index Cond: (t14051109.id_obj = o.id_obj)"
"                                            Filter: (t14051109.id_obj = t14051109.id_parent)"

das erste was mir auffaellt ist, dass in 8.3 GroupAggregate verwendet wird . und in 8.4 HashAggregate

und desweiteren irgendwelche SemiJoins …

Nun ja, dann poste es halt auf -hackers und schau, was bei herauskommt.

hier noch das ende des plans (der gleiche subplan)… da sieht man auch - was voellig anders laeuft wieder einer dieser semi-joins statt dem ehemaligen index-scan
8.3:

"        SubPlan"
"          ->  Result  (cost=117.49..117.50 rows=1 width=0) (actual time=0.115..0.115 rows=1 loops=6277)"
"                One-Time Filter: $3"
"                InitPlan"
"                  ->  Index Scan using idx_objects_signum on "BS_OBJECTS" o  (cost=0.00..4347.07 rows=37 width=4) (actual time=0.112..0.112 rows=1 loops=6277)"
"                        Index Cond: (id_parent = $2)"
"                        Filter: ((sec_level <= 4) AND (subplan))"
"                        SubPlan"
"                          ->  Nested Loop  (cost=0.00..54.10 rows=1 width=0) (actual time=0.098..0.098 rows=1 loops=6276)"
"                                ->  Nested Loop  (cost=0.00..46.47 rows=1 width=4) (actual time=0.086..0.086 rows=1 loops=6276)"
"                                      ->  Nested Loop  (cost=0.00..41.88 rows=1 width=4) (actual time=0.077..0.077 rows=1 loops=6276)"
"                                            Join Filter: (lo.id_lock = l.id_lock)"
"                                            ->  Index Scan using fki_ref_objects on "BS_LOCKOBJ" lo  (cost=0.00..39.43 rows=1 width=4) (actual time=0.049..0.049 rows=1 loops=6276)"
"                                                  Index Cond: (id_obj = $1)"
"                                                  Filter: (subplan)"
"                                                  SubPlan"
"                                                    ->  Nested Loop  (cost=1.23..31.13 rows=1 width=0) (actual time=0.040..0.040 rows=1 loops=6276)"
"                                                          ->  Nested Loop  (cost=1.23..23.50 rows=1 width=4) (actual time=0.028..0.028 rows=1 loops=6276)"
"                                                                ->  Hash Join  (cost=1.23..2.73 rows=3 width=4) (actual time=0.017..0.017 rows=1 loops=6276)"
"                                                                      Hash Cond: (le.id_exc = e.id_exc)"
"                                                                      ->  Seq Scan on "BS_LOCKEXCEPT" le  (cost=0.00..1.46 rows=3 width=4) (actual time=0.009..0.009 rows=1 loops=6276)"
"                                                                            Filter: (id_lock = $0)"
"                                                                      ->  Hash  (cost=1.10..1.10 rows=10 width=8) (actual time=0.025..0.025 rows=10 loops=1)"
"                                                                            ->  Seq Scan on "BS_EXCEPTION" e  (cost=0.00..1.10 rows=10 width=8) (actual time=0.006..0.015 rows=10 loops=1)"
"                                                                ->  Index Scan using fki_ref_action_resource on "RS_ACTION" a  (cost=0.00..6.91 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=6276)"
"                                                                      Index Cond: (a.id_res = e.id_res)"
"                                                                      Filter: ((a.act_name)::text = 'SELECT'::text)"
"                                                          ->  Index Scan using fki_ref_acl_user on "RS_ACL" acl  (cost=0.00..7.62 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=6276)"
"                                                                Index Cond: ((acl.id_usr = 2) AND (acl.id_act = a.id_act))"
"                                                                Filter: acl.acl_grant"
 ....... gekuerzt
"Total runtime: 1522.387 ms"

8.4:

"              SubPlan 4"
"                ->  Result  (cost=3803.63..3803.64 rows=1 width=0) (actual time=109.443..109.443 rows=1 loops=6277)"
"                      One-Time Filter: $3"
"                      InitPlan 3 (returns $3)"
"                        ->  Merge Semi Join  (cost=197.72..202128.84 rows=56 width=0) (actual time=109.434..109.434 rows=1 loops=6277)"
"                              Merge Cond: (o.id_obj = lo.id_obj)"
"                              ->  Index Scan using "BS_BES_pkey" on "BS_OBJECTS" o  (cost=0.00..2889.31 rows=56 width=4) (actual time=6.130..6.130 rows=1 loops=6277)"
"                                    Filter: ((sec_level <= 4) AND (id_parent = $2))"
"                              ->  Materialize  (cost=197.72..199223.78 rows=6019 width=4) (actual time=0.042..100.024 rows=5658 loops=6276)"
"                                    ->  Nested Loop  (cost=197.72..199163.59 rows=6019 width=4) (actual time=0.037..94.818 rows=5658 loops=6276)"
"                                          Join Filter: (l.id_lock = lo.id_lock)"
"                                          ->  Index Scan using fki_ref_objects on "BS_LOCKOBJ" lo  (cost=0.00..196257.32 rows=6019 width=8) (actual time=0.019..7.775 rows=5658 loops=6276)"
"                                                Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)"
"                                                SubPlan 1"
"                                                  ->  Nested Loop  (cost=0.00..48.79 rows=3 width=0) (never executed)"
"                                                        ->  Nested Loop  (cost=0.00..25.90 rows=3 width=4) (never executed)"
"                                                              ->  Nested Loop  (cost=0.00..5.13 rows=3 width=4) (never executed)"
"                                                                    Join Filter: (le.id_exc = e.id_exc)"
"                                                                    ->  Seq Scan on "BS_LOCKEXCEPT" le  (cost=0.00..1.45 rows=3 width=4) (never executed)"
"                                                                          Filter: (id_lock = $0)"
"                                                                    ->  Seq Scan on "BS_EXCEPTION" e  (cost=0.00..1.10 rows=10 width=8) (never executed)"
"                                                              ->  Index Scan using fki_ref_action_resource on "RS_ACTION" a  (cost=0.00..6.91 rows=1 width=8) (never executed)"
"                                                                    Index Cond: (a.id_res = e.id_res)"
"                                                                    Filter: ((a.act_name)::text = 'SELECT'::text)"
"                                                        ->  Index Scan using fki_ref_acl_user on "RS_ACL" acl  (cost=0.00..7.62 rows=1 width=4) (never executed)"
"                                                              Index Cond: ((acl.id_usr = 2) AND (acl.id_act = a.id_act))"
"                                                              Filter: acl.acl_grant"
"                                                SubPlan 2"
"                                                  ->  Hash Join  (cost=3.04..122.36 rows=36 width=4) (actual time=0.723..0.973 rows=27 loops=1)"
"                                                        Hash Cond: (e.id_exc = le.id_exc)"
"                                                        ->  Nested Loop  (cost=1.23..120.07 rows=10 width=4) (actual time=0.657..0.878 rows=6 loops=1)"
"                                                              ->  Hash Join  (cost=1.23..43.77 rows=10 width=8) (actual time=0.635..0.740 rows=10 loops=1)"
"                                                                    Hash Cond: (a.id_res = e.id_res)"
"                                                                    ->  Seq Scan on "RS_ACTION" a  (cost=0.00..42.17 rows=72 width=8) (actual time=0.012..0.635 rows=72 loops=1)"
"                                                                          Filter: ((act_name)::text = 'SELECT'::text)"
"                                                                    ->  Hash  (cost=1.10..1.10 rows=10 width=8) (actual time=0.026..0.026 rows=10 loops=1)"
"                                                                          ->  Seq Scan on "BS_EXCEPTION" e  (cost=0.00..1.10 rows=10 width=8) (actual time=0.006..0.015 rows=10 loops=1)"
"                                                              ->  Index Scan using fki_ref_acl_user on "RS_ACL" acl  (cost=0.00..7.62 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=10)"
"                                                                    Index Cond: ((acl.id_usr = 2) AND (acl.id_act = a.id_act))"
"                                                                    Filter: acl.acl_grant"
"                                                        ->  Hash  (cost=1.36..1.36 rows=36 width=8) (actual time=0.050..0.050 rows=36 loops=1)"
"                                                              ->  Seq Scan on "BS_LOCKEXCEPT" le  (cost=0.00..1.36 rows=36 width=8) (actual time=0.005..0.027 rows=36 loops=1)"
"                                          ->  Materialize  (cost=197.72..197.92 rows=20 width=4) (actual time=0.000..0.006 rows=14 loops=35511609)"
"                                                ->  Nested Loop  (cost=2.45..197.70 rows=20 width=4) (actual time=0.637..1.007 rows=14 loops=1)"
"                                                      ->  Hash Join  (cost=2.45..45.10 rows=20 width=8) (actual time=0.623..0.756 rows=20 loops=1)"
"                                                            Hash Cond: (a.id_res = l.id_res)"
"                                                            ->  Seq Scan on "RS_ACTION" a  (cost=0.00..42.17 rows=72 width=8) (actual time=0.006..0.624 rows=72 loops=1)"
"                                                                  Filter: ((act_name)::text = 'SELECT'::text)"
"                                                            ->  Hash  (cost=2.20..2.20 rows=20 width=8) (actual time=0.038..0.038 rows=20 loops=1)"
....... gekeurzt
"Total runtime: 689219.093 ms"

zumindest in meiner DB

Create explicit concepts of semi-joins and anti-joins (Tom)

This work formalizes our previous ad-hoc treatment of IN (SELECT …) clauses, and extends it to EXISTS and NOT EXISTS clauses. It should result in significantly better planning of EXISTS and NOT EXISTS queries. In general, logically equivalent IN and EXISTS clauses should now have similar performance, whereas previously IN often won.

Dann schick das an Tom, er wird Dir dankbar sein. Er ist der Guru…

Andreas

eMail ? Link ? Irgendwas ?

ich kenn mich in der PG-Community nicht so gut aus

postgresql.org, Mailinglists, nach “-hackers” suchen, dort anmelden, Posting schreiben.

argh anmelden … da geb ich ja meine fiktive anonymität auf!!!

Hi,

dann schick mir das komplette Query, beide Pläne komplett, Schemadefinition und die Konfig beider Serverinstanzen(der auskommentierte Teil reicht). Falls du besondere Sessionvariablen vor der Abfrage setzt(enable_xxx=false, set work_mem=1GB oder so) bitte auch angeben.

Ich setze gerne ein Posting für dich ab.

Dieser Teil des Planners ist, wie du schon bemerkt hast, neu, da ist so ein Testcase genau das Richtige zum Stresstesten.

Freundliche Grüße
hkocam

das nenn ich ja mal ein konsequentes angebot … danke … ich hab mir aber schon ne mail vorbereitet … werd sie heut abend abschicken …

was meinst du … reichen die beiden kompletten plans und die query zum vergleich?

die DB ist aus dem Leben - die werde ich sicher nicht schicken :slight_smile:

als Entwickler kann ich mir aber schon vorstellen, dass man aus der komplexität der Query und der größe der Plans einiges herauslesen kann. Also wenn ihr noch Ideen habt was ich mitschicken sollte - immer raus damit.

übrigends ists schade dass es nicht sowas wie bugzilla für postgresql gibt … im rahmen von SWT arbeite ich damit - ist recht hübsch …

Hi,


es wird sicher rückfragen geben, aber als Minimum solltest du die Tabellendefinitionen mitschicken, damit man weiß welche Datentypen , wekche FK/PK/Indize gesetzt sind und verwendet werden.
Am besten wäre es sicherlich, wenn du das Problem mit einem minimalem Testcase, der deine Problemstellung wiederspiegelt darstellen könntest.
Damit hättest du auch nicht mehr das Problem evtl. sensible Informatioen rauszugeben.

Naja es gibt ein WebFormular(PostgreSQL: Report a bug)
das dann eine Mail auf die pgsql-bugs-Mailingliste auslöst.
Die wird bestimmt von jmd. gelesen, aber so funktioniert die Entwicklung und das Support-Modell bei PG nicht.
PG ist ein Community-Projekt bei dem alle helfen können, die helfen wollen.
Die Kommunikation erfolgt über Mailinglisten.
Für generelle Probleme gibt es pgsql-general. Dort antworten eher Benutzer als Entwickler.
Die Entwickler erreichtst du am schnellsten per Mail an pgsql-hackers. Jeder Bugreport zur aktuellen Beta wird dort sicherlich schnellstens beantwortet, vermutlich sogar vom verantwortlichen Entwickler für das Feature, mit dem du ein Problem hast.

Freundliche Grüße
hkocam

Nagut - ich werd ja sehen

auch so - auch bei bugzilla werden Mails ausgeloest - Empfänger sind nicht einfach alle, sondern jene die sich für ein Problem oder eine Funktion interessieren / resp. verantwortlich fühlen / sind … die sind halt im Verteiler eingetragen …

wie auch immer - hauptsache der Prozess funktioniert

Die Frage wird ab und zu mal aufgeworfen:

Murray’s Blog » Blog Archive » PostgreSQL has no Bugzilla