View's plan not taking advantage of WHERE? - Mailing list pgsql-general
| From | Mike Summers |
|---|---|
| Subject | View's plan not taking advantage of WHERE? |
| Date | |
| Msg-id | CAJGeMG89QbDxMab7-aPD_yXVsGx7Q=auXYM9UFVaq06cRZ4E2A@mail.gmail.com Whole thread Raw |
| Responses |
Re: View's plan not taking advantage of WHERE?
(Mike Summers <msummers57@gmail.com>)
|
| List | pgsql-general |
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.
pgsql-general by date: