Query efficiency of timestamp-partitioned tables using CURRENT_TIMESTAMP

Question

Given the following table partitioning under PostgreSQL 9.0.3:

CREATE TABLE records (
  ts TIMESTAMP,
  ...
);

CREATE TABLE records_2010 (
  CHECK (ts >= '2010-01-01 00:00:00' AND ts < '2011-01-01 00:00:00')
) INHERITS (records);

CREATE TABLE records_2011 (
  CHECK (ts >= '2011-01-01 00:00:00' AND ts < '2012-01-01 00:00:00')
) INHERITS (records);

I expected the following SELECT queries to have the same EXPLAINed plan, consulting only "records" and "records_2011", but they differ:

BEGIN;
-- Assume CURRENT_TIMESTAMP is 9 a.m. on 5 March 2011
SELECT * FROM records WHERE ts >= '2011-03-05 09:00:00'; -- scans 2 tables
SELECT * FROM records WHERE ts >= CURRENT_TIMESTAMP;     -- scans all 3 tables
COMMIT;

Given that CURRENT_TIMESTAMP returns a constant value for the duration of its enclosing transactions, why doesn't the query with CURRENT_TIMESTAMP take advantage of Postgres' partitioning and only scan two tables?

UPDATE:

This isn't possible right now, but it is recognized as an area to improve. PostgreSQL 9.1 may address this behavior in the query executor.

Andomar · Accepted Answer · 2011-03-06 20:39:36Z

5

If you run a query for the first time, PostgreSQL determines a query plan. This is an expensive operation and the result is cached. So the query has to work for future executions as well.

Your first query will never need records_2010, regardless of when you run it.

But the second query uses a variable CURRENT_TIMESTAMP. The optimizer does not know that time can only increase, and generates a plan that will work for any value of ts. That means it has to look in all three tables.

answered Mar 6, 2011 at 20:39

Andomar

239k55 gold badges387 silver badges412 bronze badges

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

4 Comments

pilcrow Over a year ago

yes, but why doesn't the query engine substitute in constant values for "transactionally IMMUTABLE" functions like CURRENT_TIMESTAMP, and only thereafter turn to query planning? It doesn't matter if CURRENT_TIMESTAMP increases or not -- every call to inside one transaction returns the same, independent value. Is there something about CURRENT_TIMESTAMP that makes this infeasible? (IMHO it'd always be preferable to sub in constant values before query planning for statements which might benefit from partitioning's "constraint exclusion" mechanism.)

Andomar Over a year ago

@pilcrow: The query plan will be reused for future transactions as well, maybe days or weeks later. The reason that values are not substituted is that query planning is expensive. Creating a plan for every version of select * from orders where id = :orderId would kill performance.

pilcrow Over a year ago

on this data set, one partition-aware plan per query is much faster than re-using the same partition-unaware plan. (And, just to be clear, we're talking about "constant-folding" a function call, rather than :placeholders per se.) In any case, it looks like 9.1 will accomplish what I want

Le Droid Over a year ago

Sadly PG9.2 still has the same problem. It can't use partitions efficiently as soon as its not an explicit constant. Like if you use in a trigger function with SELECT INTO myResult * FROM myPartitionnedTable WHERE partitionKeyField = NEW.partitionKeyField... it scans all the partitions :-( It would be my #1 wish for PG9.4 (9.3 doesn't seem to address this) if the query plan could be more partition aware.

Collectives™ on Stack Overflow

Query efficiency of timestamp-partitioned tables using CURRENT_TIMESTAMP

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related