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 по дате отправления:

Предыдущее
От: Justin Clift
Дата:
Сообщение: Re: Low Budget Performance, Part 2
Следующее
От: Andrew Sullivan
Дата:
Сообщение: Re: v7.2.3 versus v7.3 -> huge performance penalty for JOIN with UNION