PostgreSQL: Scan relevant partitions only

Question

I tried to analyze why a certain query takes quite a long time. It runs on a partitioned PostgreSQL (v9.1) table. It is partitioned monthly. The rule is based on the a column which holds an integer representation of the date (so in example 20130801).

If I write a query like this one:

EXPLAIN     SELECT DISTINCT (user_id)
FROM users
WHERE
    date_tk >= 20130801

Only the relevant partitions get selected. But, when I run something like this, it scans all the partitions:

EXPLAIN     SELECT DISTINCT (user_id)
FROM users 
WHERE
    date_tk >= TO_CHAR(CURRENT_DATE - '30 days'::INTERVAL, 'yyyyMMdd')::INT

Now I casted the constraint date to int, so it seems to be ok I would assume, but unfortunately it isn't. Does somebody have an idea on how to improve this query so that only the relevant partitions get scanned?

Thanks, Diddy

here is some explanations and solution stackoverflow.com/questions/5973030/… — alexius
– alexius, Commented Oct 18, 2013 at 12:15
Thanks a lot! This explained it: "At the end of the day it looks like you're trying to index a "YYYY-MM-DD" representation of create_time. Why not just INDEX create_time? The problem is to_char() is MUTABLE because the locale environment variable could change, which changes the output of to_char()." — Diddy
– Diddy, Commented Oct 18, 2013 at 13:49

Denis de Bernardy · Accepted Answer · 2013-10-18 10:57:42Z

1

The expression you're using isn't considered immutable by Postgres (for the same reason now() isn't), so Postgres won't use it to consider when optimizing the lookup. Toss in an extra where clause that is constant and far enough in the past.

Alternatively, wouldn't using time stamps directly when partitioning avoid the issue altogether?

answered Oct 18, 2013 at 10:57

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Diddy Over a year ago

Thanks for your help! I can supply constants as parameters to the query at runtime, so at least I have a workaround.

Collectives™ on Stack Overflow

PostgreSQL: Scan relevant partitions only

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related