Thread: Using partial index in combination with prepared statement parameters
Hi, It's been covered a few times in the past, = http://www.postgresql.org/message-id/BANLkTimfT4OHQKb6Y7M4wQrffpWNUtPqJQ@m= ail.gmail.com = http://postgresql.1045698.n5.nabble.com/partial-indexes-not-used-on-parame= terized-queries-td2121027.html but in a nutshell, partial indices do not play nicely with prepared = statements because whether the index is valid or not cannot be known at query plan time. I am curious if there is any development on this? I have looked around = but don't see any activity (whether Simon's "I have a patch but don't hold your breath" or = otherwise). I ask largely because we use prepared statements everywhere and this = makes it very hard to use partial indices, which would offer us significant performance = gains. Does anyone know of any acceptable workaround? Is there continued = interest in maybe improving the PostgreSQL behavior in this case? Thanks! Steven Schlansker
Steven Schlansker <steven@likeness.com> writes: > It's been covered a few times in the past, > http://www.postgresql.org/message-id/BANLkTimfT4OHQKb6Y7M4wQrffpWNUtPqJQ@mail.gmail.com > http://postgresql.1045698.n5.nabble.com/partial-indexes-not-used-on-parameterized-queries-td2121027.html > but in a nutshell, partial indices do not play nicely with prepared > statements because whether the index is valid or not cannot be known > at query plan time. This should be pretty much a non-issue in 9.2 and up; if the partial index is actually useful enough to be worth worrying about, the plancache choice logic will realize that it should use custom not generic plans. http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=e6faf910d You might still have an issue if the partial index is only sometimes usable --- the choice logic might decide to go for the generic-plan approach anyway. But if you've got a case where the optimal plan is all over the map like that, I wonder why you're using a prepared statement at all ... regards, tom lane