v7.2.3 versus v7.3 -> huge performance penalty for JOIN with UNION
От | ir. F.T.M. van Vugt bc. |
---|---|
Тема | v7.2.3 versus v7.3 -> huge performance penalty for JOIN with UNION |
Дата | |
Msg-id | 200212021645.55032.ftm.van.vugt@foxi.nl обсуждение исходный текст |
Ответ на | Re: Query performance discontinuity (Rod Taylor <rbt@rbt.ca>) |
Ответы |
Re: v7.2.3 versus v7.3 -> huge performance penalty for JOIN with UNION
(Andrew Sullivan <andrew@libertyrms.info>)
Re: v7.2.3 versus v7.3 -> huge performance penalty for JOIN with UNION (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-performance |
L.S. The query below runs 10-20 times slower under v7.3 than it did under v7.2.3: - hardware is the same - standard install of postgresql, both version had stats-collection enabled - v7.2.3 had no multibyte and no locale, obviously v7.3 does - *very* recent vacuum analyse I expected some overhead due to the enabled mulitbyte, but not this much.. ;( BTW, there are a few other queries that are performing *real* slow, but I'm hoping this one will give away a cause for the overall problem... Could anybody offer an idea? trial=# explain analyse select foo.*, c.id from (select *, 't' from lijst01_table union all select *, 't' from lijst02_table union all select *, 'f' from lijst03_table union all select *, 'f' from lijst04_table union all select *, 't' from lijst04b_table ) as foo inner join creditor c on foo.dflt_creditor_id = c.old_creditor_id order by old_id; * foo.dflt_creditor_id is of type varchar(20) * c.old_creditor_id is of type text The plan below shows something weird is happening during the join, but I can't explain it. TIA, Frank. QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ Sort (cost=54103.74..54116.18 rows=4976 width=498) (actual time=234595.27..234607.58 rows=4976 loops=1) Sort Key: foo.old_id -> Nested Loop (cost=0.00..53798.19 rows=4976 width=498) (actual time=7559.20..234476.70 rows=4976 loops=1) Join Filter: ("inner".dflt_creditor_id = ("outer".old_creditor_id)::text) -> Seq Scan on creditor c (cost=0.00..8.27 rows=227 width=14) (actual time=0.05..7.35 rows=227 loops=1) -> Subquery Scan foo (cost=0.00..174.76 rows=4976 width=150) (actual time=0.25..969.47 rows=4976 loops=227) -> Append (cost=0.00..174.76 rows=4976 width=150) (actual time=0.20..658.14 rows=4976 loops=227) -> Subquery Scan "*SELECT* 1" (cost=0.00..2.46 rows=46 width=145) (actual time=0.19..6.26 rows=46 loops=227) -> Seq Scan on lijst01_table (cost=0.00..2.46 rows=46 width=145) (actual time=0.10..3.40 rows=46 loops=227) -> Subquery Scan "*SELECT* 2" (cost=0.00..30.62 rows=862 width=150) (actual time=0.16..111.38 rows=862 loops=227) -> Seq Scan on lijst02_table (cost=0.00..30.62 rows=862 width=150) (actual time=0.09..59.79 rows=862 loops=227) -> Subquery Scan "*SELECT* 3" (cost=0.00..48.63 rows=1363 width=148) (actual time=0.16..166.98 rows=1363 loops=227) -> Seq Scan on lijst03_table (cost=0.00..48.63 rows=1363 width=148) (actual time=0.09..87.45 rows=1363 loops=227) -> Subquery Scan "*SELECT* 4" (cost=0.00..92.03 rows=2703 width=134) (actual time=0.15..338.66 rows=2703 loops=227) -> Seq Scan on lijst04_table (cost=0.00..92.03 rows=2703 width=134) (actual time=0.09..176.41 rows=2703 loops=227) -> Subquery Scan "*SELECT* 5" (cost=0.00..1.02 rows=2 width=134) (actual time=0.16..0.28 rows=2 loops=227) -> Seq Scan on lijst04b_table (cost=0.00..1.02 rows=2 width=134) (actual time=0.09..0.16 rows=2 loops=227) Total runtime: 234624.07 msec (18 rows)
В списке pgsql-performance по дате отправления:
Следующее
От: Andrew SullivanДата:
Сообщение: Re: v7.2.3 versus v7.3 -> huge performance penalty for JOIN with UNION