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)
----------------------------------------------------------------------------
