Gary,
> There are no indexes on the columns involved in the update, they are
> not required for my usual select statements. This is an attempt to
> slightly denormalise the design to get the performance up comparable
> to SQL Server 2000. We hope to move some of our databases over to
> PostgreSQL later in the year and this is part of the ongoing testing.
> SQLServer's query optimiser is a bit smarter that PostgreSQL's (yet)
> so I am hand optimising some of the more frequently used
> SQL and/or tweaking the database design slightly.
Hmmm ... that hasn't been my general experience on complex queries. However,
it may be due to a difference in ANALYZE statistics. I'd love to see you
increase your default_stats_target, re-analyze, and see if PostgreSQL gets
"smarter".
--
-Josh Berkus
Aglio Database Solutions
San Francisco
