Re: CREATE INDEX and HOT - revised design

Pavan Deolasee wrote:
> On 3/29/07, Florian G. Pflug <fgp@phlo.org> wrote:
>>
>> Yes, but the non-index plan PREPARE generated will be used until the end
>> of the session, nut only until the end of the transaction.
> 
> Frankly I don't know this works, but are you sure that the plan will
> be used until the end of the session ? Even if thats the case, it can
> happen even today if we create a new index, but the existing sessions
> will use the stale plan (assuming what you said is true)

I've checked that:
************************************************************
test=# prepare myplan as select * from test where id=10000 ;
PREPARE

test=# explain execute myplan ;                         QUERY PLAN
------------------------------------------------------------ Seq Scan on test  (cost=0.00..22897.70 rows=5421 width=36)
 Filter: (id = 10000)
 
(2 rows)

!!!! Now I create an index in another session !!!!

test=# explain select * from test where id=10000 ;                              QUERY PLAN
---------------------------------------------------------------------- Bitmap Heap Scan on test  (cost=95.11..8248.45
rows=5000width=36)   Recheck Cond: (id = 10000)   ->  Bitmap Index Scan on idx  (cost=0.00..93.86 rows=5000 width=0)
    Index Cond: (id = 10000)
 
(4 rows)

test=# explain execute myplan ;                                       QUERY PLAN
------------------------------------------------------------ Seq Scan on test  (cost=0.00..22897.70 rows=5421 width=36)
 Filter: (id = 10000)
 
(2 rows)

!!! Index got used by the "select .. " but not by "execute myplan ..." !!!

test=# prepare myplan2 as select * from test where id=10000 ;
PREPARE
test=# explain execute myplan2 ;                           QUERY PLAN
----------------------------------------------------------------- Index Scan using idx on test  (cost=0.00..8.38 rows=1
width=37)  Index Cond: (id = 10000)
 
(2 rows)

!!! A newly prepared plan of course uses the index !!!

************************************************************

So yes, plans get cached until the end of the session, and
yes, 8.2 won't notice index creation either ;-)

The open question is how CVS HEAD with plan invalidation behaves.
If it replans after the index-creating transaction commits, then
basing index validity on a snapshot will break this, because upon
replay they index might not be useable, but later on it may very
well be (but that plan invalidation machinery won't realize that)

So this might not introduce a regression compared to 8.2, but to
a future 8.3 with plan invalidation...

Sorry for being so unclear in my previous emails - I had confused
myself ;-)

greetings, Florian Pflug