Re: Prepared statments: partial indexes are avoided!

On 06/20/2011 10:55 AM, adrien ducos wrote:
> I think this should at least be writen in this documentation:
> http://www.postgresql.org/docs/9.0/static/indexes-partial.html
> as a known limitation to avoid people spending hours of search for why
> is the application becoming slow.

It's documented in the "Notes" section of
http://www.postgresql.org/docs/9.0/static/sql-prepare.html because it's
not specific to partial indexes. Prepared statements are very risky
when you run them against queries with a very skewed distribution, like
your case where 99% of the values for a column are the same. They help
to prevent SQL injection and can reduce parsing/planning overhead, but
prepared statements can have terrible performance in some unfortunately
common situations. Just recently I had one clients end up switching to
another ORM specifically because they couldn't get the one they started
with to stop using prepared statements, and those gave them terrible
query plans.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books