DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=RA9CsvJsUitqJqiMb518JWb7N8qhfgKAQ3z2e0TeqLQugLUzHFzGArXFm2GumifYjGiye7HAFDIDhZ5hcTLrWAMw/+6JjqZKxNrHTp7t0Qh63vHWK9+u0VbCar1qPRHg+dFqMelPg7MK5jBTboH8UnlCnbLHrL0pjZMD+N/ihkA=
Message-ID: <1d4e0c10801221802y38dfdc60pde9092ff5ea295a4@mail.gmail.com>
Date: Wed, 23 Jan 2008 03:02:50 +0100
From: "Guillaume Smet" <guillaume.smet@gmail.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: Workaround for cross column stats dependency
Cc: pgsql-performance <pgsql-performance@postgresql.org>
In-Reply-To: <10869.1201052593@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <1d4e0c10801221657s6b345fcdqd1c0b3f20e519316@mail.gmail.com>
	<10869.1201052593@sss.pgh.pa.us>

On Jan 23, 2008 2:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> regression=# create or replace function getu2(int) returns int[] as $$
> select array(select unique2 from tenk1 where thousand = $1);
> $$ language sql immutable;
> CREATE FUNCTION
> regression=# explain select * from tenk1 where unique1 = any(getu2(42));
>                                               QUERY PLAN
> ------------------------------------------------------------------------------------------------------
>  Bitmap Heap Scan on tenk1  (cost=38.59..73.80 rows=10 width=244)
>    Recheck Cond: (unique1 = ANY ('{381,932,2369,4476,5530,6342,6842,6961,7817,7973}'::integer[]))
>    ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..38.59 rows=10 width=0)
>          Index Cond: (unique1 = ANY ('{381,932,2369,4476,5530,6342,6842,6961,7817,7973}'::integer[]))
> (4 rows)

I'll give it a try tomorrow.

> Now this method *only* works for interactive queries, or EXECUTE'd
> queries in plpgsql, because you don't want the plan containing the
> folded constants to get cached.  At least not if you're worried about
> responding promptly to changes in the table you're fetching from.
> But if that table is essentially constant anyway in your application,
> there's little downside to this trick.

Yeah, that sounds like a good idea in our case. We don't use prepared
statements for these queries.

I'll post my results tomorrow morning.

Thanks.

--
Guillaume