Re: Two different execution plans for similar requests

I've already used an 'EXPLAIN ANALYZE' to post the message. So I don't
clearly understand what you are expecting for, when you tell me to provide
'EXPLAIN ANALYZE' (please excuse me for the misunderstood)

I agree with you when you say that for two different values, the costs will
be different. But I probably forgot to tell that one day the cost is very
high and at another moment this cost for the same value is lower. And there
is no vacuum/analyze between the two executions.

Regards

Joby

2011/3/1 <tv(at)fuzzy(dot)cz>

> Hi, and why do you think this is a problem?
>
> The explain plan is expected to change for different parameter values,
> that's OK. The merge in the first query is expected to produce
> significantly more rows (91774) than the other one (229). That's why the
> second query chooses nested loop instead of merge join ...
>
> But it's difficult to say if those plans are OK, as you have posted just
> EXPLAIN output - please, provide 'EXPLAIN ANALYZE' output so that we can
> see if the stats are off.
>
> regards
> Tomas
>
> > *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
> >
>
>
>