pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPushing LIMIT into sub-queries of a UNION ALL
7+ messages / 4 participants
[nested] [flat]
* Pushing LIMIT into sub-queries of a UNION ALL
@ 2011-05-16 19:38 Dave Johansen <davejohansen@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Dave Johansen @ 2011-05-16 19:38 UTC (permalink / raw)
To: pgsql-performance
I am using Postgres 8.3 and I have an issue very closely related to the one
described here:
http://archives.postgresql.org/pgsql-general/2005-06/msg00488.php
Basically, I have a VIEW which is a UNION ALL of two tables but when I do a
select on the view using a LIMIT, it scans the entire tables and takes
significantly longer than writing out the query with the LIMITs in the
sub-queries themselves. Is there a solution to get the view to perform like
the sub-query version?
Thanks,
Dave
^ permalink raw reply [nested|flat] 7+ messages in thread
* Pushing LIMIT into sub-queries of a UNION ALL?
@ 2011-05-17 15:31 Dave Johansen <davejohansen@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Dave Johansen @ 2011-05-17 15:31 UTC (permalink / raw)
To: pgsql-performance
I am using Postgres 8.3 and I have an issue very closely related to the one
described here:
http://archives.postgresql.org/pgsql-general/2005-06/msg00488.php
Basically, I have a VIEW which is a UNION ALL of two tables but when I do a
select on the view using a LIMIT, it scans the entire tables and takes
significantly longer than writing out the query with the LIMITs in the
sub-queries themselves. Is there a solution to get the view to perform like
the sub-query version?
Thanks,
Dave
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Pushing LIMIT into sub-queries of a UNION ALL?
@ 2011-05-22 17:34 Robert Klemme <shortcutter@googlemail.com>
parent: Dave Johansen <davejohansen@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Robert Klemme @ 2011-05-22 17:34 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-performance
Dave,
how often do you want to repeat that posting? What about instead
replying to the answers you got so far?
Cheers
robert
On Tue, May 17, 2011 at 5:31 PM, Dave Johansen <davejohansen@gmail.com> wrote:
> I am using Postgres 8.3 and I have an issue very closely related to the one
> described here:
> http://archives.postgresql.org/pgsql-general/2005-06/msg00488.php
>
> Basically, I have a VIEW which is a UNION ALL of two tables but when I do a
> select on the view using a LIMIT, it scans the entire tables and takes
> significantly longer than writing out the query with the LIMITs in the
> sub-queries themselves. Is there a solution to get the view to perform like
> the sub-query version?
>
> Thanks,
> Dave
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Pushing LIMIT into sub-queries of a UNION ALL?
@ 2011-05-23 15:54 Dave Johansen <davejohansen@gmail.com>
parent: Robert Klemme <shortcutter@googlemail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Dave Johansen @ 2011-05-23 15:54 UTC (permalink / raw)
To: pgsql-performance
I apologize for the multiple posts. I sent this email right after joining
the list and after it hadn't shown up a day later I figured that it had been
lost or something and sent the other one.
Also, the database I posted this about does not have internet access and so
I'm working on getting it moved over to a machine that does or getting it
the info onto a machine where I can post the pertinent information about the
schema and explain outputs.
Thanks,
Dave
--
Dave Johansen
phone: (520) 302-4526
On Sun, May 22, 2011 at 10:34 AM, Robert Klemme
<shortcutter@googlemail.com>wrote:
> Dave,
>
> how often do you want to repeat that posting? What about instead
> replying to the answers you got so far?
>
> Cheers
>
> robert
>
>
> On Tue, May 17, 2011 at 5:31 PM, Dave Johansen <davejohansen@gmail.com>
> wrote:
> > I am using Postgres 8.3 and I have an issue very closely related to the
> one
> > described here:
> > http://archives.postgresql.org/pgsql-general/2005-06/msg00488.php
> >
> > Basically, I have a VIEW which is a UNION ALL of two tables but when I do
> a
> > select on the view using a LIMIT, it scans the entire tables and takes
> > significantly longer than writing out the query with the LIMITs in the
> > sub-queries themselves. Is there a solution to get the view to perform
> like
> > the sub-query version?
> >
> > Thanks,
> > Dave
>
>
>
> --
> remember.guy do |as, often| as.you_can - without end
> http://blog.rubybestpractices.com/
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Pushing LIMIT into sub-queries of a UNION ALL
@ 2011-05-23 17:21 Robert Haas <robertmhaas@gmail.com>
parent: Dave Johansen <davejohansen@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Robert Haas @ 2011-05-23 17:21 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-performance
On Mon, May 16, 2011 at 3:38 PM, Dave Johansen <davejohansen@gmail.com> wrote:
> I am using Postgres 8.3 and I have an issue very closely related to the one
> described here:
> http://archives.postgresql.org/pgsql-general/2005-06/msg00488.php
>
> Basically, I have a VIEW which is a UNION ALL of two tables but when I do a
> select on the view using a LIMIT, it scans the entire tables and takes
> significantly longer than writing out the query with the LIMITs in the
> sub-queries themselves. Is there a solution to get the view to perform like
> the sub-query version?
I believe this is fixed by MergeAppend in 9.1. You might want to try
9.1beta1 and see if that works better for you.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Pushing LIMIT into sub-queries of a UNION ALL?
@ 2011-05-23 19:47 Robert Klemme <shortcutter@googlemail.com>
parent: Dave Johansen <davejohansen@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Robert Klemme @ 2011-05-23 19:47 UTC (permalink / raw)
To: Dave Johansen <davejohansen@gmail.com>; +Cc: pgsql-performance
On Mon, May 23, 2011 at 5:54 PM, Dave Johansen <davejohansen@gmail.com> wrote:
> I apologize for the multiple posts. I sent this email right after joining
> the list and after it hadn't shown up a day later I figured that it had been
> lost or something and sent the other one.
Sorry for the nitpicking but I even see _three_ instances of this
posting (first on May 18th).
> Also, the database I posted this about does not have internet access and so
> I'm working on getting it moved over to a machine that does or getting it
> the info onto a machine where I can post the pertinent information about the
> schema and explain outputs.
Great!
Cheers
robert
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Pushing LIMIT into sub-queries of a UNION ALL?
@ 2011-05-23 21:31 Josh Berkus <josh@agliodbs.com>
parent: Dave Johansen <davejohansen@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Josh Berkus @ 2011-05-23 21:31 UTC (permalink / raw)
To: pgsql-performance
On 5/23/11 8:54 AM, Dave Johansen wrote:
> I apologize for the multiple posts. I sent this email right after joining
> the list and after it hadn't shown up a day later I figured that it had been
> lost or something and sent the other one.
List moderation took a holiday while all of us were at pgCon.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2011-05-23 21:31 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-16 19:38 Pushing LIMIT into sub-queries of a UNION ALL Dave Johansen <davejohansen@gmail.com>
2011-05-23 17:21 ` Robert Haas <robertmhaas@gmail.com>
2011-05-17 15:31 Pushing LIMIT into sub-queries of a UNION ALL? Dave Johansen <davejohansen@gmail.com>
2011-05-22 17:34 ` Re: Pushing LIMIT into sub-queries of a UNION ALL? Robert Klemme <shortcutter@googlemail.com>
2011-05-23 15:54 ` Re: Pushing LIMIT into sub-queries of a UNION ALL? Dave Johansen <davejohansen@gmail.com>
2011-05-23 19:47 ` Re: Pushing LIMIT into sub-queries of a UNION ALL? Robert Klemme <shortcutter@googlemail.com>
2011-05-23 21:31 ` Re: Pushing LIMIT into sub-queries of a UNION ALL? Josh Berkus <josh@agliodbs.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