Skip site navigation (1) Skip section navigation (2)

Re: CREATE INDEX and HOT - revised design

From: "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>
To: Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Bruce Momjian <bruce(at)momjian(dot)us>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Pavan Deolasee <pavan(dot)deolasee(at)enterprisedb(dot)com>
Subject: Re: CREATE INDEX and HOT - revised design
Date: 2007-03-29 11:44:58
Message-ID: 460BA6BA.7060002@phlo.org (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-hackers
Pavan Deolasee wrote:
> On 3/29/07, Florian G. Pflug <fgp(at)phlo(dot)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=5000 width=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


In response to

Responses

pgsql-hackers by date

Next:From: Michael MeskesDate: 2007-03-29 12:03:33
Subject: Re: ECPG threads test
Previous:From: Gregory StarkDate: 2007-03-29 11:37:02
Subject: Re: Patch queue concern

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group