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

Re: Workaround for cross column stats dependency

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

In response to

Responses

pgsql-performance by date

Next:From: Guillaume SmetDate: 2008-01-23 02:02:50
Subject: Re: Workaround for cross column stats dependency
Previous:From: Guillaume SmetDate: 2008-01-23 00:57:07
Subject: Workaround for cross column stats dependency

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