DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:sender:in-reply-to:references:from:date
	:x-google-sender-auth:message-id:subject:to:cc:content-type
	:content-transfer-encoding;
	b=ZxbxwakmSmQNauvT+SlGTVkOg30CjmL7hHu/0uISslvso4HMiz7oaP7Z4yznluEjdx
	QmdE9j4KuEoUjTEPzErTC167ZDj9MW08G/ECMQG0zDzn1foWItMSctF2eAF9+A/YMfGy
	1HdfbdX+UtKgpjoxupCvB+45BzlRgHuk9cUCg=
MIME-Version: 1.0
In-Reply-To: <6B5AF6293A289F45826220B17ABE7937FDB16C@BORON.aers.local>
References: <6B5AF6293A289F45826220B17ABE7937FDB16C@BORON.aers.local>
From: Greg Stark <gsstark@mit.edu>
Date: Wed, 8 Jul 2009 01:43:06 +0100
Message-ID: <407d949e0907071743u20025562w8141c824019a204f@mail.gmail.com>
Subject: Re: FW: Query length limitation in postgres server > 8.2.9
To: jacob@aers.ca
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On Tue, Jul 7, 2009 at 11:33 PM, <jacob@aers.ca> wrote:
> After some investigation it seems that the new server is refusing to use the index's but if I
> limit the number of arguments in the latter part of the statement to 100 then it works as
> expected in the expected amount of time using the indexs.

Ugh, I thought this sounded familiar. I think you're hitting this
limit which was put in place in 8.2.12 to protect against very slow
planning times for very long IN lists:

/*
 * Proof attempts involving large arrays in ScalarArrayOpExpr nodes are
 * likely to require O(N^2) time, and more often than not fail anyway.
 * So we set an arbitrary limit on the number of array elements that
 * we will allow to be treated as an AND or OR clause.
 * XXX is it worth exposing this as a GUC knob?
 */
#define MAX_SAOP_ARRAY_SIZE		100


For your situation I'm not sure what to suggest. You could try to make
the query more complex with something like WHERE site_id = 1 AND
(leaf_category in (...) OR leaf_category IN (...)) but I'm not too
hopeful that will work out well.

I wonder if you couldn't get a better plan by stuffing these values
into a temporary table (or even a VALUES query query) and doing a
join. Offhand I don't see any great plan this would result in.

One option would be to recompile postgres with this limit raised. Keep
in mind that the long planning times it was meant to protect against
might start to be a problem, but if you weren't already having a
problem with that in <8.2.11 then perhaps you would be ok.

-- 
greg
http://mit.edu/~gsstark/resume.pdf