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!
On Tue, Jun 18, 2013 at 2:09 AM, 高健 <luckyjackgao@gmail.com> wrote:
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.
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.
Cheers,
Jeff
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.
2013/6/19 Jeff Janes <jeff.janes@gmail.com>
Show quoted text
On Tue, Jun 18, 2013 at 2:09 AM, 高健 <luckyjackgao@gmail.com> wrote:
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.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.Cheers,
Jeff
=?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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general