From: Tom Lane <tgl@sss.pgh.pa.us>
To: Guillaume Smet <guillaume.smet@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Workaround for cross column stats dependency
Date: Tue, 22 Jan 2008 20:43:13 -0500
Message-ID: <10869.1201052593@sss.pgh.pa.us> (raw)
In-Reply-To: <1d4e0c10801221657s6b345fcdqd1c0b3f20e519316@mail.gmail.com>
References: <1d4e0c10801221657s6b345fcdqd1c0b3f20e519316@mail.gmail.com>
"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
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: tgl@sss.pgh.pa.us, guillaume.smet@gmail.com
Subject: Re: Workaround for cross column stats dependency
In-Reply-To: <10869.1201052593@sss.pgh.pa.us>
* 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