Skip site navigation (1) Skip section navigation (2)

Slow execution time when querying view with WHERE clause

From: Mike Mascari <mascarm(at)mascari(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Slow execution time when querying view with WHERE clause
Date: 2004-11-22 21:38:03
Message-ID: 41A25C3B.3040608@mascari.com (view raw or flat)
Thread:
Lists: pgsql-performance
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?



Responses

pgsql-performance by date

Next:From: Mike MascariDate: 2004-11-22 21:46:08
Subject: Re: Slow execution time when querying view with WHERE clause
Previous:From: Alexandre LeclercDate: 2004-11-22 20:32:40
Subject: Data type to use for primary key

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group