X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 041E4D1ED8D for ; Sat, 3 Apr 2004 21:30:13 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 59107-07 for ; Sat, 3 Apr 2004 17:30:27 -0400 (AST) Received: from gpdnet.co.uk (gpdnet.plus.com [212.56.100.243]) by svr1.postgresql.org (Postfix) with ESMTP id 77832D1ED91 for ; Sat, 3 Apr 2004 17:30:10 -0400 (AST) Received: from Spooler by gpdnet.co.uk (Mercury/32 v4.01a) ID MO001E59; 3 Apr 2004 22:34:24 +0100 Received: from spooler by gpdnet.co.uk (Mercury/32 v4.01a); 3 Apr 2004 22:34:06 +0100 Received: from gary (192.168.1.2) by gpdnet.co.uk (Mercury/32 v4.01a) with ESMTP ID MG001E58; 3 Apr 2004 22:33:56 +0100 Resent-From: "Gary Doades" Resent-To: pgsql-performance@postgresql.org Resent-date: Sat, 03 Apr 2004 22:33:56 +0100 X-cs: R From: Gary Doades X-RS-ID: X-RS-Flags: 0,0,1,1,0,0,0 X-RS-Header: In-reply-to: <200404031059.39268.josh@agliodbs.com> X-RS-Header: References: <406EA51B.21218.D2DE061@localhost> X-RS-Sigset: 0 To: pgsql-performance-owner@postgresql.org Subject: Re: PostgreSQL and Linux 2.6 kernel. MIME-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-transfer-encoding: 8BIT Date: Sat, 03 Apr 2004 20:32:35 +0100 Message-ID: Resent-Message-Id: <20040403213010.77832D1ED91@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/17 X-Sequence-Number: 6317 Actually it hasn't been my experience either. Most of my queries against the database, large and small are either a little quicker or no real difference. I have only really noticed big differences under stress when memory (RAM) is being squeezed. The main winner on 2.6 seems to be write performance and memory management. Unfortunately I only have one test machine and I can't really keep switching between 2.4 and 2.6 to do the comparisons. I had written down 27 timings from a set of SQL of varying complexity using the 2.4 kernel. Each SQL statement was executed 10 times and the average of the last 5 was used. I can only really compare those timings against the new installation on 2.6. I know that this is not ideal "real world" testing, but it is good enough for me at the moment. Unless anyone has contradictory indications then I will proceed with 2.6. I did increase the default stats target from 10 to 50 and re-analysed. The explain numbers are slightly different, but the time to run was almost the same. Not surprising since the plan was the same. QUERY PLAN Merge Join (cost=0.00..192636.20 rows=2845920 width=92) Merge Cond: ("outer".reqt_id = "inner".reqt_id) -> Index Scan using order_reqt_pkey on order_reqt r (cost=0.00..52662.40 rows=2206291 width=6) -> Index Scan using staff_book_idx2 on staff_booking (cost=0.00..102529.28 rows=2845920 width=90) 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