I want to make an example of using parameterized path - Mailing list pgsql-general

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!