Home > mailing lists

Thread: Table DDL Causing All Tables To Be Hit During Query

Table DDL Causing All Tables To Be Hit During Query

From

Samuel Stearns

Date:

16 April 2013, 08:49:18

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

Re: Table DDL Causing All Tables To Be Hit During Query

From

Rosser Schwarz

Date:

16 April 2013, 10:26:19

> We have tried dropping the constrainst and re-creating casting the

> check to timestamp rather than date but no change.

What is your constraint_exclusion setting?

If it's not enabled, try enabling it temporarily with "SET constraint_exclusion = on" and re-run your query.

rls

--

:wq

Re: Table DDL Causing All Tables To Be Hit During Query

From

Albe Laurenz

Date:

16 April 2013, 10:43:27

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

Re: Table DDL Causing All Tables To Be Hit During Query

From

Samuel Stearns

Date:

17 April 2013, 00:55:52

Thanks, Rosser and Albe.

Constraint_exclusion is enabled.  I'll look at using a constant.

Sam


-----Original Message-----
From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at]
Sent: Tuesday, 16 April 2013 5:13 PM
To: Samuel Stearns; pgsql-admin@postgresql.org
Subject: RE: Table DDL Causing All Tables To Be Hit During Query

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
returna constant value). 
So it cannot be evaluated at query planning time, and consequently it cannot be used to prune partitions (which happens
atplanning time). 

You'd have to use a constant instead of "NOW() - INTERVAL '10 minutes'"
if you want partition pruning to happen.

Yours,
Laurenz Albe