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)
