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