what does "initplan" operation in explain output mean? - Mailing list 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)