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