Puzzling planner choice (non-urgent)

Поиск
Список
Период
Сортировка
От Richard Huxton
Тема Puzzling planner choice (non-urgent)
Дата
Msg-id 200208051148.02648.dev@archonet.com
обсуждение исходный текст
Ответы Re: Puzzling planner choice (non-urgent)  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Elielson Fontanezi
Дата:
Сообщение: RES: concept question: PostgreSQL vs. Oracle database
Следующее
От: Karel Zak
Дата:
Сообщение: Re: Convert number of month to name of month