Sorry, I think I misread your post in my last reply. I thought you were still talking 
the big update....

The main thing I have noticed about SQLServer is it seems more willing to do hash or 
merge joins than PostgreSQL. I have experimented with various postgresql.conf 
parameters and even turned off nested loops to see the difference. When actually 
getting a merge join out of PostgreSQL when it wanted to do a nested loop it, not 
surprisingly, took longer to execute.

Looking at the SQLServer plan it seemed to be spending MUCH less time in the sort 
operations than PostgreSQL. This is probably what leads SQLServer to go for 
hash/merge joins more often. The other problem is that the SQLServer timings are 
skewed by its query plan caching.

For one query SQLserver plan said it spent 2% of its time in a big sort, the same 
in PostgreSQL when hash join was forced spent 23% of its time on the sort (from 
analyse actual stats). I have played about with the sort_mem, but it doesn't make much 

I have also noticed that SQLServer tends to fold more complex IN subselects into the 
main query using merge joins, maybe for the same reason as above.

SQLServer seems to have some more "exotic" joins ("nested loop/left semi join","nested 
loop/left anti semi join"). These are probably just variants of nested loops, but I 
know enough about it to say if they make a difference. Clustered indexes and clustered 
index seeks also seem to be a big player in the more complex queries.

I still have quite a lot comparitive testing and tuning to do before I can nail it 
further, but I will let you know when I have some hard stats to go on.

On 3 Apr 2004 at 10:59, Josh Berkus wrote:


> 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 

-Josh Berkus
 Aglio Database Solutions
 San Francisco

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Incoming mail is certified Virus Free.
Checked by AVG Anti-Virus (
Version: 7.0.230 / Virus Database: 262.6.5 - Release Date: 31/03/2004

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

Reply via email to