On Wed, Nov 12, 2003 at 16:30:41 +0100,
Paulo Jan <admin@digital.ddnet.es> wrote:
> This table has about 28000 rows, and is running with Postgres 7.2.3
> under Red Hat 8.0, in a 2.4 Ghz. Pentiun 4 with 512 Mb. of RAM.
You probably want to use 7.4 for this since a new way to do aggragates using
hashes has been added. 7.4 is currently in release candidate status and
maybe be released as early as next Monday.
>
> This query takes up to 3 minutes to execute. I have tried to strip
> it down and leaving it in its most vanilla form (without "count(id)" and
> But here is the weird thing: I dump the table, export it into
> another machine running Postgres 7.3.2 (Celeron 1.7 Ghz, 512 Mb. of
> memory), and the query takes only 2 or 3 seconds to execute, even though
> the query plan is almost the same:
This makes it sound like you haven't been properly vacuuming and/or
analyzing the database. You might want to run a vacuum full on the
production db and see if that speeds things up. Once you have done
a vacuum full then regular vacuums should keep the number of dead tuples
down (as long as FSM is set high enough).