Re: partition queries hitting all partitions even though check key is specified

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: partition queries hitting all partitions even though check key is specified
Дата
Msg-id 3465.1251904760@sss.pgh.pa.us
обсуждение исходный текст
Ответ на partition queries hitting all partitions even though check key is specified  (Kevin Kempter <kevink@consistentstate.com>)
Ответы Re: partition queries hitting all partitions even though check key is specified  (Kevin Kempter <kevink@consistentstate.com>)
Список pgsql-performance
Kevin Kempter <kevink@consistentstate.com> writes:
> I cant figure out why we're scanning all of our partitions.

The example works as expected for me:

regression=# CREATE TABLE url_hits (
    id integer NOT NULL,
    content_type_id integer,
    file_extension_id integer,
    "time" integer,
    bytes integer NOT NULL,
    path_id integer);
CREATE TABLE
regression=# create table url_hits_2011_12 (
   check (
          "time" >= extract ('epoch' from timestamp '2011-12-01
00:00:00')::int4
          and "time" <= extract ('epoch' from timestamp '2011-12-31
23:59:59')::int4
   )
) INHERITS (url_hits);
CREATE TABLE
regression=# create table url_hits_2009_08 (
   check (
          "time" >= extract ('epoch' from timestamp '2009-08-01
00:00:00')::int4
          and "time" <= extract ('epoch' from timestamp '2009-08-31
23:59:59')::int4
   )
) INHERITS (url_hits);
CREATE TABLE
regression=# explain select * from url_hits where "time" <
date_part('epoch'::text, '2009-08-12'::timestamp without time zone)::integer;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Result  (cost=0.00..82.50 rows=1401 width=24)
   ->  Append  (cost=0.00..82.50 rows=1401 width=24)
         ->  Seq Scan on url_hits  (cost=0.00..27.50 rows=467 width=24)
               Filter: ("time" < 1250049600)
         ->  Seq Scan on url_hits_2011_12 url_hits  (cost=0.00..27.50 rows=467 width=24)
               Filter: ("time" < 1250049600)
         ->  Seq Scan on url_hits_2009_08 url_hits  (cost=0.00..27.50 rows=467 width=24)
               Filter: ("time" < 1250049600)
(8 rows)

regression=# set constraint_exclusion TO 1;
SET
regression=# explain select * from url_hits where "time" <
date_part('epoch'::text, '2009-08-12'::timestamp without time zone)::integer;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Result  (cost=0.00..55.00 rows=934 width=24)
   ->  Append  (cost=0.00..55.00 rows=934 width=24)
         ->  Seq Scan on url_hits  (cost=0.00..27.50 rows=467 width=24)
               Filter: ("time" < 1250049600)
         ->  Seq Scan on url_hits_2009_08 url_hits  (cost=0.00..27.50 rows=467 width=24)
               Filter: ("time" < 1250049600)
(6 rows)


You sure you remembered those fiddly little casts everywhere?
(Frankly, declaring "time" as integer and not timestamp here strikes
me as utter lunacy.)  What PG version are you using?

            regards, tom lane

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

Предыдущее
От: Greg Jaman
Дата:
Сообщение: Re: partition queries hitting all partitions even though check key is specified
Следующее
От: Greg Stark
Дата:
Сообщение: Re: partition queries hitting all partitions even though check key is specified