Re: Rewriting using rules for performance

On Fri, Apr 3, 2009 at 9:17 AM, Matthew Wakeling <matthew(at)flymine(dot)org> wrote:
>
> So, I have a view. The query that the view uses can be written two different
> ways, to use two different indexes. Then I use the view in another query,
> under some circumstances the first way will be quick, and under other
> circumstances the second way will be quick.
>
> What I want to know is, can I create a view that has both queries, and
> allows the planner to choose which one to use? The documentation seems to
> say so in http://www.postgresql.org/docs/8.3/interactive/querytree.html (the
> rule system "creates zero or more query trees as result"), but doesn't say
> how one would do it.

yes.

create view v as
select * from
(
select true as b, pg_sleep(1)::text
union all
select false as b, pg_sleep(1)::text
) q;

recent versions of pg are smart enough to optimize (in some cases):
select * from v where b;

merlin