On Wed, May 18, 2011 at 5:26 PM, Dave Johansen <davejohansen@xxxxxxxxx> wrote: > I am using Postgres 8.3.3 and 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 query with the LIMIT explicitly placed in the sub-queries? Can you show DDL and queries? The query with the LIMIT on the subqueries and the one with the LIMIT on the overall query are not semantically equivalent. Since you can have an ORDER BY before the LIMIT on the query with the limit on the view the database must have all the rows before it can apply the ordering and properly determine the limit. Although it might be possible to determine under particular circumstances that only one of the tables needs to be queried or tables need only be queried partially I deem that quite complex. I do not know whether Postgres can do such optimizations but for that we would certainly need to see the concrete example (including constraint and indexes). Kind regards robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
