Query plan not updated after dropped index

From: Victor Blomqvist <vb(at)viblo(dot)se>
To: pgsql-general(at)postgresql(dot)org
Subject: Query plan not updated after dropped index
Date: 2016-02-18 08:17:03
Message-ID: CAL870DVUok9gx4T3SEY6UwXUs0zxiT6iHwbLeCivX3XGYA-+xA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sridhar N Bamandlapally 2016-02-18 08:26:59 Re: JDBC behaviour
Previous Message Vladimir Sitnikov 2016-02-18 08:13:39 Re: [JDBC] JDBC behaviour