Search Postgresql Archives

View's plan not taking advantage of WHERE?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux