Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 40A82476608 for ; Wed, 15 Jan 2003 11:19:59 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id BDC0A476F02 for ; Wed, 15 Jan 2003 10:57:19 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h0FFvI5u005115; Wed, 15 Jan 2003 10:57:19 -0500 (EST) To: Andreas Joseph Krogh Cc: Achilleus Mantzios , pgsql-sql@postgresql.org Subject: Re: index on to_char(created, 'YYYY') doesn't work In-reply-to: <200301151504.47092.andreak@officenet.no> References: <200301151504.47092.andreak@officenet.no> Comments: In-reply-to Andreas Joseph Krogh message dated "Wed, 15 Jan 2003 15:04:46 +0000" Date: Wed, 15 Jan 2003 10:57:18 -0500 Message-ID: <5114.1042646238@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/219 X-Sequence-Number: 11084 Andreas Joseph Krogh writes: > create index session_u_idx on session (drus(created)) where username is null; > select to_char(created, 'IW') as week, count(session_id) from session WHERE > username IS NULL and drus(created) = '2002' group by week ORDER BY week; > This table is quite large(900 000 rows) and the query takes several > minutes to run, which makes the browser timeout. Is the query actually using the index? It looks like it should, but there's no substitute for making sure. May we see the output of EXPLAIN ANALYZE for this query? How does it change if you force indexscan or force seqscan (by setting enable_seqscan or enable_indexscan to 0 respectively)? > Is the use of to_char on the timestamp wrong I'd be inclined to write cast(extract(year from created) as int) so that the index key is int rather than varchar --- but this is probably just a marginal efficiency hack. The real problem is very likely that the query selects such a large fraction of the table rows that the index isn't buying you anything. regards, tom lane