pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRewriting 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