pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Rewriting using rules for performance
5+ messages / 3 participants
[nested] [flat]

* Rewriting using rules for performance
@ 2009-04-03 13:17  Matthew Wakeling <matthew@flymine.org>
  0 siblings, 2 replies; 5+ messages in thread

From: Matthew Wakeling @ 2009-04-03 13:17 UTC (permalink / raw)
  To: pgsql-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.



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Rewriting using rules for performance
@ 2009-04-03 13:46  Robert Haas <robertmhaas@gmail.com>
  parent: Matthew Wakeling <matthew@flymine.org>
  1 sibling, 1 reply; 5+ messages in thread

From: Robert Haas @ 2009-04-03 13:46 UTC (permalink / raw)
  To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Rewriting using rules for performance
@ 2009-04-03 13:55  Matthew Wakeling <matthew@flymine.org>
  parent: Robert Haas <robertmhaas@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Matthew Wakeling @ 2009-04-03 13:55 UTC (permalink / raw)
  To: pgsql-performance

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.



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Rewriting using rules for performance
@ 2009-04-03 15:52  Merlin Moncure <mmoncure@gmail.com>
  parent: Matthew Wakeling <matthew@flymine.org>
  1 sibling, 1 reply; 5+ messages in thread

From: Merlin Moncure @ 2009-04-03 15:52 UTC (permalink / raw)
  To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Rewriting using rules for performance
@ 2009-04-03 15:59  Merlin Moncure <mmoncure@gmail.com>
  parent: Merlin Moncure <mmoncure@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Merlin Moncure @ 2009-04-03 15:59 UTC (permalink / raw)
  To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2009-04-03 15:59 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-04-03 13:17 Rewriting using rules for performance Matthew Wakeling <matthew@flymine.org>
2009-04-03 13:46 ` Robert Haas <robertmhaas@gmail.com>
2009-04-03 13:55   ` Matthew Wakeling <matthew@flymine.org>
2009-04-03 15:52 ` Merlin Moncure <mmoncure@gmail.com>
2009-04-03 15:59   ` Merlin Moncure <mmoncure@gmail.com>

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