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

Desperate: View not using indexes (very slow)

From: Jen Sale <js(at)slipt(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: Desperate: View not using indexes (very slow)
Date: 2006-01-25 16:09:25
Message-ID: 200601251109.25876.js@slipt.net (view raw or flat)
Thread:
Lists: pgsql-performance
We recently segmented a large table into calendar month slices and were going 
to to replace the original, but we are not getting the results we think it 
should...  Everything is vacuumed, and we are using 8.0.3 on amd64.

Anything anyone can suggest would be appreciated, our backs against the wall.

=> explain select suck_id from sucks_new where suck_id in ( select id as 
suck_id from saved_cart_items where 
publish_id='60160b57a1969fa228ae3470fbe7a50a' );
                                                                                                   
QUERY PLAN 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop IN Join  (cost=5311290.80..7124499.86 rows=5472 width=32)
   Join Filter: ("outer".suck_id = "inner".id)
   ->  Subquery Scan sucks_new  (cost=5309907.40..6181642.53 rows=13947762 
width=32)
         ->  Unique  (cost=5309907.40..6042164.91 rows=13947762 width=212)
               ->  Sort  (cost=5309907.40..5344776.81 rows=13947762 width=212)
                     Sort Key: suck_id, sitenum
                     ->  Append  (cost=0.00..632289.24 rows=13947762 
width=212)
                           ->  Subquery Scan "*SELECT* 
1"  (cost=0.00..83767.54 rows=1703577 width=209)
                                 ->  Seq Scan on sucks_2006_01  
(cost=0.00..66731.77 rows=1703577 width=209)
                           ->  Subquery Scan "*SELECT* 
2"  (cost=0.00..93670.20 rows=2081560 width=209)
                                 ->  Seq Scan on sucks_2005_12  
(cost=0.00..72854.60 rows=2081560 width=209)
                           ->  Subquery Scan "*SELECT* 
3"  (cost=0.00..91311.16 rows=2021958 width=210)
                                 ->  Seq Scan on sucks_2005_11  
(cost=0.00..71091.58 rows=2021958 width=210)
                           ->  Subquery Scan "*SELECT* 
4"  (cost=0.00..85510.34 rows=1886417 width=211)
                                 ->  Seq Scan on sucks_2005_10  
(cost=0.00..66646.17 rows=1886417 width=211)
                           ->  Subquery Scan "*SELECT* 
5"  (cost=0.00..74216.38 rows=1642719 width=210)
                                 ->  Seq Scan on sucks_2005_09  
(cost=0.00..57789.19 rows=1642719 width=210)
                           ->  Subquery Scan "*SELECT* 
6"  (cost=0.00..64346.12 rows=1429106 width=209)
                                 ->  Seq Scan on sucks_2005_08  
(cost=0.00..50055.06 rows=1429106 width=209)
                           ->  Subquery Scan "*SELECT* 
7"  (cost=0.00..76449.66 rows=1709283 width=209)
                                 ->  Seq Scan on sucks_2005_07  
(cost=0.00..59356.83 rows=1709283 width=209)
                           ->  Subquery Scan "*SELECT* 
8"  (cost=0.00..63017.84 rows=1473142 width=212)
                                 ->  Seq Scan on sucks_2005_06 
"local"  (cost=0.00..48286.42 rows=1473142 width=212)
   ->  Materialize  (cost=1383.39..1383.60 rows=20 width=12)
         ->  Seq Scan on saved_cart_items  (cost=0.00..1383.38 rows=20 
width=12)
               Filter: (publish_id = 
'60160b57a1969fa228ae3470fbe7a50a'::bpchar)

as opposed to 

=> explain select suck_id from sucks_new where suck_id=7136642;                                                                                                 
QUERY PLAN 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan sucks_new  (cost=46.22..46.72 rows=8 width=32)
   ->  Unique  (cost=46.22..46.64 rows=8 width=212)
         ->  Sort  (cost=46.22..46.24 rows=8 width=212)
               Sort Key: suck_id, sitenum
               ->  Append  (cost=0.00..46.10 rows=8 width=212)
                     ->  Subquery Scan "*SELECT* 1"  (cost=0.00..5.64 rows=1 
width=209)
                           ->  Index Scan using sucks_2006_01_pkey on 
sucks_2006_01  (cost=0.00..5.63 rows=1 width=209)
                                 Index Cond: (suck_id = 7136642::numeric)
                     ->  Subquery Scan "*SELECT* 2"  (cost=0.00..5.95 rows=1 
width=209)
                           ->  Index Scan using sucks_2005_12_pkey on 
sucks_2005_12  (cost=0.00..5.94 rows=1 width=209)
                                 Index Cond: (suck_id = 7136642::numeric)
                     ->  Subquery Scan "*SELECT* 3"  (cost=0.00..5.21 rows=1 
width=210)
                           ->  Index Scan using sucks_2005_11_pkey on 
sucks_2005_11  (cost=0.00..5.20 rows=1 width=210)
                                 Index Cond: (suck_id = 7136642::numeric)
                     ->  Subquery Scan "*SELECT* 4"  (cost=0.00..5.67 rows=1 
width=211)
                           ->  Index Scan using sucks_2005_10_pkey on 
sucks_2005_10  (cost=0.00..5.66 rows=1 width=211)
                                 Index Cond: (suck_id = 7136642::numeric)
                     ->  Subquery Scan "*SELECT* 5"  (cost=0.00..5.78 rows=1 
width=210)
                           ->  Index Scan using sucks_2005_09_pkey on 
sucks_2005_09  (cost=0.00..5.77 rows=1 width=210)
                                 Index Cond: (suck_id = 7136642::numeric)
                     ->  Subquery Scan "*SELECT* 6"  (cost=0.00..6.01 rows=1 
width=209)
                           ->  Index Scan using sucks_2005_08_pkey on 
sucks_2005_08  (cost=0.00..6.00 rows=1 width=209)
                                 Index Cond: (suck_id = 7136642::numeric)
                     ->  Subquery Scan "*SELECT* 7"  (cost=0.00..5.87 rows=1 
width=209)
                           ->  Index Scan using sucks_2005_07_pkey on 
sucks_2005_07  (cost=0.00..5.86 rows=1 width=209)
                                 Index Cond: (suck_id = 7136642::numeric)
                     ->  Subquery Scan "*SELECT* 8"  (cost=0.00..5.98 rows=1 
width=212)
                           ->  Index Scan using sucks_2005_06_pkey on 
sucks_2005_06 "local"  (cost=0.00..5.97 rows=1 width=212)
                                 Index Cond: (suck_id = 7136642::numeric)
(29 rows)




can someone please tell me what we did wrong?

TIA



Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2006-01-25 16:21:58
Subject: Re: DB responce during DB dump
Previous:From: Richard HuxtonDate: 2006-01-25 12:44:45
Subject: Re: DB responce during DB dump

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