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

[高健 <luckyjackgao@xxxxxxxxx>]

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@xxxxxxxxx>

On Tue, Jun 18, 2013 at 2:09 AM, 高健 <luckyjackgao@xxxxxxxxx> 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