Sorry, I think I misread your post in my last reply. I thought you were still talking
about
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
query
in PostgreSQL when hash join was forced spent 23% of its time on the sort (from
explain
analyse actual stats). I have played about with the sort_mem, but it doesn't make much
diffrence.
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
don't
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
down
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:
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
---------------------------(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 (http://www.grisoft.com).
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
