plan difference between set-returning function with ROWS within IN() and a plain join

Поиск
Список
Период
Сортировка
От Frank van Vugt
Тема plan difference between set-returning function with ROWS within IN() and a plain join
Дата
Msg-id 200805061021.43448.ftm.van.vugt@foxi.nl
обсуждение исходный текст
Ответы Re: plan difference between set-returning function with ROWS within IN() and a plain join  (PFC <lists@peufeu.com>)
Re: plan difference between set-returning function with ROWS within IN() and a plain join  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
L.S.

I'm noticing a difference in planning between a join and an in() clause,
before trying to create an independent test-case, I'd like to know if there's
an obvious reason why this would be happening:


=> the relatively simple PLPGSQL si_credit_tree() function has 'ROWS 5' in
it's definition


df=# select version();
                                version
------------------------------------------------------------------------
 PostgreSQL 8.3.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
(1 row)



db=# explain analyse
    select sum(si.base_total_val)
    from sales_invoice si, si_credit_tree(80500007) foo(id)
    where si.id = foo.id;
                                                                    QUERY PLAN
                   
-----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=42.73..42.74 rows=1 width=8) (actual time=0.458..0.459
rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..42.71 rows=5 width=8) (actual
time=0.361..0.429 rows=5 loops=1)
         ->  Function Scan on si_credit_tree foo  (cost=0.00..1.30 rows=5
width=4) (actual time=0.339..0.347 rows=5 loops=1)
         ->  Index Scan using sales_invoice_pkey on sales_invoice si
(cost=0.00..8.27 rows=1 width=12) (actual time=0.006..0.008 rows=1 loops=5)
               Index Cond: (si.id = foo.id)

Total runtime: 0.562 ms




db=# explain analyse
    select sum(base_total_val)
    from sales_invoice
    where id in (select id from si_credit_tree(80500007));
                                                                   QUERY PLAN
                   
-----------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=15338.31..15338.32 rows=1 width=8) (actual
time=3349.401..3349.402 rows=1 loops=1)
   ->  Seq Scan on sales_invoice  (cost=0.00..15311.19 rows=10846 width=8)
(actual time=0.781..3279.046 rows=21703 loops=1)
         Filter: (subplan)
         SubPlan
           ->  Function Scan on si_credit_tree  (cost=0.00..1.30 rows=5
width=0) (actual time=0.146..0.146 rows=1 loops=21703)

Total runtime: 3349.501 ms





I'd hoped the planner would use the ROWS=5 knowledge a bit better:


db=# explain analyse
    select sum(base_total_val)
    from sales_invoice
    where id in (80500007,80500008,80500009,80500010,80500011);
                                                            QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=40.21..40.22 rows=1 width=8) (actual time=0.105..0.106
rows=1 loops=1)
   ->  Bitmap Heap Scan on sales_invoice  (cost=21.29..40.19 rows=5 width=8)
(actual time=0.061..0.070 rows=5 loops=1)
         Recheck Cond: (id = ANY
('{80500007,80500008,80500009,80500010,80500011}'::integer[]))
         ->  Bitmap Index Scan on sales_invoice_pkey  (cost=0.00..21.29 rows=5
width=0) (actual time=0.049..0.049 rows=5 loops=1)
               Index Cond: (id = ANY
('{80500007,80500008,80500009,80500010,80500011}'::integer[]))

Total runtime: 0.201 ms






--
Best,




Frank.

В списке pgsql-performance по дате отправления:

Предыдущее
От: PFC
Дата:
Сообщение: Re: need to speed up query
Следующее
От: PFC
Дата:
Сообщение: Re: plan difference between set-returning function with ROWS within IN() and a plain join