Message-ID: <4E000F2B.4000102@2ndQuadrant.com>
Date: Mon, 20 Jun 2011 23:25:31 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20110505 Icedove/3.0.11
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: Prepared statments: partial indexes are avoided!
References: <4DC17AAB.1080206@hbs-research.com>
	<4DFF52BB.5060605@hbs-research.com>
	<BANLkTimfT4OHQKb6Y7M4wQrffpWNUtPqJQ@mail.gmail.com>
	<4DFF5F5C.7040109@hbs-research.com>
In-Reply-To: <4DFF5F5C.7040109@hbs-research.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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