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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
