Desperate: View not using indexes (very slow) - Mailing list 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