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 4B882329F04 for ; Wed, 6 Oct 2004 10:29:27 +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 30492-01 for ; Wed, 6 Oct 2004 09:29:15 +0000 (GMT) Received: from mail.hamburg.cityline.net (ns.mcs-hh.de [194.77.146.129]) by svr1.postgresql.org (Postfix) with ESMTP id 8863532A188 for ; Wed, 6 Oct 2004 10:29:15 +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 1CF86j-0004rG-Si for pgsql-performance@postgresql.org; Wed, 06 Oct 2004 11:29:13 +0200 From: Ole Langbehn Organization: freiheit.com To: pgsql-performance@postgresql.org Subject: sequential scan on select distinct Date: Wed, 6 Oct 2004 11:30:58 +0200 User-Agent: KMail/1.7 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200410061130.58625.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/54 X-Sequence-Number: 8530 Hi, I'm using Postgres 7.4.5. Tables are analyzed & vacuumed. I am wondering why postgresql never uses an index on queries of the type 'select distinct ...' while e.g. mysql uses the index on the same query. See the following explains: postgresql: explain analyze select distinct "land" from "customer_dim"; ---------------------------------------------------------------------------= ------------------------------------------------------------+ QUERY PLAN = | ---------------------------------------------------------------------------= ------------------------------------------------------------+ Unique (cost=3D417261.85..430263.66 rows=3D18 width=3D15) (actual time=3D= 45875.235..67204.694 rows=3D103 loops=3D1) | -> Sort (cost=3D417261.85..423762.75 rows=3D2600362 width=3D15) (actua= l time=3D45875.226..54114.473 rows=3D2600362 loops=3D1) | Sort Key: land = | -> Seq Scan on customer_dim (cost=3D0.00..84699.62 rows=3D260036= 2 width=3D15) (actual time=3D0.048..10733.227 rows=3D2600362 loops=3D1) | Total runtime: 67246.465 ms = | ---------------------------------------------------------------------------= ------------------------------------------------------------+ mysql: explain select DISTINCT `customer_dim`.`land` from `customer_dim`; --------------+-------+---------------+---------------+---------+--------+-= --------+-------------+ table | type | possible_keys | key | key_len | ref | = rows | Extra | --------------+-------+---------------+---------------+---------+--------+-= --------+-------------+ customer_dim | index | [NULL] | IDX_cstd_land | 81 | [NULL] | = 2600362 | Using index | --------------+-------+---------------+---------------+---------+--------+-= --------+-------------+ 1 row in result (first row: 8 msec; total: 9 msec) The result set contains 103 rows (but i get this behavior with every query = of this kind). My tables consist of at least a million rows. The indexes on the column 'land' are standard indexes, so in case of postgresql, it's a btree-index. I've tried to change the index type, but to= no avail. So, why doesn't postgresql use the index, and (how) could i persuade postgr= esql to use an index for this type of query? TiA --=20 Ole Langbehn freiheit.com technologies gmbh Theodorstr. 42-90 / 22761 Hamburg, Germany fon =A0 =A0 =A0 +49 (0)40 / 890584-0 fax =A0 =A0 =A0 +49 (0)40 / 890584-20 Freie Software durch B=FCcherkauf f=F6rdern | http://bookzilla.de/