Row estimates on empty table

From: Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>
To: "pgsql-general(at)postgresql(dot)org List" <pgsql-general(at)postgresql(dot)org>
Subject: Row estimates on empty table
Date: 2009-09-17 18:33:07
Message-ID: A27E8314-D87A-4C3F-9112-A3884893AC8A@solfertje.student.utwente.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello all,

I'm seeing something strange with the row-estimates on an empty table.
The table in question is merely a template-table that specialised
tables inherit from, it will never contain any data. Nevertheless,
after importing my creation script and vacuum analyse the result I see
is this:

dalroi=# SELECT * FROM ONLY unit;
unit | format | scales_up | scales_down
------+--------+-----------+-------------
(0 rows)

dalroi=# EXPLAIN ANALYZE SELECT * FROM ONLY unit;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on unit (cost=0.00..18.50 rows=850 width=66) (actual
time=0.001..0.001 rows=0 loops=1)
Total runtime: 0.025 ms
(2 rows)

As you see, estimated rows 850, actual rows 0!

Now 25 µs doesn't sound like much, but this data is going to be joined
to another small table and it's throwing the estimated number of rows
WAY off. See here: http://explain-analyze.info/query_plans/3956-Alban-s-unit-normalization-query-1

(Yes, 4 ms still isn't bad, but these queries are likely going to be
at the basis of many other queries so they need to be snap-snap-snap!
The more joins the worse the estimate will get, right?)

So what's going on here?

For the record, this is PG 8.4 compiled from macports on Snow Leopard.
I've seen a few odd reports with that combination so I thought I'd
mention it. To be exact: PostgreSQL 8.4.0 on i386-apple-darwin10.0.0,
compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc.
build 5646), 64-bit

Alban Hertroys

--
Screwing up is the best way to attach something to the ceiling.

!DSPAM:737,4ab280e511031155049759!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message hubert depesz lubaczewski 2009-09-17 18:34:20 Re: creation of foreign key without checking prior data?
Previous Message Joshua D. Drake 2009-09-17 18:30:22 Re: pg_restore -j