Skip site navigation (1) Skip section navigation (2)

Re: Workaround for cross column stats dependency

From: "Guillaume Smet" <guillaume(dot)smet(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Workaround for cross column stats dependency
Date: 2008-01-23 02:02:50
Message-ID: 1d4e0c10801221802y38dfdc60pde9092ff5ea295a4@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Joshua FielekDate: 2008-01-23 04:11:48
Subject: Making the most of memory?
Previous:From: Tom LaneDate: 2008-01-23 01:43:13
Subject: Re: Workaround for cross column stats dependency

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group