Thread: Pushing LIMIT into sub-queries of a UNION ALL?

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

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/

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 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/

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