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