Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 0A2E264FC20 for ; Tue, 23 Sep 2008 10:34:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 20053-09 for ; Tue, 23 Sep 2008 10:34:04 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from outmail128097.authsmtp.com (outmail128097.authsmtp.com [62.13.128.97]) by postgresql.org (Postfix) with ESMTP id 17BFD64FC0D for ; Tue, 23 Sep 2008 10:34:03 -0300 (ADT) Received: from mail-c189.authsmtp.com (mail-c189.authsmtp.com [62.13.128.71]) by punt5.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id m8NDXqNp094496; Tue, 23 Sep 2008 14:33:52 +0100 (BST) Received: from [192.168.0.3] (85-211-71-126.dyn.gotadsl.co.uk [85.211.71.126]) (authenticated bits=0) by mail.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id m8NDXocw036229; Tue, 23 Sep 2008 14:33:50 +0100 (BST) Subject: Re: hash index improving v3 From: Simon Riggs To: Tom Lane Cc: "Jonah H. Harris" , Alex Hunsaker , Kenneth Marshall , Xiao Meng , Zdenek Kotala , pgsql-patches@postgresql.org In-Reply-To: <26331.1222175594@sss.pgh.pa.us> References: <34d269d40809052249h59750f83v4b19a50d0555580@mail.gmail.com> <34d269d40809091823t204e252fl246443be71aed9ff@mail.gmail.com> <20080910130401.GG6714@it.is.rice.edu> <34d269d40809102049y2599a381kf409c0b5f18b6170@mail.gmail.com> <34d269d40809102117g470c4d25l55be7898fc43fc40@mail.gmail.com> <20080911152437.GM6714@it.is.rice.edu> <34d269d40809111951s7bac44cfk5dc9244693dd777c@mail.gmail.com> <20080912142921.GB26773@it.is.rice.edu> <7246.1221445012@sss.pgh.pa.us> <34d269d40809222025k2b2252dft89db471649c8eef8@mail.gmail.com> <36e682920809222030k49293e85jc57429296c5d2d76@mail.gmail.com> <17323.1222142759@sss.pgh.pa.us> <1222144262.4445.265.camel@ebony.2ndQuadrant> <18049.1222145316@sss.pgh.pa.us> <1222146936.4445.290.camel@ebony.2ndQuadrant> <25193.1222172194@sss.pgh.pa.us> <1222175115.4445.344.camel@ebony.2ndQuadrant> <26331.1222175594@sss.pgh.pa.us> Content-Type: text/plain Date: Tue, 23 Sep 2008 14:34:39 +0100 Message-Id: <1222176879.4445.361.camel@ebony.2ndQuadrant> Mime-Version: 1.0 X-Mailer: Evolution 2.12.0 Content-Transfer-Encoding: 7bit X-Server-Quench: 422a1c80-8974-11dd-bc7a-001f29070be2 X-AuthRoute: OCdxZQATClZOTQEd DAteCiNZVAwpPBRK HVkIKg5MJUcNSQVJ NksacRtFagZbflxB HGQLW1xEUVl7WmB/ bgsfZQBDYEtPQQJr TklLRE1QEQdtHhxP WxZgJnEtJEJGe3hy Z08sXHNTXAp+d0Qs Qx1cFXAObGBkdWEe BBRFJVBTIR5Kfh4T aFl/U3NfNGdJBC9q VzwLFgwaHRhkHWxV SxoNZVMUTUsbVj85 XwoFAzQpEgUbHE1p X-Authentic-SMTP: 61633235383639.kestrel.dmpriest.net.uk:1374/Kp X-Report-SPAM: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse X-Virus-Status: No virus detected - but ensure you scan with your own anti-virus system! X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200809/157 X-Sequence-Number: 7172 On Tue, 2008-09-23 at 09:13 -0400, Tom Lane wrote: > Simon Riggs writes: > > Thinks: Why not just sort all of the time and skip the debate entirely? > > The sort is demonstrably a loser for smaller indexes. Admittedly, > if the index is small then the sort can't cost all that much, but if > the (correct) threshold is some large fraction of shared_buffers then > it could still take awhile on installations with lots-o-buffers. The other realisation is that for large indexes, giving them more maintenance_work_mem probably will make them build faster 'cos we'll be sorting. So "give big indexes more memory" is still true *enough* to be broadly consistent, explainable and understandable. I do explain things in more detail on some courses, but pithy rules help busy people. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support