Postgres Pro
Downloads
Home   >   mailing lists

Table DDL Causing All Tables To Be Hit During Query - Mailing list pgsql-admin

From Samuel Stearns
Subject Table DDL Causing All Tables To Be Hit During Query
Date
Msg-id CBAC86BE623FDB4E8B6225471691724291F15417@EXCHMBX-ADL6-01.staff.internode.com.au
Whole thread Raw
Responses Re: Table DDL Causing All Tables To Be Hit During Query
Re: Table DDL Causing All Tables To Be Hit During Query
List pgsql-admin
Tree view

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

 

pgsql-admin by date:

Previous
From: Armand du Plessis
Date:
Subject: Re: pg_basebackup error
Next
From: Rosser Schwarz
Date:
Subject: Re: Table DDL Causing All Tables To Be Hit During Query