Thread: SELECT...VIEW...UNION...LIMIT

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)

"Ed L." <pgsql@bluepolka.net> writes:

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

Try "UNION ALL" instead of just "union"

The difference is that union has to avoid duplicates. If you want duplicates
to be included or know for certain there will be no duplicates then union all
is faster.

--
greg