I have a VIEW that does not appear to take advantage of the WHERE when given the opportunity:
db=# explain select * from best_for_sale_layouts; QUERY PLAN ------------------------------------------------------------------------------------------------ Aggregate (cost=1977.21..1977.22 rows=1 width=118) -> Sort (cost=1938.18..1940.05 rows=748 width=122) Sort Key: sources.for_sale_layout_rank -> Hash Join (cost=1.04..1902.48 rows=748 width=122) Hash Cond: (for_sale_layouts.source_id = sources.id) -> Append (cost=0.00..1613.60 rows=74760 width=118) -> Seq Scan on for_sale_layouts (cost=0.00..806.74 rows=37374 width=118) -> Seq Scan on assessor_records (cost=0.00..806.86 rows=37386 width=118) -> Hash (cost=1.02..1.02 rows=2 width=8) -> Seq Scan on sources (cost=0.00..1.02 rows=2 width=8) (10 rows) db=# explain analyze select * from best_for_sale_layouts where address_id = 2871034;; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=1979.33..1979.34 rows=1 width=118) (actual time=93569.509..93569.510 rows=1 loops=1) Filter: (first_not_null(for_sale_layouts.address_id) = 2871034) -> Sort (cost=1938.18..1940.05 rows=748 width=122) (actual time=320.652..464.523 rows=74748 loops=1) Sort Key: sources.for_sale_layout_rank Sort Method: external sort Disk: 5840kB -> Hash Join (cost=1.04..1902.48 rows=748 width=122) (actual time=0.057..198.500 rows=74748 loops=1) Hash Cond: (for_sale_layouts.source_id = sources.id) -> Append (cost=0.00..1613.60 rows=74760 width=118) (actual time=0.022..94.871 rows=74748 loops=1) -> Seq Scan on for_sale_layouts (cost=0.00..806.74 rows=37374 width=118) (actual time=0.021..22.361 rows=37374 loops=1) -> Seq Scan on assessor_records (cost=0.00..806.86 rows=37386 width=118) (actual time=0.011..23.383 rows=37374 loops=1) -> Hash (cost=1.02..1.02 rows=2 width=8) (actual time=0.015..0.015 rows=2 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on sources (cost=0.00..1.02 rows=2 width=8) (actual time=0.007..0.010 rows=2 loops=1) Total runtime: 93573.390 ms (14 rows) If I run the View's select with the WHERE in psql I get what I expect (first_not_null is an aggregate function): db=# explain analyze SELECT first_not_null(a.id) as id, first_not_null(a.address_id) as address_id, .... first_not_null(a.created_at) as created_at, first_not_null(a.updated_at) as updated_at FROM (SELECT b.*, for_sale_layout_rank FROM ((SELECT * FROM for_sale_layouts UNION ALL SELECT * FROM assessor_records) AS b INNER JOIN sources ON b.source_id = sources.id) ORDER BY for_sale_layout_rank) AS a where address_id = 2871034; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=21.23..21.24 rows=1 width=118) (actual time=0.571..0.571 rows=1 loops=1) -> Sort (cost=17.64..17.64 rows=2 width=122) (actual time=0.272..0.274 rows=2 loops=1) Sort Key: sources.for_sale_layout_rank Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=0.00..17.63 rows=2 width=122) (actual time=0.199..0.253 rows=2 loops=1) Join Filter: (for_sale_layouts.source_id = sources.id) Rows Removed by Join Filter: 2 -> Append (cost=0.00..16.54 rows=2 width=118) (actual time=0.140..0.185 rows=2 loops=1) -> Index Scan using index_for_sale_layouts_on_address_id on for_sale_layouts (cost=0.00..8.27 rows=1 width=118) (actual time=0.139..0.142 rows=1 loops=1) Index Cond: (address_id = 2871034) -> Index Scan using index_assessor_layouts_on_address_id on assessor_records (cost=0.00..8.27 rows=1 width=118) (actual time=0.038..0.039 rows=1 loops=1) Index Cond: (address_id = 2871034) -> Materialize (cost=0.00..1.03 rows=2 width=8) (actual time=0.022..0.025 rows=2 loops=2) -> Seq Scan on sources (cost=0.00..1.02 rows=2 width=8) (actual time=0.020..0.023 rows=2 loops=1) Total runtime: 0.802 ms (15 rows) Is there anything I can do to get the View to update its plan? Improvements are welcome, although for other reasons (Rails' ActiveRecord) the View is a must. Thanks in advance.