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 A8665329EAD for ; Wed, 29 Sep 2004 20:41:36 +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 75996-07 for ; Wed, 29 Sep 2004 19:41:32 +0000 (GMT) Received: from aari.shi.ar (shiar.net [213.84.17.17]) by svr1.postgresql.org (Postfix) with ESMTP id 3DBC832A0D9 for ; Wed, 29 Sep 2004 20:41:33 +0100 (BST) Received: by aari.shi.ar (Postfix, from userid 1000) id 6D6E031285; Wed, 29 Sep 2004 21:41:31 +0200 (CEST) Date: Wed, 29 Sep 2004 21:41:31 +0200 From: Shiar To: pgsql-performance@postgresql.org Subject: index not used when using function Message-ID: <20040929194131.GH22917@shiar.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline X-Mailer: Mutt 1.5.4i + Vim 6.2 (Debian Sid GNU/Linux 2.6.2) X-Face: VcS>5pP95[crO3y%~/D\dP`^QY|ah,r=QQSNBrO%EA#21cv.`uXP^w{xH^/BJM% 'r:6P~{Nx\5Rrr\r1C[B{{ZbP9g}T1pG\9l%QIe@9):fgn-(ma.hb~n{LUmXRA 4%/z+K; dzq@{C=VXd$NAv<|sy!r1?; eYKcRjgzC_f(%O6@#Zxb7gJIX4QK{!'$=H_d<7rB X-URL: http://shiar.org/ X-Accept-Language: nl, eo, en 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: 200409/330 X-Sequence-Number: 8458 Hi all, a small question: I've got this table "songs" and an index on column artist. Since there's about one distinct artist for every 10 rows, it would be nice if it could use this index when counting artists. It doesn't however: lyrics=> EXPLAIN ANALYZE SELECT count(DISTINCT artist) FROM songs; Aggregate (cost=31961.26..31961.26 rows=1 width=14) (actual time=808.863..808.864 rows=1 loops=1) -> Seq Scan on songs (cost=0.00..31950.41 rows=4341 width=14) (actual time=26.801..607.172 rows=25207 loops=1) Total runtime: 809.106 ms Even with enable_seqscan to off, it just can't seem to use the index. The same query without the count() works just fine: lyrics=> EXPLAIN ANALYZE SELECT DISTINCT artist FROM songs; Unique (cost=0.00..10814.96 rows=828 width=14) (actual time=0.029..132.903 rows=3280 loops=1) -> Index Scan using songs_artist_key on songs (cost=0.00..10804.11 rows=4341 width=14) (actual time=0.027..103.448 rows=25207 loops=1) Total runtime: 135.697 ms Of course I can just take the number of rows from the latter query, but I'm still wondering why it can't use indexes with functions. Thanks -- Shiar - http://www.shiar.org > Faktoj estas malamik del verajh