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 D85B23A3DCE for ; Mon, 17 Jan 2005 18:01:16 +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 33273-02 for ; Mon, 17 Jan 2005 18:01:01 +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 90B8B3A3DD5 for ; Mon, 17 Jan 2005 18:01:00 +0000 (GMT) Received: from 207-229-189-211.lem-bsr1.chi-lem.il.cable.rcn.com (HELO [192.168.1.101]) (207.229.189.211) by smtp05.mrf.mail.rcn.net with ESMTP; 17 Jan 2005 13:02:33 -0500 X-BrightmailFiltered: true X-Brightmail-Tracker: AAAAAA== X-IronPort-AV: i="3.88,132,1102309200"; d="scan'208"; a="1402092:sNHT62107506" From: Adrian Holovaty To: pgsql-performance@postgresql.org Subject: Re: Index on a function and SELECT DISTINCT Date: Mon, 17 Jan 2005 11:59:24 -0600 User-Agent: KMail/1.7.2 References: <200501141232.13139.postgresql@holovaty.com> <20050117100942.692be97a.frank@wiles.org> In-Reply-To: <20050117100942.692be97a.frank@wiles.org> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200501171159.24797.postgresql@holovaty.com> X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200501/209 X-Sequence-Number: 9899 Frank Wiles wrote: > Adrian Holovaty wrote: > > 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? > > It's doing a sequence scan because you're not limiting the query in > the FROM clause. No point in using an index when you're asking for > the entire table. :) Ah, that makes sense. So is there a way to optimize SELECT DISTINCT queries that have no WHERE clause? Adrian