From: Guillaume Smet <guillaume.smet@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Workaround for cross column stats dependency
Date: Wed, 23 Jan 2008 03:02:50 +0100
Message-ID: <1d4e0c10801221802y38dfdc60pde9092ff5ea295a4@mail.gmail.com> (raw)
In-Reply-To: <10869.1201052593@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: guillaume.smet@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Workaround for cross column stats dependency
In-Reply-To: <1d4e0c10801221802y38dfdc60pde9092ff5ea295a4@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox