Table DDL Causing All Tables To Be Hit During Query

Howdy,

Environment:

Postgres 8.4.15
Ubuntu 10.04.4

We have multiple monthly tables inherited from a master. Sample definition:

--
-- Name: syslog_master; Type: TABLE; Schema: public; Owner: nms; Tablespace:
--

CREATE TABLE syslog_master (
ip inet,
facility character varying(10),
level character varying(10),
datetime timestamp without time zone,
program character varying(25),
msg text,
seq bigint NOT NULL
);

--
-- Name: syslog_201008; Type: TABLE; Schema: public; Owner: nms; Tablespace:
--

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';

nms=# explain analyze SELECT msg
nms-# FROM syslog
nms-# WHERE ip = '150.101.0.140'
nms-# AND msg LIKE '%218.244.147.129%'
nms-# AND datetime > NOW() - INTERVAL '10 minutes';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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)
-> Bitmap Heap Scan on syslog_master (cost=4.27..9.63 rows=1 width=32) (actual time=0.012..0.012 rows=0 loops=1)
Recheck Cond: (ip = '150.101.0.140'::inet)
Filter: ((msg ~~ '%218.244.147.129%'::text) AND (datetime > (now() - '00:10:00'::interval)))
-> Bitmap Index Scan on syslog_master_ip_idx (cost=0.00..4.27 rows=2 width=0) (actual time=0.010..0.010 rows=0 loops=1)
Index Cond: (ip = '150.101.0.140'::inet)
-> 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))
-> Index Scan using syslog_201009_datetime_idx on syslog_201009 syslog_master (cost=0.00..235.34 rows=1 width=129) (actual time=0.719..0.719 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))
-> Index Scan using syslog_201010_datetime_idx on syslog_201010 syslog_master (cost=0.00..586.48 rows=1 width=127) (actual time=0.710..0.710 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))
-> Index Scan using syslog_201011_datetime_idx on syslog_201011 syslog_master (cost=0.00..130.45 rows=1 width=128) (actual time=14.916..14.916 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))
-> Index Scan using syslog_201012_datetime_idx on syslog_201012 syslog_master (cost=0.00..56.77 rows=1 width=125) (actual time=22.792..22.792 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))
-> Index Scan using syslog_201101_datetime_idx on syslog_201101 syslog_master (cost=0.00..11.80 rows=1 width=126) (actual time=0.669..0.669 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))
-> Index Scan using syslog_201102_datetime_idx on syslog_201102 syslog_master (cost=0.00..30.49 rows=1 width=121) (actual time=0.705..0.705 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))
-> Index Scan using syslog_201103_datetime_idx on syslog_201103 syslog_master (cost=0.00..32.32 rows=1 width=123) (actual time=8.463..8.463 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))
-> Index Scan using syslog_201104_datetime_idx on syslog_201104 syslog_master (cost=0.00..262.22 rows=1 width=124) (actual time=0.794..0.794 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))
-> Index Scan using syslog_201105_datetime_idx on syslog_201105 syslog_master (cost=0.00..119.54 rows=1 width=122) (actual time=0.606..0.606 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))
-> Index Scan using syslog_201106_datetime_idx on syslog_201106 syslog_master (cost=0.00..32.49 rows=1 width=109) (actual time=16.159..16.159 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))
-> Index Scan using syslog_201107_datetime_idx on syslog_201107 syslog_master (cost=0.00..37.21 rows=1 width=118) (actual time=0.757..0.757 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))
-> Index Scan using syslog_201108_datetime_idx on syslog_201108 syslog_master (cost=0.00..467.15 rows=1 width=132) (actual time=2.050..2.050 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))
-> Index Scan using syslog_201109_datetime_idx on syslog_201109 syslog_master (cost=0.00..315.72 rows=1 width=121) (actual time=1.505..1.505 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...

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

Any ideas?

Thank you,

Samuel Stearns