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)
