Re: Query plan not updated after dropped index

[Oleg Bartunov <obartunov@xxxxxxxxx>]

On Thu, Feb 18, 2016 at 11:17 AM, Victor Blomqvist <vb@xxxxxxxx> 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