Andrew,
> Oops, sorry. What if you force the index use here? Just because the
> planner thinks that's more expensive doesn't mean that it is.
Yeah, I tried it ... no faster, no slower, really.
BTW, in case you missed it, the real concern is that an UPDATE query similar
to the SELECT query we are discussing takes over 10 minutes, which on this
hardware is ridiculous. Robert suggested that we test the SELECT query to
see if there were general performance problems; apparently, there are.
The hardware I'm using is:
dual-processor Athalon 1400mhz motherboard
raid 5 UW SCSI drive array with 3 drives
512mb DDR RAM
SuSE Linux 7.3 (Kernel 2.4.10)
Postgres is on its own LVM partition
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.3
(will upgrade to 7.2.3 very soon)
Postgresql.conf has: fdatasync, various chared memory tuned to allocate 256mb
to postgres (which seems to be working correctly).
Debug level 2.
When the UPDATE query takes a long time, I generally can watch the log hover
in the land of "Reaping dead child processes" for 30-90 seconds per
iteration.
--
Josh Berkus
josh@agliodbs.com
Aglio Database Solutions
San Francisco