CODEWITHSUNDEEP
postgresqldatabase-partitioning
onwsk8r
onwsk8r

Reputation: 403

Why won't Postgres filter my date range partitions?

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)?

Upvotes: 3

Views: 1370

Answers (1)

onwsk8r
onwsk8r

Reputation: 403

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...

Upvotes: 3

Related Questions