X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B404AD1C4C3 for ; Wed, 28 Jul 2004 00:24:16 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 99980-08 for ; Wed, 28 Jul 2004 03:24:13 +0000 (GMT) Received: from www.postgresql.com (www.postgresql.com [200.46.204.209]) by svr1.postgresql.org (Postfix) with ESMTP id 18145D1C92A for ; Wed, 28 Jul 2004 00:21:33 -0300 (ADT) Received: from mail.logi-track.com (burro.logi-track.com [213.239.193.212]) by www.postgresql.com (Postfix) with ESMTP id 135A4CF6957 for ; Tue, 27 Jul 2004 11:51:10 -0300 (ADT) Received: from kingfisher.intern.logi-track.com (G6110.g.pppool.de [80.185.97.16]) by mail.logi-track.com (Postfix) with ESMTP id BF9BE30197; Tue, 27 Jul 2004 14:57:02 +0000 (UTC) Received: from kingfisher.intern.logi-track.com (localhost [127.0.0.1]) by kingfisher.intern.logi-track.com (Postfix) with SMTP id 34541AB2D0; Tue, 27 Jul 2004 16:50:45 +0200 (CEST) Date: Tue, 27 Jul 2004 16:50:44 +0200 From: Markus Schaber To: "Merlin Moncure" Cc: Subject: Re: best way to fetch next/prev record based on index Message-Id: <20040727165044.0549286e@kingfisher.intern.logi-track.com> In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB34101AEF8@Herge.rcsinc.local> References: <6EE64EF3AB31D5448D0007DD34EEB34101AEF8@Herge.rcsinc.local> Organization: logi-track ag, =?ISO-8859-15?Q?z=FCrich?= X-Mailer: Sylpheed-Claws 0.9.12 (GTK+ 1.2.10; i386-pc-linux-gnu) X-Face: Nx5T&>Nj$VrVPv}sC3IL&)TqHHOKCz/|)R$i"*r@w0{*I6w; UNU_hdl1J4NI_m{IMztq=>cmM}1gCLbAF+9\#CGkG8}Y{x%SuQ>1#t:; Z(|\qdd[i]HStki~#w1$TPF}:0w-7"S\Ev|_a$K wrote: > The basic problem is the planner can't always match the query to the > index. So, either the planner has to be helped/fixed or I have to > explore another solution. This seems to happen most when the 'a' > column has very poor selectivity. In this case, the planner will only > examine the 'a' part of the key. So it may help to add some more indices so you have an index for all permut= ations, Create an index on (a,b,c), (a,c,b), (b,c,a), (b,a,c), (c,a,b) and (c,b,a). So as long as one of the rows has enough selectivity, the planner should be able to select the correct index. Maybe increasing the number of random samples for the rows is useful. HTH, Markus --=20 markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 z=FCrich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:schabios@logi-track.com | www.logi-track.com