Home > mailing lists

Two different execution plans for similar requests - Mailing list pgsql-performance

From	Joby Joba
Subject	Two different execution plans for similar requests
Date	March 1, 2011 07:46:57
Msg-id	AANLkTinfNhCyMUysjcv2HP6GS6Y_9U9TZgn_QWgXq5Ew@mail.gmail.com Whole thread Raw
Responses	Re: Two different execution plans for similar requests (tv@fuzzy.cz)
List	pgsql-performance

Tree view

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

pgsql-performance by date:

From: Marc Cousin
Date: 01 March 2011, 06:42:56
Subject: Re: inheritance: planning time vs children number vs column number

From: tv@fuzzy.cz
Date: 01 March 2011, 08:10:12
Subject: Re: Two different execution plans for similar requests

Two different execution plans for similar requests - Mailing list pgsql-performance

Previous

Next