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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
