SELECT...VIEW...UNION...LIMIT - Mailing list pgsql-general

From Ed L.
Subject SELECT...VIEW...UNION...LIMIT
Date
Msg-id 200411242204.09775.pgsql@bluepolka.net
Whole thread Raw
Responses Re: SELECT...VIEW...UNION...LIMIT
List 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)


pgsql-general by date:

Previous
From: Reid Thompson
Date:
Subject: Re: Any good report/form generators for postgresql?
Next
From: Miles Keaton
Date:
Subject: why use SCHEMA? any real-world examples?