Re: Table DDL Causing All Tables To Be Hit During Query

Thanks, Rosser and Albe.

Constraint_exclusion is enabled. I'll look at using a constant.

Sam

-----Original Message-----
From: Albe Laurenz [mailto:laurenz(dot)albe(at)wien(dot)gv(dot)at]
Sent: Tuesday, 16 April 2013 5:13 PM
To: Samuel Stearns; pgsql-admin(at)postgresql(dot)org
Subject: RE: Table DDL Causing All Tables To Be Hit During Query

Samuel Stearns wrote:
> Environment:
> Postgres 8.4.15
> Ubuntu 10.04.4

> We have multiple monthly tables inherited from a master. Sample definition:
>
> CREATE TABLE syslog_master (
[...]
> );
>
> CREATE TABLE syslog_201008 (CONSTRAINT syslog_201008_datetime_check
> CHECK (((datetime >= '2010-08-
> 01'::date) AND (datetime < '2010-09-01'::date)))
> )
> INHERITS (syslog_master);

> We have a query that hits all tables when it should be only looking at the last 10 minutes:
>
> SELECT msg
> FROM syslog
> WHERE ip = '150.101.0.140'
> AND msg LIKE '%218.244.147.129%'
> AND datetime > NOW() - INTERVAL '10 minutes';
>
>
> Result (cost=4.27..5705.32 rows=35 width=117) (actual time=304.528..304.528 rows=0 loops=1)
> -> Append (cost=4.27..5705.32 rows=35 width=117) (actual
> time=304.528..304.528 rows=0 loops=1)
[...]
> -> Index Scan using syslog_201008_datetime_idx on
> syslog_201008 syslog_master
> (cost=0.00..39.13 rows=1 width=122) (actual time=111.534..111.534 rows=0 loops=1)
> Index Cond: (datetime > (now() - '00:10:00'::interval))
> Filter: ((msg ~~ '%218.244.147.129%'::text) AND (ip =
> '150.101.0.140'::inet))
[and so on for all partitions]

> We have tried dropping the constrainst and re-creating casting the
> check to timestamp rather than date but no change.

The problem is that the function now() is not declared as IMMUTABLE, but as STABLE, which is correct (it does not return a constant value).
So it cannot be evaluated at query planning time, and consequently it cannot be used to prune partitions (which happens at planning time).

You'd have to use a constant instead of "NOW() - INTERVAL '10 minutes'"
if you want partition pruning to happen.

Yours,
Laurenz Albe