Skip site navigation (1) Skip section navigation (2)

SELECT...VIEW...UNION...LIMIT

From: "Ed L(dot)" <pgsql(at)bluepolka(dot)net>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: SELECT...VIEW...UNION...LIMIT
Date: 2004-11-25 05:04:09
Message-ID: 200411242204.09775.pgsql@bluepolka.net (view raw or flat)
Thread:
Lists: pgsql-general
I have "big_table" (1M rows) and "small_table" (1K rows) with 
identical schemas and together in a view as follows:

	create view big_view as
		select *, 'big_table'::varchar as source from big_table
		union
		select *, 'small_table'::varchar as source from small_table;

I tried this query...

	select * from big_view limit 1

...expecting a quick result, but no joy.  Is there something I can do 
to make this work?  Here's the explain:

$ psql -c "explain select * from big_view limit 1"    
                                               QUERY PLAN                                                
---------------------------------------------------------------------------------------------------------
 Limit  (cost=294405.67..294405.79 rows=1 width=711)
   ->  Subquery Scan big_view  (cost=294405.67..295871.93 rows=11730 width=711)
         ->  Unique  (cost=294405.67..295871.93 rows=11730 width=711)
               ->  Sort  (cost=294405.67..294698.92 rows=117301 width=711)
                     Sort Key: value, cdate, "key", source
                     ->  Append  (cost=0.00..183139.01 rows=117301 width=711)
                           ->  Subquery Scan "*SELECT* 1"  (cost=0.00..183119.01 rows=116301 width=711)
                                 ->  Seq Scan on big_table  (cost=0.00..183119.01 rows=116301 width=711)
                           ->  Subquery Scan "*SELECT* 2"  (cost=0.00..20.00 rows=1000 width=72)
                                 ->  Seq Scan on small_table  (cost=0.00..20.00 rows=1000 width=72)
(10 rows)


Responses

pgsql-general by date

Next:From: Miles KeatonDate: 2004-11-25 05:12:45
Subject: why use SCHEMA? any real-world examples?
Previous:From: Reid ThompsonDate: 2004-11-25 04:39:47
Subject: Re: Any good report/form generators for postgresql?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group