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

=?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