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