massive performance hit when using "Limit 1" - Mailing list pgsql-general

can anyone explain the reason for the difference in the
following 2 query plans, or offer any advice? the two queries
are identical apart from the limit clause.

the performance here is fine and is the same for LIMIT >= 2

   EXPLAIN SELECT _t.* FROM
         tokens.ta_tokens       _t INNER JOIN
         tokens.ta_tokens_stock _s ON _t.token_id = _s.token_id
   WHERE
         _s.retailer_id = '96599' AND
         _t.value       = '10'
   ORDER BY
         _t.number ASC
   LIMIT '2';

                                               QUERY PLAN
------------------------------------------------------------------------------------------------------
  Limit  (cost=22757.15..22757.15 rows=2 width=27)
    ->  Sort  (cost=22757.15..22760.88 rows=1491 width=27)
          Sort Key: _t.number
          ->  Nested Loop  (cost=0.00..22678.56 rows=1491 width=27)
                ->  Seq Scan on ta_tokens_stock _s  (cost=0.00..75.72 rows=4058 width=4)
                      Filter: ((retailer_id)::integer = 96599)
                ->  Index Scan using ta_tokens_pkey on ta_tokens _t  (cost=0.00..5.56 rows=1 width=27)
                      Index Cond: ((_t.token_id)::integer = ("outer".token_id)::integer)
                      Filter: ((value)::numeric = 10::numeric)
(9 rows)

This one goes nuts and doesn't return. is there any way i can
force a query plan similar to the one above?

   EXPLAIN SELECT _t.* FROM
        tokens.ta_tokens       _t INNER JOIN
        tokens.ta_tokens_stock _s ON _t.token_id = _s.token_id
   WHERE
        _s.retailer_id = '96599' AND
        _t.value       = '10'
   ORDER BY
        _t.number ASC
   LIMIT '1';
                                                     QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..14967.39 rows=1 width=27)
    ->  Nested Loop  (cost=0.00..22316378.56 rows=1491 width=27)
          ->  Index Scan using ta_tokens_number_key on ta_tokens _t  (cost=0.00..15519868.33 rows=1488768 width=27)
                Filter: ((value)::numeric = 10::numeric)
          ->  Index Scan using ta_tokens_stock_pkey on ta_tokens_stock _s  (cost=0.00..4.55 rows=1 width=4)
                Index Cond: (("outer".token_id)::integer = (_s.token_id)::integer)
                Filter: ((retailer_id)::integer = 96599)
(7 rows)


All tables are vacuumed and analysed. the row estimates in the
plans are accurate.


select version();
                                                    version
--------------------------------------------------------------------------------------------------------------
  PostgreSQL 8.0.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.2 20050821 (prerelease) (Debian 4.0.1-6)



Thanks a lot,


   - Rich Doughty