partition query using Seq Scan even when index is present

Поиск
Список
Период
Сортировка
Искать
От
Kenneth Cox
Тема
partition query using Seq Scan even when index is present
Дата
Msg-id
op.uznbv8qi5ru9c3@kent60.office.vivox.com
Список
Дерево обсуждения
partition query using Seq Scan even when index is present "Kenneth Cox" <kenstir@gmail.com>
Re: partition query using Seq Scan even when index is present Merlin Moncure <mmoncure@gmail.com>
Re: partition query using Seq Scan even when index is present Greg Jaman <gjaman@gmail.com>
Re: partition query using Seq Scan even when index is present "Kenneth Cox" <kenstir@gmail.com>
With postgresql-8.3.6, I have many partitions inheriting a table.  SELECT  
min() on the parent performs a Seq Scan, but SELECT min() on a child uses  
the index.  Is this another case where the planner is not aware enough to  
come up with the best plan?  I tried creating an index on the parent table  
to no avail.  Is there a way to formulate the query so that it uses the  
index?  Here is the general flavor:

create table calls (caller text, ts timestamptz);
create table calls_partition_2009_08 (check (ts >= '2009-08-01' and ts <  
'2009-09-01')) inherits (calls);
create index calls_partition_2009_08_ts on calls_partition_2009_08 (ts);
insert into calls_partition_2009_08 (ts)
   select to_timestamp(unix_time)
     from generate_series(extract(epoch from  
'2009-08-01'::timestamptz)::int,
                          extract(epoch from '2009-08-31  
23:59'::timestamptz)::int, 60) as unix_time;
analyze calls_partition_2009_08;
explain select min(ts) from calls;

                                           QUERY PLAN
-----------------------------------------------------------------------------------------------
  Aggregate  (cost=780.50..780.51 rows=1 width=8)
    ->  Append  (cost=0.00..666.00 rows=45800 width=8)
          ->  Seq Scan on calls  (cost=0.00..21.60 rows=1160 width=8)
          ->  Seq Scan on calls_partition_2009_08 calls  (cost=0.00..644.40  
rows=44640 width=8)
(4 rows)

explain select min(ts) from calls_partition_2009_08;

                                                           QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
  Result  (cost=0.03..0.04 rows=1 width=0)
    InitPlan
      ->  Limit  (cost=0.00..0.03 rows=1 width=8)
            ->  Index Scan using calls_partition_2009_08_ts on  
calls_partition_2009_08  (cost=0.00..1366.85 rows=44640 width=8)
                  Filter: (ts IS NOT NULL)
(5 rows)
В списке pgsql-performance по дате отправления
От: Scott Carey
Дата:
От: Greg Jaman
Дата:
FAQ