From: Greg Smith <greg@2ndQuadrant.com>
To: pgsql-admin@postgresql.org
Subject: Re: Prepared statments: partial indexes are avoided!
Date: Mon, 20 Jun 2011 23:25:31 -0400
Message-ID: <4E000F2B.4000102@2ndQuadrant.com> (raw)
In-Reply-To: <4DFF5F5C.7040109@hbs-research.com>
References: <4DC17AAB.1080206@hbs-research.com>
<4DFF52BB.5060605@hbs-research.com>
<BANLkTimfT4OHQKb6Y7M4wQrffpWNUtPqJQ@mail.gmail.com>
<4DFF5F5C.7040109@hbs-research.com>
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@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: greg@2ndQuadrant.com
Subject: Re: Prepared statments: partial indexes are avoided!
In-Reply-To: <4E000F2B.4000102@2ndQuadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox