X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 44C94D1B541 for ; Tue, 11 Nov 2003 00:58:35 +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 23448-07 for ; Mon, 10 Nov 2003 20:58:06 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 94590D1B50C for ; Mon, 10 Nov 2003 20:58:03 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id B998135409; Mon, 10 Nov 2003 16:57:51 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id B375E353E8; Mon, 10 Nov 2003 16:57:51 -0800 (PST) Date: Mon, 10 Nov 2003 16:57:51 -0800 (PST) From: Stephan Szabo To: ow Cc: pgsql-sql@postgresql.org Subject: Re: pg 7.4.rc1, Range query performance In-Reply-To: <20031111004139.47333.qmail@web21407.mail.yahoo.com> Message-ID: <20031110165327.P34950@megazone.bigpanda.com> References: <20031111004139.47333.qmail@web21407.mail.yahoo.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/104 X-Sequence-Number: 15919 On Mon, 10 Nov 2003, ow wrote: > --- Stephan Szabo wrote: > > I'm not sure that AK_abc is the best index for check a range on a and > > single values on b and c. I'd think that something like an index > > on (b,c,a) would probably be better for this purpose (without doing any > > testing ;) ). > > That would not work for us since most of the time users are working with most > recent data, hence column "a" is the first in the index. Are you doing alot of queries where you're only searching on a? If so, then you may want to consider a second, non-unique index to speed up this sort of query. With a range query on a and fixed values on b and c, you're really not utilizing an index in that order efficiently.