Re: Workaround for cross column stats dependency

On Jan 23, 2008 2:43 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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