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 <obartu...@gmail.com> wrote: > > > 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 >> > >
