massive performance hit when using "Limit 1"

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

Responses

Browse pgsql-general by date

  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