literal vs dynamic partition constraint in plan execution

From: Luca Ferrari <fluca1978(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: literal vs dynamic partition constraint in plan execution
Date: 2019-08-29 14:45:27
Message-ID: CAKoxK+4qyKtzdznxzG+AyOe-Yf=FFbUHRLcptVGK6hrRQD9wPA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rich Shepard 2019-08-29 14:46:45 Re: Selecting rows having substring in a column [RESOLVED]
Previous Message Gary Cowell 2019-08-29 14:28:07 Re: Selecting rows having substring in a column