6 dec 2007 kl. 18.12 skrev Tom Lane:
Henrik Zagerholm <henke@xxxxxx> writes:
5 dec 2007 kl. 16.25 skrev Tom Lane:
Henrik Zagerholm <henke@xxxxxx> writes:
-> Bitmap Index Scan on tbl_archive_idx1
(cost=0.00..1150.47 rows=8 width=0) (actual time=1505.456..1505.456
rows=86053 loops=16)
Index Cond: (tbl_share.pk_share_id =
tbl_archive.fk_share_id)
Why is this scan finding so many more rows than the planner expects?
This is really weird. That tables primary key sequence is at 1220
and the number of rows right now is 139. There have never been that
many rows in tbl_archive. Could the index or stat be really really
corrupt?
I wonder how long it's been since you vacuumed that table? The
rowcount
from the bitmap indexscan would include tuple IDs that are in the
index
but prove to be dead upon arrival at the heap.
I actually have autovacuum on the whole database but I may have been a
little too restrictive on the autovacuum settings.
I did a vacuum full and a reindex and now its fine again.
I think I have a clue why its so off. We update a value in that table
about 2 - 3 million times per night and as update creates a new row it
becomes bloated pretty fast. The table hade a size of 765 MB including
indexes and after vacuum full and reindex it went down to 80kB... I
guess I need routine reindex on this table. Thank god is not big. :)
Thanks Tom!
Cheers,
Henke
regards, tom lane
---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
