Re: Nested loop in simple query taking long time

Future Enhancement?
If the column's new value can fit in the space already being used by the
existing value, just change the column value in place and leave the
record alone. Would reduce the need for vacuum in many cases.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Alvaro Herrera
Sent: Thursday, December 06, 2007 2:26 PM
To: Henrik
Cc: Tom Lane; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Nested loop in simple query taking long time

Henrik wrote:

> 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. :)

I suggest you put a lone VACUUM on that table in cron, say once every 5
minutes, and you should be fine. You shouldn't need a reindex at all.

--
Alvaro Herrera
http://www.PlanetPostgreSQL.org/
"Right now the sectors on the hard disk run clockwise, but I heard a
rumor that
you can squeeze 0.2% more throughput by running them counterclockwise.
It's worth the effort. Recommended." (Gerry Pourwelle)

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly