literal vs dynamic partition constraint in plan execution

Поиск
Список
Период
Сортировка
От Luca Ferrari
Тема literal vs dynamic partition constraint in plan execution
Дата
Msg-id CAKoxK+4qyKtzdznxzG+AyOe-Yf=FFbUHRLcptVGK6hrRQD9wPA@mail.gmail.com
обсуждение исходный текст
Ответы Re: literal vs dynamic partition constraint in plan execution  (Luca Ferrari <fluca1978@gmail.com>)
Список pgsql-general
Ok, the title is a little buzz, however I've got a partitioned table
and one "leaf" has a set of checks against a timestamp field to ensure
that tuples within such table belongs to the year and month:

testdb=# \d respi.y2019m08
...
Partition of: respi.y2019 FOR VALUES IN ('8')
Check constraints:
    "y2019_mis_ora_check" CHECK (date_part('year'::text, mis_ora) =
2019::double precision)
    "y2019_mis_ora_check1" CHECK (mis_ora >= make_timestamp(2019, 1,
1, 0, 0, 0::double precision) AND mis_ora <= make_timestamp(2019, 12,
31, 23, 59, 59::double precision))
    "y2019m08_mis_ora_check" CHECK (date_part('month'::text, mis_ora)
= 8::double precision)
    "y2019m08_mis_ora_check1" CHECK (date_part('year'::text, mis_ora)
= 2019::double precision)
    "y2019m08_mis_ora_check2" CHECK (mis_ora >= make_timestamp(2019,
8, 1, 0, 0, 0::double precision) AND mis_ora <= make_timestamp(2019,
8, 31, 23, 59, 59::double precision))
    "y2019m08_mis_ora_check3" CHECK (mis_ora >= make_timestamp(2019,
8, 1, 0, 0, 0::double precision) AND mis_ora <= make_timestamp(2019,
8, 31, 23, 59, 59::double precision))

So y2019m08 accepts only tuples where 'mis_ora' has a timestamp that
is contained into the eigth month of the year.
Now if I look at the plan for this query everything works as expected
(I disabled parallel scans for better see the plan):

testdb=# explain select * from respi.root where ts >= '2019-08-28
23:35:00.007245' and mis_ora >= '2019-08-29 16:28:48.711482'   order
by ts;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=353986.27..353991.59 rows=2129 width=40)
   Sort Key: y2019m08.ts
   ->  Append  (cost=0.00..353868.58 rows=2129 width=40)
         ->  Seq Scan on y2019m08  (cost=0.00..353409.93 rows=1 width=40)
               Filter: ((ts >= '2019-08-28 23:35:00.007245'::timestamp
without time zone) AND (mis_ora >= '2019-08-29
16:28:48.711482'::timestamp without time zone))
         ->  Seq Scan on y2019m09  (cost=0.00..28.00 rows=133 width=40)
               Filter: ((ts >= '2019-08-28 23:35:00.007245'::timestamp
without time zone) AND (mis_ora >= '2019-08-29
16:28:48.711482'::timestamp without time zone))
         ->  Seq Scan on y2019m10  (cost=0.00..28.00 rows=133 width=40)
               Filter: ((ts >= '2019-08-28 23:35:00.007245'::timestamp
without time zone) AND (mis_ora >= '2019-08-29
16:28:48.711482'::timestamp without time zone))
         ->  Seq Scan on y2019m11  (cost=0.00..28.00 rows=133 width=40)
               Filter: ((ts >= '2019-08-28 23:35:00.007245'::timestamp
without time zone) AND (mis_ora >= '2019-08-29
16:28:48.711482'::timestamp without time zone))
         ->  Seq Scan on y2019m12  (cost=0.00..28.00 rows=133 width=40)
               Filter: ((ts >= '2019-08-28 23:35:00.007245'::timestamp
without time zone) AND (mis_ora >= '2019-08-29
16:28:48.711482'::timestamp without time zone))


The "as I expected" means that the system starts scanning from
y2019m08 and following (in time) tables, and does not scan previous
time tables.
This works if the mis_ora is compared against a literal timestamp, but
if I simply change it with a dynamic timestamp:

testdb=# explain select * from respi.root where ts >= '2019-08-28
23:35:00.007245' and   mis_ora >= current_timestamp   order by ts;
   QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=4654860.37..4654865.25 rows=1952 width=36)
   Sort Key: r.ts
   ->  Nested Loop  (cost=0.00..4654753.69 rows=1952 width=36)
         Join Filter: (r.sen_id = s.sen_id)
         ->  Append  (cost=0.00..4638927.56 rows=3204 width=32)
               ->  Seq Scan on y2018m01 r  (cost=0.00..31.00 rows=133 width=32)
                     Filter: ((ts >= '2019-08-28
23:35:00.007245'::timestamp without time zone) AND (mis_ora >=
CURRENT_TIMESTAMP))
               ->  Seq Scan on y2018m02 r_1  (cost=0.00..31.00
rows=133 width=32)
                     Filter: ((ts >= '2019-08-28
23:35:00.007245'::timestamp without time zone) AND (mis_ora >=
CURRENT_TIMESTAMP))
               ->  Seq Scan on y2018m03 r_2  (cost=0.00..31.00
rows=133 width=32)
                     Filter: ((ts >= '2019-08-28
23:35:00.007245'::timestamp without time zone) AND (mis_ora >=
CURRENT_TIMESTAMP))
               ->  Seq Scan on y2018m04 r_3  (cost=0.00..31.00
rows=133 width=32)
                     Filter: ((ts >= '2019-08-28
23:35:00.007245'::timestamp without time zone) AND (mis_ora >=
CURRENT_TIMESTAMP))

also the tables for the past year are scanned. Moreover, the planner
thinks I will get 133 rows out of, for instance, y2018m01 which is
impossible.
So, do I have defined the constraint on each table in a wrong manner?


testdb=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

testdb=# show constraint_exclusion ;
 constraint_exclusion
----------------------
 partition



Thanks,
Luca



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

Предыдущее
От: Gary Cowell
Дата:
Сообщение: Re: Selecting rows having substring in a column
Следующее
От: Rich Shepard
Дата:
Сообщение: Re: Selecting rows having substring in a column [RESOLVED]