Re: Nested loop in simple query taking long time

6 dec 2007 kl. 18.12 skrev Tom Lane:

> Henrik Zagerholm <henke(at)mac(dot)se> writes:
>> 5 dec 2007 kl. 16.25 skrev Tom Lane:
>>> Henrik Zagerholm <henke(at)mac(dot)se> 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