To: "Guillaume Smet" <guillaume.smet@gmail.com>
cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Workaround for cross column stats dependency 
In-reply-to: <1d4e0c10801221657s6b345fcdqd1c0b3f20e519316@mail.gmail.com> 
References: <1d4e0c10801221657s6b345fcdqd1c0b3f20e519316@mail.gmail.com>
Comments: In-reply-to "Guillaume Smet" <guillaume.smet@gmail.com>
	message dated "Wed, 23 Jan 2008 01:57:07 +0100"
Date: Tue, 22 Jan 2008 20:43:13 -0500
Message-ID: <10869.1201052593@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Guillaume Smet" <guillaume.smet@gmail.com> writes:
> So the question is: is there any way to improve the results of the
> original query, other than doing a first query in the application to
> get the list of types and inject them in a second query (the one just
> above)?

Well, if you're willing to cheat like mad, you can use a phony immutable
function to perform that injection.  Here's a really silly example in
the regression database:

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)

Since the function is marked immutable, it'll be pre-evaluated during
planning and then the constant array result is exposed for statistics
purposes.

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.

			regards, tom lane