Explain output: wrong row count?

From: Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>
To: Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Explain output: wrong row count?
Date: 2004-11-25 16:11:22
Message-ID: 20041125161122.GE13383@dcc.uchile.cl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I have this EXPLAIN ANALYZE output:

alvherre=# explain analyze select * from oficina join ciudad using
(codciudad) where codtipoofi in (4,5);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Hash Join (cost=15.24..68.76 rows=621 width=394) (actual time=6.003..22.175 rows=641 loops=1)
Hash Cond: ("outer".codciudad = "inner".codciudad)
-> Seq Scan on oficina (cost=0.00..42.66 rows=621 width=309) (actual time=0.013..3.103 rows=641 loops=1)
Filter: ((codtipoofi = 4) OR (codtipoofi = 5))
-> Hash (cost=13.99..13.99 rows=499 width=89) (actual time=5.947..5.947 rows=0 loops=1)
-> Seq Scan on ciudad (cost=0.00..13.99 rows=499 width=89) (actual time=0.018..3.909 rows=499 loops=1)
Total runtime: 24.448 ms
(7 rows)

Note that the Hash step has an estimated row count of 499 (which is a
good estimate), but the actual row count is 0, which is way off. But,
the outer Hash Join step has a good estimate.

I wonder if the rows=0 is a bug, or is that number meant to be
interpreted in some special way? It is the same on both 7.4.6 and
8.0beta5. (Now that I look, it's 0 in all Hash steps I have at sight
... I had never noticed before!)

This query doesn't actually affect me a lot, but it is part of a bigger
query whose estimation is way off. I won't post it here because it's
topic for pgsql-performance ...

--
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
"No reniegues de lo que alguna vez creíste"

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2004-11-25 16:19:10 Re: Intermittent bug
Previous Message Thomas Hallgren 2004-11-25 14:40:24 Re: Intermittent bug