PSQL 8.4 - partittions - join tables - not optimal plan

Поиск
Список
Период
Сортировка
От Vratislav Benes
Тема PSQL 8.4 - partittions - join tables - not optimal plan
Дата
Msg-id 4AA52920.6090507@optisolutions.cz
обсуждение исходный текст
Ответы Re: PSQL 8.4 - partittions - join tables - not optimal plan  ("A. Kretschmer" <andreas.kretschmer@schollglas.com>)
Список pgsql-performance
Hello,

could you help me with joined query from partitioned table, please? I have a table "data" with partitions  by period_id

CREATE TABLE data
(
  period_id smallint NOT NULL DEFAULT 0,
  store_id smallint NOT NULL DEFAULT 0,
  product_id integer NOT NULL DEFAULT 0,
  s_pcs real NOT NULL DEFAULT 0,
  s_val real NOT NULL DEFAULT 0
)

CONSTRAINT data_561_period_id_check CHECK (period_id = 561)
CONSTRAINT data_562_period_id_check CHECK (period_id = 562)
...

When I run a simple query with a condition period_id = something I get best query plan:

explain select sum(s_pcs),sum(s_val)
from data d inner join periods p on d.period_id=p.period_id
 where p.period_id=694;
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Aggregate  (cost=214028.71..214028.72 rows=1 width=8)
   ->  Nested Loop  (cost=0.00..181511.71 rows=6503400 width=8)
         ->  Index Scan using pk_periods on periods p  (cost=0.00..8.27 rows=1 width=2)
               Index Cond: (period_id = 694)
         ->  Append  (cost=0.00..116469.44 rows=6503400 width=10)
                     ->  Seq Scan on data_694 d  (cost=0.00..116446.44 rows=6503395 width=10)
                     Filter: (d.period_id = 694)
(8 rows)


but when I try make a condition by join table, the query plan is not optimal:


select period_id from periods where y=2009 and w=14;
 period_id
-----------
       704
(1 row)


explain select sum(s_pcs),sum(s_val)
from data d inner join periods p on d.period_id=p.period_id
where p.y=2009 and p.w=14;                                 
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Aggregate  (cost=15313300.27..15313300.28 rows=1 width=8)
   ->  Hash Join  (cost=8.92..15293392.89 rows=3981476 width=8)
         Hash Cond: (d.period_id = p.period_id)
         ->  Append  (cost=0.00..12267462.15 rows=796295215 width=10)
               ->  Seq Scan on data d  (cost=0.00..20.40 rows=1040 width=10)
               ->  Seq Scan on data_561 d  (cost=0.00..66903.25 rows=4342825 width=10)
               ->  Seq Scan on data_562 d  (cost=0.00..73481.02 rows=4769802 width=10)
               ->  Seq Scan on data_563 d  (cost=0.00..73710.95 rows=4784695 width=10)
               ->  Seq Scan on data_564 d  (cost=0.00..71869.75 rows=4665175 width=10)
               ->  Seq Scan on data_565 d  (cost=0.00..72850.37 rows=4728837 width=10)
   ...


I get same result with constraint_exclusion = partition and constraint_exclusion = on.
Do you have any idea where can be a problem?

For simple query the partitions works perfect on this table (about 2*10^9 records) but the joined query is an problem.


Thank you very much, Vrata

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

Предыдущее
От: Vincent de Phily
Дата:
Сообщение: Re: slow query : very simple delete, 100% cpu, nearly no disk activity
Следующее
От: Eugene Morozov
Дата:
Сообщение: Re: Forcing postgresql to use an index