Re: Query plan not updated after dropped index

The end goal is to get rid of index bloat. If there is a better way to
handle this Im all ears!

/Victor

On Thu, Feb 18, 2016 at 5:21 PM, Oleg Bartunov <obartunov(at)gmail(dot)com> wrote:

>
>
> On Thu, Feb 18, 2016 at 11:17 AM, Victor Blomqvist <vb(at)viblo(dot)se> wrote:
>
>> Hello!
>>
>> 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. At
>> least this is our theory, since the function in question became much slower
>> and as a result brought our system to a halt.
>>
>> Basically it went:
>> 1. create new index (a simple btree on a bigint column index)
>> 2. drop old index
>> 3. rename new index to old index name
>>
>
> why do you did this !?
>
>
>> 3. analyze table
>>
>> After these steps normally our functions will update their plans and use
>> the new index just fine. However this time the function (only one function
>> use this particular index) seemed to take forever to complete. This is a
>> 40GB table so querying for something not indexed would take a long time.
>> Therefor my suspicion is that the function didnt start to use the new index.
>>
>> Adding to the strangeness is that if I ran the function manually it was
>> fast, only when called from our application through pg_bouncer it was slow.
>> I should also say that the function is only used on our 3 read slaves setup
>> to our database.
>>
>> Things we tried to fix this:
>> 1. Analyze table
>> 2. Restart our application
>> 3. Recreate the function
>> 4. Kill the slow running queries with pg_cancel_backend()
>>
>> These things did not help.
>>
>> Instead what helped in the end was to replace the function with an extra
>> useless where clause (in the hope that it would force it to create a new
>> plan)
>>
>> So, the function only have a single SELECT inside:
>> RETURN QUERY
>> SELECT * FROM table
>> WHERE bigint_column = X
>> LIMIT 100 OFFSET 0;
>>
>> And this is my modification that made it work again:
>> RETURN QUERY
>> SELECT * FROM table
>> WHERE bigint_column = X AND 1=1
>> LIMIT 100 OFFSET 0;
>>
>>
>> Obviously we are now worried why this happened and how we can avoid it in
>> the future? We run Postgres 9.3 on CentOS 6.
>>
>> Thanks!
>> Victor
>>
>
>