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

=?UTF-8?B?6auY5YGl?= <luckyjackgao(at)gmail(dot)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