Re: Query plan not updated after dropped index

On Thu, Feb 18, 2016 at 11:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Victor Blomqvist <vb(at)viblo(dot)se> writes:
> > We just had a major issue on our databases, after a index was replaced a
> > user defined function didnt change its query plan to use the new index.
>
> I'm suspicious that this is some variant of the problem discussed a couple
> days ago:
>
>
> http://www.postgresql.org/message-id/flat/CAKt_ZfsvdE3WiHUjg81MT1KzOkebqz_bg=DBHf4L6rkfjtEJ_w(at)mail(dot)gmail(dot)com
>
> However, that theory requires that the index not have been immediately
> usable, which implies that it initially had some broken HOT chains,
> which really should not have happened if you were simply replacing one
> index with an identical one. (The pre-existing index should've been
> enough to ensure HOT chain consistency for its columns.)
>
> Perhaps you were doing something "cute" like replacing a single-column
> index with a multi-column one?
>

No the new index looked exactly as the old one. The index was created with
CONCURRENTLY, and we waited until it returned.

I could use the index just fine when running a query in a separate
connection, so I am not sure if it helps dropping the old index in a
transaction and run a query to verify that the new index is usable? How can
I know that the new index is usable from already open connections?

Another thing to note about this index is that it sometimes has an
unexpected zero page inside, resulting in this error: ERROR: index
"user_pictures_picture_dhash_idx" contains unexpected zero page at block
123780. But it always happens sporadically on hour read slaves, and
rerunning the query that cause it doesnt reproduce the error. I asked about
this problem before, here:
http://www.postgresql.org/message-id/flat/CAL870DVXR9fHkyEJ5sMydK4pJUPL5kWabUhSPbTQeK03gdDcqA(at)mail(dot)gmail(dot)com#CAL870DVXR9fHkyEJ5sMydK4pJUPL5kWabUhSPbTQeK03gdDcqA@mail.gmail.com

>
> regards, tom lane
>