Puzzling planner choice (non-urgent)

From: Richard Huxton <dev(at)archonet(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Puzzling planner choice (non-urgent)
Date: 2002-08-05 10:48:02
Message-ID: 200208051148.02648.dev@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've been playing with the stats analyser (not had a chance yet) and came
across an odd choice with a fairly simple query.

Vacuum analyse'd and indexes in all the obvious places (this is a test
database I use). Version 7.2.1

First the fast query (reformatted for ease of reading):

richardh=> SET enable_seqscan=off;
SET VARIABLE
richardh=> EXPLAIN ANALYSE
SELECT o.ord_id, c.co_name, p.pr_desc, o.ord_qty, o.ord_placed
FROM orders o
JOIN companies c ON o.ord_company=c.co_id
JOIN products p ON o.ord_product=p.pr_code
ORDER BY o.ord_placed LIMIT 200;
NOTICE: QUERY PLAN:

Limit (cost=0.00..1889.33 rows=200 width=90)
(actual time=0.36..42.32 rows=200 loops=1)
-> Nested Loop (cost=0.00..944666.75 rows=100000 width=90)
(actual time=0.35..41.71 rows=201 loops=1)
-> Nested Loop (cost=0.00..475591.80 rows=100000 width=57)
(actual time=0.25..23.34 rows=201 loops=1)
-> Index Scan using ord_placed_idx on orders o
(cost=0.00..4772.99 rows=100000 width=25)
(actual time=0.10..6.52 rows=201 loops=1)
-> Index Scan using companies_co_id_key on companies c
(cost=0.00..4.70 rows=1 width=32)
(actual time=0.05..0.06 rows=1 loops=201)
-> Index Scan using products_pkey on products p (cost=0.00..4.68
rows=1 width=33)
(actual time=0.05..0.07 rows=1 loops=201)
Total runtime: 43.44 msec

EXPLAIN
richardh=> SET enable_seqscan=on;
...as before...

Limit (cost=0.00..1509.55 rows=200 width=90)
(actual time=2.96..600.16 rows=200 loops=1)
-> Nested Loop (cost=0.00..754772.99 rows=100000 width=90)
(actual time=2.95..599.62 rows=201 loops=1)
-> Nested Loop (cost=0.00..429772.99 rows=100000 width=57)
(actual time=1.60..299.58 rows=201 loops=1)
-> Index Scan using ord_placed_idx on orders o
(cost=0.00..4772.99 rows=100000 width=25)
(actual time=0.10..6.87 rows=201 loops=1)
-> Seq Scan on companies c (cost=0.00..3.00 rows=100 width=32)
(actual time=0.02..0.97 rows=100 loops=201)
-> Seq Scan on products p (cost=0.00..2.00 rows=100 width=33)
(actual time=0.02..0.98 rows=100 loops=201)
Total runtime: 601.12 msec

What I don't understand is the seq-scan on companies.co_id since it has a
unique index on it and *can't* match more than once for a given row while
looping through orders.

As the subject said, not urgent, I'm just curious since the planner usually
does much better than this.

- Richard Huxton

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Elielson Fontanezi 2002-08-05 10:58:35 RES: concept question: PostgreSQL vs. Oracle database
Previous Message Masaru Sugawara 2002-08-05 10:26:02 Re: Serials: removing the holes? (consecutive)