Re: index on to_char(created, 'YYYY') doesn't work

Andreas Joseph Krogh <andreak(at)officenet(dot)no> 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