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 46EF63A1AF3 for ; Fri, 14 Jan 2005 18:34:05 +0000 (GMT) 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 00642-01 for ; Fri, 14 Jan 2005 18:33:46 +0000 (GMT) Received: from smtp05.mrf.mail.rcn.net (smtp05.mrf.mail.rcn.net [207.172.4.64]) by svr1.postgresql.org (Postfix) with ESMTP id 598073A1A7D for ; Fri, 14 Jan 2005 18:33:46 +0000 (GMT) Received: from 24-148-30-138.lem-bsr1.chi-lem.il.cable.rcn.com (HELO [192.168.1.102]) (24.148.30.138) by smtp05.mrf.mail.rcn.net with ESMTP; 14 Jan 2005 13:34:36 -0500 X-BrightmailFiltered: true X-Brightmail-Tracker: AAAAAA== X-IronPort-AV: i="3.88,125,1102309200"; d="scan'208"; a="1129507:sNHT19550024" From: Adrian Holovaty To: pgsql-performance@postgresql.org Subject: Index on a function and SELECT DISTINCT Date: Fri, 14 Jan 2005 12:32:12 -0600 User-Agent: KMail/1.7.2 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200501141232.13139.postgresql@holovaty.com> X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200501/191 X-Sequence-Number: 9881 If I have this table, function and index in Postgres 7.3.6 ... """ CREATE TABLE news_stories ( id serial primary key NOT NULL, pub_date timestamp with time zone NOT NULL, ... ) CREATE OR REPLACE FUNCTION get_year_trunc(timestamp with time zone) returns timestamp with time zone AS 'SELECT date_trunc(\'year\',$1);' LANGUAGE 'SQL' IMMUTABLE; CREATE INDEX news_stories_pub_date_year_trunc ON news_stories( get_year_trunc(pub_date) ); """ ...why does this query not use the index? db=# EXPLAIN SELECT DISTINCT get_year_trunc(pub_date) FROM news_stories; QUERY PLAN --------------------------------------------------------------------------------- Unique (cost=59597.31..61311.13 rows=3768 width=8) -> Sort (cost=59597.31..60454.22 rows=342764 width=8) Sort Key: date_trunc('year'::text, pub_date) -> Seq Scan on news_stories (cost=0.00..23390.55 rows=342764 width=8) (4 rows) The query is noticably slow (2 seconds) on a database with 150,000+ records. How can I speed it up? Thanks, Adrian