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

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(dot)janes(at)gmail(dot)com>

> On Tue, Jun 18, 2013 at 2:09 AM, 高健 <luckyjackgao(at)gmail(dot)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
>