Using partial index in combination with prepared statement parameters
Hi,
It's been covered a few times in the past,
/messages/by-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.
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Steven Schlansker <steven@likeness.com> writes:
It's been covered a few times in the past,
/messages/by-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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general