Why won't Postgres filter my date range partitions?

Question

I have a table that uses declarative partitioning (w00t!) to partition tables by date range - one year in my case.

When I query against the table - SELECT * FROM tbl WHERE date > date '2016-01-01', it works exactly as intended; only tables containing newer data are scanned.

When I specify a date using variables or functions (CURRENT_DATE, NOW(), etc), EXPLAIN says it scans every partition.

Things that work as intended:

SELECT * FROM tbl WHERE date > date '2016-01-01'
--
SELECT * FROM tbl WHERE date > '2016-01-01'::date

Things that scan all partitions unnecessarily:

SELECT * FROM tbl WHERE date > CURRENT_DATE
--
SELECT * FROM tbl WHERE date > NOW()
--
SELECT * FROM tbl WHERE date > (NOW() - 365)::date
--
SELECT * FROM tbl WHERE date > (SELECT (NOW()::date - 365)::date AS d)
-- Even CTEs are no dice:
WITH a AS (SELECT CURRENT_DATE AS d)
SELECT * FROM tbl, a WHERE date > a.d
-- Same with JOINs
SELECT w.*
FROM (CURRENT_DATE - 365 as d) a
LEFT JOIN wtf w ON w.date > a.d

..etc

I get the same behavior with other comparison operators - =, <, etc.

The docs say I don't need an idx on the field (which I don't anyways). I added one just in case and it did not help.

Why is this happening, and what can I do to stop it (preferably without adding complication to a simple query)?

Please Edit your question and add the create table statements for the tables in question (including all indexes) and the execution plan generated using explain (analyze, verbose). Formatted text please, no screen shots — user330315
– user330315, Commented Oct 16, 2017 at 13:45
That's because planner doesn't know output of now() till executing. — JustMe
– JustMe, Commented Oct 16, 2017 at 13:46
Thanks @JustMe - would you post this as an answer so I can accept it and remove the unnecessary SQL and output of EXPLAIN so this can help other people as well? — onwsk8r
– onwsk8r, Commented Oct 16, 2017 at 14:05

onwsk8r · Accepted Answer · 2017-10-16 16:43:28Z

Thanks to JustMe for answering this- see the comments on the OP.

The issue lies with when NOW() and CURRENT_TIMESTAMP are evaluated in relation to FROM; it's the same issue you see when you try to filter in a join ala WHERE join_table.a > from_table.b.

Supposing today is Jan 1, 1970, these queries

SELECT * FROM my_stuff WHERE date > NOW()::date;
--
SELECT * FROM my_stuff WHERE date > '1970-01-01'::date;

will necessarily produce an identical resultset but will not necessarily be evaluated in an identical way.

That's why this is happening and unfortunately, there doesn't seem to be a simple way to stop it. A function seems to be the best-ish option:

CREATE OR REPLACE FUNCTION myfunc()
    RETURNS setof tbl
    LANGUAGE 'plpgsql'
AS $$
DECLARE
    n date := CURRENT_DATE - 365;
BEGIN
    RETURN query EXECUTE $a$
        SELECT * FROM tbl
        WHERE date > $1;
    $a$ using n;
END $$;

You can test this by changing RETURNS setof tbl to RETURNS setof text and SELECT... to EXPLAIN SELECT...

Collectives™ on Stack Overflow

Why won't Postgres filter my date range partitions?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related