pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedwhat does "initplan" operation in explain output mean?
5+ messages / 2 participants
[nested] [flat]
* what does "initplan" operation in explain output mean?
@ 2010-07-31 02:27 Mark Rostron <mrostron@ql2.com>
0 siblings, 0 replies; 5+ messages in thread
From: Mark Rostron @ 2010-07-31 02:27 UTC (permalink / raw)
To: pgsql-performance
We are running 8.3.10 64bit.
Compare the plans below.
They all do the same thing and delete from a table named work_active (about 500rows), which is a subset of work_unit (about 50m rows).
I want to introduce range-partitions on work_unit.id column (serial pk), and I want constraint exclusion to be used.
Stmt_3 is the plan currently in use.
Stmt_4 and stmt_5 compare explain plans of two variants of the stmt (no partitions yet):
- Limit the sub-query using constants (derived from a prior query of min() and max() against work_active), (ref stmt_4 below) or
- Try and do something cute and do a subquery using min() and max() (ref stmt_5 below).
My questions are:
- What does the "initplan" operation do? ( I can take a guess, but could someone give me some details, cos the docn about it is pretty sparse).
- Will this enable constraint exclusion on the work_unit table if we introduce partitioning?
Thanks in adv for any help you can give me.
Mr
caesius=# \i stmt_3.sql
explain
DELETE FROM work_active wa
WHERE EXISTS (
SELECT 1
FROM work_unit wu
, run r
WHERE wu.id = wa.wu_id
AND wu.run_id = r.id
AND (( (wu.status not in (2,3)) OR (wu.stop_time is not null)) OR (r.status > 2) )
LIMIT 1
);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on work_active wa (cost=0.00..23078.82 rows=370 width=6)
Filter: (subplan)
SubPlan
-> Limit (cost=0.00..30.53 rows=1 width=0)
-> Nested Loop (cost=0.00..30.53 rows=1 width=0)
Join Filter: ((wu.status <> ALL ('{2,3}'::integer[])) OR (wu.stop_time IS NOT NULL) OR (r.status > 2))
-> Index Scan using tmp_work_unit_pkey on work_unit wu (cost=0.00..19.61 rows=1 width=16)
Index Cond: (id = $0)
-> Index Scan using run_pkey on run r (cost=0.00..10.91 rows=1 width=8)
Index Cond: (r.id = wu.run_id)
(10 rows)
caesius=# \i stmt_4.sql
explain
DELETE FROM work_active wa
where exists (
SELECT 1
FROM work_unit wu
, run r
WHERE wu.id = wa.wu_id
AND wu.id between 1000000 and 1100000
AND wu.run_id = r.id
AND (( (wu.status not in(2,3) ) OR (wu.stop_time is not null)) OR (r.status > 2) )
LIMIT 1
);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on work_active wa (cost=0.00..22624.37 rows=362 width=6)
Filter: (subplan)
SubPlan
-> Limit (cost=0.00..30.54 rows=1 width=0)
-> Nested Loop (cost=0.00..30.54 rows=1 width=0)
Join Filter: ((wu.status <> ALL ('{2,3}'::integer[])) OR (wu.stop_time IS NOT NULL) OR (r.status > 2))
-> Index Scan using tmp_work_unit_pkey on work_unit wu (cost=0.00..19.61 rows=1 width=16)
Index Cond: ((id >= 1000000) AND (id <= 1100000) AND (id = $0))
-> Index Scan using run_pkey on run r (cost=0.00..10.91 rows=1 width=8)
Index Cond: (r.id = wu.run_id)
(10 rows)
caesius=# \i stmt_5.sql
explain
DELETE FROM work_active wa
where exists (
SELECT 1
FROM work_unit wu
, run r
WHERE wu.id = wa.wu_id
AND wu.id between (select min(wu_id) from work_active limit 1) and (select max(wu_id) from work_active limit 1)
AND wu.run_id = r.id
AND (( (wu.status not in(2,3) ) OR (wu.stop_time is not null)) OR (r.status > 2) )
LIMIT 1
);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Seq Scan on work_active wa (cost=0.00..35071.47 rows=370 width=6)
Filter: (subplan)
SubPlan
-> Limit (cost=16.22..46.76 rows=1 width=0)
InitPlan
-> Limit (cost=8.10..8.11 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..8.10 rows=1 width=4)
-> Index Scan using work_active_pkey on work_active (cost=0.00..5987.09 rows=739 width=4)
Filter: (wu_id IS NOT NULL)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Limit (cost=8.10..8.11 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..8.10 rows=1 width=4)
-> Index Scan Backward using work_active_pkey on work_active (cost=0.00..5987.09 rows=739 width=4)
Filter: (wu_id IS NOT NULL)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Nested Loop (cost=0.00..30.54 rows=1 width=0)
Join Filter: ((wu.status <> ALL ('{2,3}'::integer[])) OR (wu.stop_time IS NOT NULL) OR (r.status > 2))
-> Index Scan using tmp_work_unit_pkey on work_unit wu (cost=0.00..19.61 rows=1 width=16)
Index Cond: ((id >= $1) AND (id <= $3) AND (id = $4))
-> Index Scan using run_pkey on run r (cost=0.00..10.91 rows=1 width=8)
Index Cond: (r.id = wu.run_id)
(23 rows)
^ permalink raw reply [nested|flat] 5+ messages in thread
* what does "initplan" operation in explain output mean?
@ 2010-08-01 06:36 Mark Rostron <mrostron@ql2.com>
2010-08-01 14:08 ` Re: what does "initplan" operation in explain output mean? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Mark Rostron @ 2010-08-01 06:36 UTC (permalink / raw)
To: pgsql-performance
We are running 8.3.10 64bit.
This message is a request for information about the "initplan" operation in explain plan.
I want to know if I can take advantage of it, and use it to initialize query-bounds for the purpose of enforcing constraint exclusion on a table which has been range-partitioned on a serial-id column.
Compare the plans below.
They all do the same thing and delete from a table named work_active (about 500rows), which is a subset of work_unit (about 50m rows).
Stmt_3 is the plan currently in use.
Stmt_4 and stmt_5 ilustrate explain plans of two variants of stmt_3 (no partitions yet):
- Limit the sub-query using constants (derived from a prior query min() and max() against work_active), (ref stmt_4 below) or
- Try and do something cute and do a subquery using min() and max() (ref stmt_5 below).
My questions are:
- What does the "initplan" operation do? ( I can take a guess, but could someone give me some details, cos the docn about it is pretty sparse).
- Will this enable constraint exclusion on the work_unit table if we introduce partitioning?
Thanks in adv for any help you can give me.
Mr
caesius=# \i stmt_3.sql
explain
DELETE FROM work_active wa
WHERE EXISTS (
SELECT 1
FROM work_unit wu
, run r
WHERE wu.id = wa.wu_id
AND wu.run_id = r.id
AND (( (wu.status not in (2,3)) OR (wu.stop_time is not null)) OR (r.status > 2) )
LIMIT 1
);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on work_active wa (cost=0.00..23078.82 rows=370 width=6)
Filter: (subplan)
SubPlan
-> Limit (cost=0.00..30.53 rows=1 width=0)
-> Nested Loop (cost=0.00..30.53 rows=1 width=0)
Join Filter: ((wu.status <> ALL ('{2,3}'::integer[])) OR (wu.stop_time IS NOT NULL) OR (r.status > 2))
-> Index Scan using tmp_work_unit_pkey on work_unit wu (cost=0.00..19.61 rows=1 width=16)
Index Cond: (id = $0)
-> Index Scan using run_pkey on run r (cost=0.00..10.91 rows=1 width=8)
Index Cond: (r.id = wu.run_id)
(10 rows)
caesius=# \i stmt_4.sql
explain
DELETE FROM work_active wa
where exists (
SELECT 1
FROM work_unit wu
, run r
WHERE wu.id = wa.wu_id
AND wu.id between 1000000 and 1100000
AND wu.run_id = r.id
AND (( (wu.status not in(2,3) ) OR (wu.stop_time is not null)) OR (r.status > 2) )
LIMIT 1
);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on work_active wa (cost=0.00..22624.37 rows=362 width=6)
Filter: (subplan)
SubPlan
-> Limit (cost=0.00..30.54 rows=1 width=0)
-> Nested Loop (cost=0.00..30.54 rows=1 width=0)
Join Filter: ((wu.status <> ALL ('{2,3}'::integer[])) OR (wu.stop_time IS NOT NULL) OR (r.status > 2))
-> Index Scan using tmp_work_unit_pkey on work_unit wu (cost=0.00..19.61 rows=1 width=16)
Index Cond: ((id >= 1000000) AND (id <= 1100000) AND (id = $0))
-> Index Scan using run_pkey on run r (cost=0.00..10.91 rows=1 width=8)
Index Cond: (r.id = wu.run_id)
(10 rows)
caesius=# \i stmt_5.sql
explain
DELETE FROM work_active wa
where exists (
SELECT 1
FROM work_unit wu
, run r
WHERE wu.id = wa.wu_id
AND wu.id between (select min(wu_id) from work_active limit 1) and (select max(wu_id) from work_active limit 1)
AND wu.run_id = r.id
AND (( (wu.status not in(2,3) ) OR (wu.stop_time is not null)) OR (r.status > 2) )
LIMIT 1
);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Seq Scan on work_active wa (cost=0.00..35071.47 rows=370 width=6)
Filter: (subplan)
SubPlan
-> Limit (cost=16.22..46.76 rows=1 width=0)
InitPlan
-> Limit (cost=8.10..8.11 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..8.10 rows=1 width=4)
-> Index Scan using work_active_pkey on work_active (cost=0.00..5987.09 rows=739 width=4)
Filter: (wu_id IS NOT NULL)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Limit (cost=8.10..8.11 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..8.10 rows=1 width=4)
-> Index Scan Backward using work_active_pkey on work_active (cost=0.00..5987.09 rows=739 width=4)
Filter: (wu_id IS NOT NULL)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Nested Loop (cost=0.00..30.54 rows=1 width=0)
Join Filter: ((wu.status <> ALL ('{2,3}'::integer[])) OR (wu.stop_time IS NOT NULL) OR (r.status > 2))
-> Index Scan using tmp_work_unit_pkey on work_unit wu (cost=0.00..19.61 rows=1 width=16)
Index Cond: ((id >= $1) AND (id <= $3) AND (id = $4))
-> Index Scan using run_pkey on run r (cost=0.00..10.91 rows=1 width=8)
Index Cond: (r.id = wu.run_id)
(23 rows)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: what does "initplan" operation in explain output mean?
2010-08-01 06:36 what does "initplan" operation in explain output mean? Mark Rostron <mrostron@ql2.com>
@ 2010-08-01 14:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-08-02 16:42 ` Re: what does "initplan" operation in explain output mean? Mark Rostron <mrostron@ql2.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2010-08-01 14:08 UTC (permalink / raw)
To: Mark Rostron <mrostron@ql2.com>; +Cc: pgsql-performance
Mark Rostron <mrostron@ql2.com> writes:
> This message is a request for information about the "initplan" operation in explain plan.
An initplan is a sub-SELECT that only needs to be executed once because it
has no dependency on the immediately surrounding query level. The cases
you show here are from sub-SELECTs like this:
(select min(wu_id) from work_active limit 1)
which yields a value that's independent of anything in the outer query.
If there were an outer reference in there, you'd get a SubPlan instead,
because the subquery would need to be done over again for each row of
the outer query.
BTW, adding LIMIT 1 to an aggregate query is pretty pointless.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: what does "initplan" operation in explain output mean?
2010-08-01 06:36 what does "initplan" operation in explain output mean? Mark Rostron <mrostron@ql2.com>
2010-08-01 14:08 ` Re: what does "initplan" operation in explain output mean? Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-08-02 16:42 ` Mark Rostron <mrostron@ql2.com>
2010-08-02 17:29 ` Re: what does "initplan" operation in explain output mean? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Mark Rostron @ 2010-08-02 16:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Thanks.
So am I right in assuming that the aggregate sub-query ( against work_active ) results will not assist with constraint exclusion in the sub-query against work_unit (if we introduce range partitions on this table)?
Mr
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Sunday, August 01, 2010 7:08 AM
To: Mark Rostron
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] what does "initplan" operation in explain output mean?
Mark Rostron <mrostron@ql2.com> writes:
> This message is a request for information about the "initplan" operation in explain plan.
An initplan is a sub-SELECT that only needs to be executed once because it has no dependency on the immediately surrounding query level. The cases you show here are from sub-SELECTs like this:
(select min(wu_id) from work_active limit 1)
which yields a value that's independent of anything in the outer query.
If there were an outer reference in there, you'd get a SubPlan instead, because the subquery would need to be done over again for each row of the outer query.
BTW, adding LIMIT 1 to an aggregate query is pretty pointless.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: what does "initplan" operation in explain output mean?
2010-08-01 06:36 what does "initplan" operation in explain output mean? Mark Rostron <mrostron@ql2.com>
2010-08-01 14:08 ` Re: what does "initplan" operation in explain output mean? Tom Lane <tgl@sss.pgh.pa.us>
2010-08-02 16:42 ` Re: what does "initplan" operation in explain output mean? Mark Rostron <mrostron@ql2.com>
@ 2010-08-02 17:29 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2010-08-02 17:29 UTC (permalink / raw)
To: Mark Rostron <mrostron@ql2.com>; +Cc: pgsql-performance
Mark Rostron <mrostron@ql2.com> writes:
> So am I right in assuming that the aggregate sub-query ( against work_active ) results will not assist with constraint exclusion in the sub-query against work_unit (if we introduce range partitions on this table)?
Dunno. You didn't actually show what you were hoping would work, or
work differently as the case may be.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-08-02 17:29 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-31 02:27 what does "initplan" operation in explain output mean? Mark Rostron <mrostron@ql2.com>
2010-08-01 06:36 what does "initplan" operation in explain output mean? Mark Rostron <mrostron@ql2.com>
2010-08-01 14:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-08-02 16:42 ` Mark Rostron <mrostron@ql2.com>
2010-08-02 17:29 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox