Re: PostgreSQL and Linux 2.6 kernel. - Mailing list pgsql-performance

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 majordomo@postgresql.org 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