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 7E943329E8B for ; Wed, 6 Oct 2004 17:08:43 +0100 (BST) 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 55100-07 for ; Wed, 6 Oct 2004 16:08:02 +0000 (GMT) Received: from mail.hamburg.cityline.net (ns.mcs-hh.de [194.77.146.129]) by svr1.postgresql.org (Postfix) with ESMTP id 4D706329E4B for ; Wed, 6 Oct 2004 17:08:01 +0100 (BST) Received: from [212.1.56.10] (helo=aconitin.toxine.lan) by mail.hamburg.cityline.net with asmtp (TLSv1:RC4-MD5:128) (Exim 4.32) id 1CFEKb-00073J-4j for pgsql-performance@postgresql.org; Wed, 06 Oct 2004 18:07:57 +0200 From: Ole Langbehn Organization: freiheit.com To: pgsql-performance@postgresql.org Subject: Re: sequential scan on select distinct Date: Wed, 6 Oct 2004 18:09:43 +0200 User-Agent: KMail/1.7 References: <200410061130.58625.ole@freiheit.com> In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200410061809.43549.ole@freiheit.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/60 X-Sequence-Number: 8536 Am Mittwoch, 6. Oktober 2004 12:19 schrieb Pierre-Fr=E9d=E9ric Caillaud: > You could try : > > explain analyze select "land" from "customer_dim" group by "land"; > It will be a lot faster but I can't make it use the index on my machine.= .. this already speeds up my queries to about 1/4th of the time, which is abou= t=20 the range of mysql and oracle. > > Example : > > [..] > > Hum hum ! Again, a lot better ! > Index scan backwards seems a lot faster than index scan forwards. Why, I > don't know, but here you go from 15 seconds to 14 milliseconds... thanks for this very extensive answer, it helped me a lot. > > I don't know WHY (oh why) postgres does not use this kind of strategy > when distinct'ing an indexed field... Anybody got an idea ? That's the big question I still would like to see answered too. Can anyone= =20 tell us? TiA --=20 Ole Langbehn