X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 883B09DCBEC for ; Tue, 6 Dec 2005 16:46:32 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 89828-05 for ; Tue, 6 Dec 2005 16:46:28 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id 1E27C9DD624 for ; Tue, 6 Dec 2005 16:46:24 -0400 (AST) Received: (qmail 24887 invoked by uid 500); 6 Dec 2005 20:50:48 -0000 Date: Tue, 6 Dec 2005 14:50:48 -0600 From: Bruno Wolff III To: Csaba Nagy Cc: Joost Kraaijeveld , Pgsql-Performance Subject: Re: Can this query go faster??? Message-ID: <20051206205048.GB22168@wolff.to> Mail-Followup-To: Bruno Wolff III , Csaba Nagy , Joost Kraaijeveld , Pgsql-Performance References: <1133862229.8837.61.camel@Panoramix> <1133862777.4779.185.camel@coppola.muc.ecircle.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1133862777.4779.185.camel@coppola.muc.ecircle.de> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.015 required=5 tests=[AWL=0.015] X-Spam-Score: 0.015 X-Spam-Level: X-Archive-Number: 200512/191 X-Sequence-Number: 16012 On Tue, Dec 06, 2005 at 10:52:57 +0100, Csaba Nagy wrote: > Joost, > > Why do you use an offset here ? I guess you're traversing the table > somehow, in this case it would be better to remember the last zipcode + > housenumber and put an additional condition to get the next bigger than > the last one you've got... that would go for the index on > zipcode+housenumber and be very fast. The big offset forces postgres to > traverse that many entries until it's able to pick the one row for the > result... The other problem with saving an offset, is unless the data isn't changing or you are doing all of the searches in one serialized transaction, the fixed offset might not put you back where you left off. Using the last key, instead of counting records is normally a better way to do this.