Thread: Misbehavior of the query optimizer when using limit.
Misbehavior of the query optimizer when using limit.
From
domingo@dad-it.com (Domingo Alvarez Duarte)
Date:
I have a strange behavior of postgresql 7.1.3 and 7.2 when I make a query without limit it works reazonable but when using limit the postgresql eats all cpu for a long time, when using explain to see what postgresql is trying to do I got two complete different strategies to do it. Can someone explain that ? -------------------------------------------------------------------------- -- First without using limit explain select ca.*, cm.name as maker_name, cmm.name as model_name from car_adverts as ca, car_makers as cm, car_models as cmm where cm.id = ca.maker and cmm.id = ca.model; NOTICE: QUERY PLAN: Merge Join (cost=4782.06..5076.37 rows=16254 width=208) -> Index Scan using car_models_pkey on car_models cmm (cost=0.00..72.98 rows=1452 width=16) -> Sort (cost=4782.06..4782.06 rows=16254 width=192) -> Merge Join (cost=2503.93..2708.26rows=16254 width=192) -> Sort (cost=2498.96..2498.96 rows=16254 width=176) -> Seq Scan on car_adverts ca (cost=0.00..505.54 rows=16254 width=176) -> Sort (cost=4.97..4.97 rows=93 width=16) -> Seq Scan on car_makerscm (cost=0.00..1.93 rows=93 width=16) --------------------------------------------------------------------------- -- Now the same using limit explain select ca.*, cm.name as maker_name, cmm.name as model_name from car_adverts as ca, car_makers as cm, car_models as cmm where cm.id = ca.maker and cmm.id = ca.model limit 10; NOTICE: QUERY PLAN: Limit (cost=0.00..457.94 rows=10 width=208) -> Nested Loop (cost=0.00..744329.21 rows=16254 width=208) -> NestedLoop (cost=0.00..694063.72 rows=16254 width=192) -> Seq Scan on car_adverts ca (cost=0.00..505.54 rows=16254 width=176) -> Seq Scan on car_models cmm (cost=0.00..24.52 rows=1452 width=16) -> Seq Scan on car_makers cm (cost=0.00..1.93 rows=93 width=16) ----------------------------------------------------------------------------