Обсуждение: Puzzling planner choice (non-urgent)

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

Richard Huxton <dev@archonet.com> writes:
> 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.

Since the table is evidently only 2 pages long, the planner figures it's
cheaper to read it all than to touch both the index and the table.

The hole in this logic is that the cost estimate is made without regard
for the fact that we're scanning the table repeatedly, and so at least
the first few levels of index are likely to stay swapped in.  I have
some ideas about how to improve that, but dunno if it'll get done for
7.3.  In the meantime you might try reducing random_page_cost to see if
you like the results better.

            regards, tom lane

On Monday 05 Aug 2002 3:28 pm, Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
> > 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.
>
> Since the table is evidently only 2 pages long, the planner figures it's
> cheaper to read it all than to touch both the index and the table.

Ah - seems like a good guess Tom. I've upped companies to 1000 entries and the
planner switches to an index-scan.

- Richard Huxton