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