Thread: Rewriting using rules for performance

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.

Matthew

--
 I have an inferiority complex. But it's not a very good one.

On Fri, Apr 3, 2009 at 9:17 AM, Matthew Wakeling <matthew@flymine.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.

I think this would be clearer if you gave an actual example of what
you're trying to accomplish, but the short answer is "no".  The rule
system lets you create multiple query trees to perform multiple
actions (for example, when an INSERT command is issued, do the
original insert plus also an update) and it implements views.  But
it's independent of query planning.

On the other hand, the query planner should be figuring out which
index to use without any help from you.  If it's not, something is
wrong.

...Robert

On Fri, 3 Apr 2009, Robert Haas wrote:
> On the other hand, the query planner should be figuring out which
> index to use without any help from you.  If it's not, something is
> wrong.

Unfortunately it cannot tell that

SELECT l1.id AS id1, l2.id AS id2 FROM location l1, location l2
WHERE l1.start <= l2.end AND l2.start <= l1.end

is the same as

SELECT l1.id AS id1, l2.id AS id2 FROM location l1, location l2
WHERE bioseg_create(l1.start, l1.end) && bioseg_create(l2.start, l2.end)

which is also the same as

SELECT * from do_overlaps() AS (id1 int, id2 int)

But thanks for clarifying the rule thing for me.

Matthew

--
 The email of the species is more deadly than the mail.

On Fri, Apr 3, 2009 at 9:17 AM, Matthew Wakeling <matthew@flymine.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

On Fri, Apr 3, 2009 at 11:52 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Fri, Apr 3, 2009 at 9:17 AM, Matthew Wakeling <matthew@flymine.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