Re: Workaround for cross column stats dependency

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