Hi all !
Postgresql (8.2) has as a strange behaviour in some of my environments.
A request follows two execution plans ( but not always !!! ). I encounter some difficulties to reproduce the case.
J-2
Aggregate (cost=2323350.24..2323350.28 rows=1 width=24)
-> Merge Join (cost=2214044.98..2322432.49 rows=91774 width=24)
Merge Cond: ((azy_header.txhd_azy_nr = azy_detail.txhd_azy_nr) AND ((azy_header.till_short_desc)::text = inner"."?column8?") AND ((azy_header.orgu_xxx)::text = "inner"."?column9?") AND ((azy_header.orgu_xxx_cmpy)::text = "inner"."?column10?"))"
-> Sort (cost=409971.56..410050.39 rows=31532 width=77)
Sort Key: azy_queue.txhd_azy_nr, (azy_queue.till_short_desc)::text, (azy_queue.orgu_xxx)::text, (azy_queue.orgu_xxx_cmpy)::text
-> Nested Loop (cost=0.00..407615.41 rows=31532 width=77)
-> Nested Loop (cost=0.00..70178.58 rows=52216 width=46)
Join Filter: (((azy_queue.orgu_xxx_cmpy)::text = (firma_session.orgu_xxx_cmpy)::text) AND ((azy_queue.orgu_xxx)::text = (firma_session.orgu_xxx)::text))
-> Seq Scan on firma_session (cost=0.00..599.29 rows=401 width=25)
Filter: ((cssn_trading_date >= '20110226'::bpchar) AND (cssn_trading_date <= '20110226'::bpchar))
-> Index Scan using azyq_ix2 on azy_queue (cost=0.00..165.92 rows=434 width=41)
Index Cond: (azy_queue.cssn_session_id = firma_session.cssn_session_id)
-> Index Scan using txhd_pk on azy_header (cost=0.00..6.44 rows=1 width=31)
Index Cond: (((azy_queue.orgu_xxx_cmpy)::text = (azy_header.orgu_xxx_cmpy)::text) AND ((azy_queue.orgu_xxx)::text = (azy_header.orgu_xxx)::text) AND ((azy_queue.till_short_desc)::text = (azy_header.till_short_desc)::text) AND (azy_queue.txhd_azy_nr = azy_header.txhd_azy_nr))
Filter: (txhd_voided = 0::numeric)
-> Sort (cost=1804073.42..1825494.05 rows=8568252 width=55)
Sort Key: azy_detail.txhd_azy_nr, (azy_detail.till_short_desc)::text, (azy_detail.orgu_xxx)::text, (azy_detail.orgu_xxx_cmpy)::text
-> Seq Scan on azy_detail (cost=0.00..509908.30 rows=8568252 width=55)
Filter: (txde_item_void = 0::numeric)
J-1
Aggregate (cost=10188.38..10188.42 rows=1 width=24)
-> Nested Loop (cost=0.00..10186.08 rows=229 width=24)
-> Nested Loop (cost=0.00..2028.51 rows=79 width=77)
-> Nested Loop (cost=0.00..865.09 rows=130 width=46)
Join Filter: (((azy_queue.orgu_xxx_cmpy)::text = (firma_session.orgu_xxx_cmpy)::text) AND ((azy_queue.orgu_xxx)::text = (firma_session.orgu_xxx)::text))
-> Seq Scan on firma_session (cost=0.00..599.29 rows=1 width=25)
Filter: ((cssn_trading_date >= '20110227'::bpchar) AND (cssn_trading_date <= '20110227'::bpchar))
-> Index Scan using azyq_ix2 on azy_queue (cost=0.00..258.20 rows=434 width=41)
Index Cond: (azy_queue.cssn_session_id = firma_session.cssn_session_id)
-> Index Scan using txhd_pk on azy_header (cost=0.00..8.93 rows=1 width=31)
Index Cond: (((azy_queue.orgu_xxx_cmpy)::text = (azy_header.orgu_xxx_cmpy)::text) AND ((azy_queue.orgu_xxx)::text = (azy_header.orgu_xxx)::text) AND ((azy_queue.till_short_desc)::text = (azy_header.till_short_desc)::text) AND (azy_queue.txhd_azy_nr = azy_header.txhd_azy_nr))
Filter: (txhd_voided = 0::numeric)
-> Index Scan using txde_pk on azy_detail (cost=0.00..102.26 rows=50 width=55)
Index Cond: (((azy_detail.orgu_xxx_cmpy)::text = (azy_header.orgu_xxx_cmpy)::text) AND ((azy_detail.orgu_xxx)::text = (azy_header.orgu_xxx)::text) AND ((azy_detail.till_short_desc)::text = (azy_header.till_short_desc)::text) AND (azy_detail.txhd_azy_nr = azy_header.txhd_azy_nr))
Filter: (txde_item_void = 0::numeric)
Where shall I investigate ?
Thanks for your help
