Message-ID: <bc0d8e3dcc7832b0d29e41de75879087.squirrel@sq.gransy.com>
In-Reply-To: <AANLkTinfNhCyMUysjcv2HP6GS6Y_9U9TZgn_QWgXq5Ew@mail.gmail.com>
References: <AANLkTinfNhCyMUysjcv2HP6GS6Y_9U9TZgn_QWgXq5Ew@mail.gmail.com>
Date: Tue, 1 Mar 2011 10:10:02 +0100
Subject: Re: Two different execution plans for similar requests
From: tv@fuzzy.cz
To: "Joby Joba" <jobyjoba59@gmail.com>
Cc: pgsql-performance@postgresql.org
User-Agent: SquirrelMail/1.4.21
MIME-Version: 1.0
Content-Type: text/plain;charset=utf-8
Content-Transfer-Encoding: 8bit
Importance: Normal

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
>