Slow execution time when querying view with WHERE clause

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

 



I have the following view:

create or replace view market.p_areas as
select a.*
from _areas a
where a.area in (
 select b.area
 from _bins b, _inventories i, _offers o, _pricemembers p
 where b.bin = i.bin and
 i.inventory = o.inventory and
 o.pricegroup = p.pricegroup and
 p.buyer in (
  select s.store
  from _stores s, _webusers w
  where w.webuser = getWebuser() and
  w.company = s.company
   union
  select s.store
  from _stores s, _companies c
  where s.company = c.company and
  c.companyid = 'DEFAULT'
 )
);

When I query the view without a where clause I get:

 explain analyze select * from p_areas;
                                                                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=1273.12..1276.31 rows=47 width=163) (actual time=438.739..439.574 rows=34 loops=1)
   Hash Cond: ("outer".area = "inner".area)
   ->  Seq Scan on _areas a  (cost=0.00..2.48 rows=48 width=163) (actual time=0.015..0.169 rows=48 loops=1)
   ->  Hash  (cost=1273.01..1273.01 rows=47 width=8) (actual time=438.532..438.532 rows=0 loops=1)
         ->  HashAggregate  (cost=1273.01..1273.01 rows=47 width=8) (actual time=438.286..438.395 rows=34 loops=1)
               ->  Hash Join  (cost=558.53..1266.68 rows=2532 width=8) (actual time=160.923..416.968 rows=5264 loops=1)
                     Hash Cond: ("outer".bin = "inner".bin)
                     ->  Hash Join  (cost=544.02..1207.86 rows=2531 width=8) (actual time=156.097..356.560 rows=5264 loops=1)
                           Hash Cond: ("outer".inventory = "inner".inventory)
                           ->  Seq Scan on _inventories i  (cost=0.00..265.96 rows=11396 width=16) (actual time=0.010..44.047 rows=11433 loops=1)
                           ->  Hash  (cost=537.14..537.14 rows=2751 width=8) (actual time=155.891..155.891 rows=0 loops=1)
                                 ->  Hash Join  (cost=13.96..537.14 rows=2751 width=8) (actual time=11.967..136.598 rows=5264 loops=1)
                                       Hash Cond: ("outer".pricegroup = "inner".pricegroup)
                                       ->  Seq Scan on _offers o  (cost=0.00..379.24 rows=15524 width=16) (actual time=0.008..50.335 rows=15599 loops=1)
                                       ->  Hash  (cost=13.94..13.94 rows=9 width=8) (actual time=11.861..11.861 rows=0 loops=1)
                                             ->  Hash IN Join  (cost=8.74..13.94 rows=9 width=8) (actual time=10.801..11.801 rows=12 loops=1)
                                                   Hash Cond: ("outer".buyer = "inner".store)
                                                   ->  Seq Scan on _pricemembers p  (cost=0.00..4.07 rows=207 width=16) (actual time=0.011..0.548 rows=207 loops=1)
                                                   ->  Hash  (cost=8.72..8.72 rows=8 width=8) (actual time=10.687..10.687 rows=0 loops=1)
                                                         ->  Subquery Scan "IN_subquery"  (cost=8.60..8.72 rows=8 width=8) (actual time=10.645..10.654 rows=1 loops=1)
                                                               ->  Unique  (cost=8.60..8.64 rows=8 width=8) (actual time=10.631..10.636 rows=1 loops=1)
                                                                     ->  Sort  (cost=8.60..8.62 rows=8 width=8) (actual time=10.625..10.627 rows=1 loops=1)
                                                                           Sort Key: store
                                                                           ->  Append  (cost=2.86..8.48 rows=8 width=8) (actual time=10.529..10.583 rows=1 loops=1)
                                                                                 ->  Subquery Scan "*SELECT* 1"  (cost=2.86..5.15 rows=5 width=8) (actual time=10.222..10.222 rows=0 loops=1)
                                                                                       ->  Hash Join  (cost=2.86..5.10 rows=5 width=8) (actual time=10.214..10.214 rows=0 loops=1)
                                                                                             Hash Cond: ("outer".company = "inner".company)
                                                                                             ->  Seq Scan on _stores s  (cost=0.00..2.13 rows=13 width=16) (actual time=0.019..0.059 rows=13 loops=1)
                                                                                             ->  Hash  (cost=2.85..2.85 rows=1 width=8) (actual time=10.031..10.031 rows=0 loops=1)
                                                                                                   ->  Seq Scan on _webusers w  (cost=0.00..2.85 rows=1 width=8) (actual time=10.023..10.023 rows=0 loops=1)
                                                                                                         Filter: (webuser = getwebuser())
                                                                                 ->  Subquery Scan "*SELECT* 2"  (cost=1.08..3.33 rows=3 width=8) (actual time=0.298..0.349 rows=1 loops=1)
                                                                                       ->  Hash Join  (cost=1.08..3.30 rows=3 width=8) (actual time=0.287..0.335 rows=1 loops=1)
                                                                                             Hash Cond: ("outer".company = "inner".company)
                                                                                             ->  Seq Scan on _stores s  (cost=0.00..2.13 rows=13 width=16) (actual time=0.008..0.048 rows=13 loops=1)
                                                                                             ->  Hash  (cost=1.07..1.07 rows=1 width=8) (actual time=0.111..0.111 rows=0 loops=1)
                                                                                                   ->  Seq Scan on _companies c  (cost=0.00..1.07 rows=1 width=8) (actual time=0.059..0.080 rows=1 loops=1)
                                                                                                         Filter: ((companyid)::text = 'DEFAULT'::text)
                     ->  Hash  (cost=13.01..13.01 rows=601 width=16) (actual time=4.735..4.735 rows=0 loops=1)
                           ->  Seq Scan on _bins b  (cost=0.00..13.01 rows=601 width=16) (actual time=0.054..2.846 rows=601 loops=1)
 Total runtime: 441.685 ms
(41 rows)

---

When I query the view with a simple filter, I get:

explain analyze select * from p_areas where deactive is null;
                                                                                  QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop IN Join  (cost=8.60..524.28 rows=1 width=163) (actual time=1023.291..20025.620 rows=34 loops=1)
   Join Filter: ("outer".area = "inner".area)
   ->  Seq Scan on _areas a  (cost=0.00..2.48 rows=1 width=163) (actual time=0.037..0.804 rows=48 loops=1)
         Filter: (deactive IS NULL)
   ->  Nested Loop  (cost=8.60..25530.60 rows=2532 width=8) (actual time=0.345..402.775 rows=3408 loops=48)
         ->  Nested Loop  (cost=8.60..16893.61 rows=2531 width=8) (actual time=0.304..264.929 rows=3408 loops=48)
               ->  Merge Join  (cost=8.60..2912.00 rows=2751 width=8) (actual time=0.258..120.841 rows=3408 loops=48)
                     Merge Cond: ("outer".pricegroup = "inner".pricegroup)
                     ->  Nested Loop IN Join  (cost=8.60..1837.73 rows=9 width=8) (actual time=0.216..4.612 rows=8 loops=48)
                           Join Filter: ("outer".buyer = "inner".store)
                           ->  Index Scan using i_pricemembers3 on _pricemembers p  (cost=0.00..10.96 rows=207 width=16) (actual time=0.022..1.045 rows=138 loops=48)
                           ->  Subquery Scan "IN_subquery"  (cost=8.60..8.72 rows=8 width=8) (actual time=0.011..0.017 rows=1 loops=6606)
                                 ->  Unique  (cost=8.60..8.64 rows=8 width=8) (actual time=0.006..0.010 rows=1 loops=6606)
                                       ->  Sort  (cost=8.60..8.62 rows=8 width=8) (actual time=0.003..0.004 rows=1 loops=6606)
                                             Sort Key: store
                                             ->  Append  (cost=2.86..8.48 rows=8 width=8) (actual time=7.667..7.757 rows=1 loops=1)
                                                   ->  Subquery Scan "*SELECT* 1"  (cost=2.86..5.15 rows=5 width=8) (actual time=7.362..7.362 rows=0 loops=1)
                                                         ->  Hash Join  (cost=2.86..5.10 rows=5 width=8) (actual time=7.355..7.355 rows=0 loops=1)
                                                               Hash Cond: ("outer".company = "inner".company)
                                                               ->  Seq Scan on _stores s  (cost=0.00..2.13 rows=13 width=16) (actual time=0.013..0.054 rows=13 loops=1)
                                                               ->  Hash  (cost=2.85..2.85 rows=1 width=8) (actual time=7.163..7.163 rows=0 loops=1)
                                                                     ->  Seq Scan on _webusers w  (cost=0.00..2.85 rows=1 width=8) (actual time=7.154..7.154 rows=0 loops=1)
                                                                           Filter: (webuser = getwebuser())
                                                   ->  Subquery Scan "*SELECT* 2"  (cost=1.08..3.33 rows=3 width=8) (actual time=0.295..0.381 rows=1 loops=1)
                                                         ->  Hash Join  (cost=1.08..3.30 rows=3 width=8) (actual time=0.286..0.368 rows=1 loops=1)
                                                               Hash Cond: ("outer".company = "inner".company)
                                                               ->  Seq Scan on _stores s  (cost=0.00..2.13 rows=13 width=16) (actual time=0.008..0.080 rows=13 loops=1)
                                                               ->  Hash  (cost=1.07..1.07 rows=1 width=8) (actual time=0.116..0.116 rows=0 loops=1)
                                                                     ->  Seq Scan on _companies c  (cost=0.00..1.07 rows=1 width=8) (actual time=0.062..0.083 rows=1 loops=1)
                                                                           Filter: ((companyid)::text = 'DEFAULT'::text)
                     ->  Index Scan using i_offers4 on _offers o  (cost=0.00..1007.93 rows=15524 width=16) (actual time=0.023..67.183 rows=10049 loops=48)
               ->  Index Scan using i_inventories1 on _inventories i  (cost=0.00..5.07 rows=1 width=16) (actual time=0.025..0.029 rows=1 loops=163561)
                     Index Cond: (i.inventory = "outer".inventory)
         ->  Index Scan using i_bins1 on _bins b  (cost=0.00..3.40 rows=1 width=16) (actual time=0.021..0.026 rows=1 loops=163561)
               Index Cond: (b.bin = "outer".bin)
 Total runtime: 20027.414 ms
(36 rows)

---

That's a slow-down on execution time by a factor of 50, even
though the row count was the same: 34. In fact, it's MUCH
faster to do:

create temporary table foo as
select * from p_areas;

select * from foo
where deactive is null;

The database has been analyzed.

Any tips would be greatly appreciated.

Mike Mascari

P.S.: I turned off word-wrap in my mail client for this post.
Is that the right thing to do for analyze output?




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux