Thread: I want to make an example of using parameterized path

Hello:

I  have some questions about parameterized path.

I have heard that it is a new feature in PG9.2.

I digged for information of parameterized path, but found few(maybe my method is not right).

My FIRST question is:

What is "parameterized path " for?

Is the following a correct example of activating "parameterized path" being created?

I found an example by googling. I tried it:

--making data:

postgres=# create table tst01(id integer);

CREATE TABLE

postgres=#

postgres=# insert into tst01 values(generate_series(1,100000));

INSERT 0 100000

postgres=#

postgres=# create index idx_tst01_id on tst01(id);

CREATE INDEX

postgres=#

--runing:

postgres=# prepare s(int) as select * from tst01 t where id < $1;

PREPARE

postgres=# explain execute s(2);

                                   QUERY PLAN                                    

---------------------------------------------------------------------------------

 Index Only Scan using idx_tst01_id on tst01 t  (cost=0.00..8.38 rows=1 width=4)

   Index Cond: (id < 2)

(2 rows)

postgres=# explain execute s(100000);

                          QUERY PLAN                           

---------------------------------------------------------------

 Seq Scan on tst01 t  (cost=0.00..1693.00 rows=100000 width=4)

   Filter: (id < 100000)

(2 rows)

postgres=# 

When I just send sql of  " select * from tst01 t where id <2" , it will also produce index only scan plan.

When I just send sql of  " select * from tst01 t where id < 100000", it will also produce seq scan plan.

So I think that  the above example can not show that "parameterized path" has been created.

Maybe:

"parameterized path" is special method to do something for  a parse tree's plan in ahead I think,

In order to  improve  prepared statement's planning and executing speed more.

Is this understanding right?

My SECOND question is:

For the above example I used,

I found that as if "parameterized path" is not created.

For my above example,

I can find calling relationship of the following:

PostgresMainàexec_simple_queryàpg_plan_queriesàpg_plan_queryàplanneràstandard_plannerà

àsubquery_planneràgrouping_planneràquery_planneràmake_one_relàset_base_rel_pathlistsà

àset_rel_pathlistàset_plain_rel_pathlist

The set_plain_rel_pathlist calls  create_seqscan_path  via  add_path function's parameter.

Then In create_seqscan_path function , get_baserel_parampathlist function returned  null.

As following:

pathnode->param_info = get_baserel_parampathinfo(root, rel,required_outer);

So I got no param_info .  Does that mean :  parameteried path is not created ?

If so, Is there any option to let the parameterized path being created? And how to observe it?

Thanks!

Thank you Jeff

I tried on PostgreSQL 9.1.0, and found the running result is:

postgres=# explain execute s(2);

                                   QUERY PLAN                                  

--------------------------------------------------------------------------------

-

 Bitmap Heap Scan on tst01 t  (cost=626.59..1486.25 rows=33333 width=4)

   Recheck Cond: (id < $1)

   ->  Bitmap Index Scan on idx_tst01_id  (cost=0.00..618.26 rows=33333 width=0)

         Index Cond: (id < $1)

(4 rows)

postgres=# explain execute s(10000);

                                   QUERY PLAN                                  

--------------------------------------------------------------------------------

-

 Bitmap Heap Scan on tst01 t  (cost=626.59..1486.25 rows=33333 width=4)

   Recheck Cond: (id < $1)

   ->  Bitmap Index Scan on idx_tst01_id  (cost=0.00..618.26 rows=33333 width=0)

         Index Cond: (id < $1)

(4 rows)

postgres=#

I want to know some internal about the "parameterized path".

I guess that  Before PG9.2,

     After I called  prepare command, the path and plan is already created and done.

     The plan is based on average estimation of all kinds of paths.

      So even when I put different parameter, it just  execute the same finished plan.

=?UTF-8?B?6auY5YGl?= <luckyjackgao@gmail.com> writes:
>>> So I think that  the above example can not show that "parameterized path"
>>> has been created.

>> But if you try the PREPAREd sets in versions before 9.2, you will find
>> they use the same plan as each other.  Allowing them to differ based on the
>> parameter they are executed with, just like the non-PREPARE ones differ, is
>> what parameterized paths is all about.

No, actually, parameterized paths have nothing to do with parameterized
queries.  Here's a trivial example:

regression=# create table sml as select generate_series(1,1000000,100000) as x;
SELECT 10
regression=# analyze sml;
ANALYZE
regression=# create table big as select generate_series(1,1000000) as y;
SELECT 1000000
regression=# alter table big add primary key(y);
ALTER TABLE
regression=# analyze big;
ANALYZE
regression=# explain select * from sml, big where x=y;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop  (cost=0.42..85.65 rows=10 width=8)
   ->  Seq Scan on sml  (cost=0.00..1.10 rows=10 width=4)
   ->  Index Only Scan using big_pkey on big  (cost=0.42..8.45 rows=1 width=4)
         Index Cond: (y = sml.x)
(4 rows)

The indexscan on "big" is a parameterized path (or was when it was still
inside the planner, anyway).  It's parameterized by "sml.x", which is a
value that is not available from the "big" table so it has to be passed
in from the current outer row of a nestloop join.

Now, pre-9.2 PG versions were perfectly capable of generating plans that
looked just like that one, but the planner's method for doing so was a lot
more ad-hoc back then.  The main practical benefit that we got from the
parameterized-path rewrite is that the planner can now generate plans
that require pushing an outer-row value down through more than one level
of join.  For instance, consider this rather artificial example:

regression=# explain select * from sml left join (sml s2 join big on s2.x <= y) on big.y = sml.x;
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.42..98.08 rows=33 width=12)
   ->  Seq Scan on sml  (cost=0.00..1.10 rows=10 width=4)
   ->  Nested Loop  (cost=0.42..9.67 rows=3 width=8)
         Join Filter: (s2.x <= big.y)
         ->  Index Only Scan using big_pkey on big  (cost=0.42..8.44 rows=1 width=4)
               Index Cond: (y = sml.x)
         ->  Seq Scan on sml s2  (cost=0.00..1.10 rows=10 width=4)
(7 rows)

The joins have to be done in that order because the leftjoin and inner
join don't commute.  So "sml.x" is being passed down through the inner
nestloop join.  Pre-9.2 could not have found that plan, and would have
had to do something involving a full-table scan of "big".

            regards, tom lane