From: | Rich Doughty <rich(at)opusvl(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | massive performance hit when using "Limit 1" |
Date: | 2005-12-05 14:21:47 |
Message-ID: | 43944CFB.40808@opusvl.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Frank.Motzkat | 2005-12-05 14:39:59 | how to use SAVEPOINT in stored function |
Previous Message | Pandurangan | 2005-12-05 14:08:25 | Re: ODBC Layer and the now() function |