Re: Crazy looking actual row count from explain analyze

Tom Lane-2 wrote:
>
> My first suspicion
> is that those are unvacuumed dead rows ... what's your vacuuming policy
> on this database?
>

Ah, I didn't know that number included dead tuples.  That probably explains
it.  pg_stat_user_tables says the table has 370,269 dead tuples.  On this
table, I have autovacuum_vacuum_scale_factor set to 0.02, so I believe the
table will have to have 869K dead tuples before vacuum will kick in.

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


Tom Lane-2 wrote:
>
> I think the new index might have "fixed" things largely by not bothering
> to index already-dead rows.
>

Actually, I put a partial index on status, where != 'V'.  That fits our
usage pattern of 99% of the records being 'V', so it's a tiny index and
satisifies this type of query very quickly.

Thanks,

--gordon

--
View this message in context:
http://old.nabble.com/Crazy-looking-actual-row-count-from-explain-analyze-tp28517643p28518862.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.