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
