Re: Crazy looking actual row count from explain analyze

Gordon Shannon <gordo169(at)gmail(dot)com> writes:
> -> Bitmap Heap Scan on m_20100201 (cost=987806.75..987810.75 rows=1
> width=0) (actual time=2340.191..2340.191 rows=0 loops=1)
> Recheck Cond: (status = ANY ('{S,X}'::message_status_enum[]))
> -> Bitmap Index Scan on m_20100201_topic_multi
> (cost=0.00..987806.75 rows=1 width=0) (actual time=2334.371..2334.371
> rows=126336 loops=1)
> Index Cond: (status = ANY ('{S,X}'::message_status_enum[]))

> What I don't understand is the "actual rows" of 126,336 in the bitmap index
> scan. I would expect it to have to scan every index entry, but doesn't this
> output mean that it's *returning* 126K rows from that scan? Whereas I think
> it should return zero.

Well, it does return zero rows from the actual heapscan. What the above
is telling you is that a whole lot of rows are being returned by the
index and then filtered out at the table scan stage. My first suspicion
is that those are unvacuumed dead rows ... what's your vacuuming policy
on this database?

> I have already fixed this query by adding a better index.

I think the new index might have "fixed" things largely by not bothering
to index already-dead rows.

regards, tom lane