Thread

Misbehavior of the query optimizer when using limit.

Domingo Alvarez Duarte <domingo@dad-it.com> — 2002-02-26T08:10:03Z

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.26 rows=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_makers cm  (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)
        ->  Nested Loop  (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)

----------------------------------------------------------------------------