Re: Rewriting using rules for performance

On Fri, Apr 3, 2009 at 11:52 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> 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;

oop, I read your question wrong. for the above to work, _you_ have to
choose the plan, not the planner. I think it still might be possible
so long as you can deterministically figure out (say, as the result of
a function) which query you want the planner to choose using a form of
the above technique.

merlin