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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wednesday 15 January 2003 20:02, you wrote:
> You didnt try it!!
>
> Change your to_char(created, ''YYYY'')||$2 to
> to_char(created, ''YYYY'')||(coalesce($2,'''')
> (provided there is no user named mister '' :)
>
> then perform your query like:
>
> select to_char(created, 'IW') as week, count(session_id) from session
> WHERE drus(created,username) = '2002' group by week ORDER BY
> week;
>
> do a explain analyze to see index and performance issues.

I didn't try it because I don't have a problem with the optimizer utilizing
the index anymore. As you can se in the attachment the index is used.

Quoting Tom Lane:
"he 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."

nbeweb=> select count(*) from session;
count
- --------
899691
(1 row)

nbeweb=> select count(*) from session where username IS NULL;
count
- --------
898377
(1 row)

The output of EXPLAIN and EXPLAIN ANALYZE is in the attachment.

Can anyone explain to me how to reed the output from ANALYZE. It seems most of
the time is spent sorting and grouping. Are there any ways to optimize this?

- --
Andreas Joseph Krogh <andreak(at)officenet(dot)no>
There will always be someone who agrees with you
but is, inexplicably, a moron.

gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE+JZx+UopImDh2gfQRAjfJAKCv4uXE2PhtmWfCvm/6pRkumfM8KACgmeDF
AX9HeKVu9SErXxpaUh9ys4A=
=sPIN
-----END PGP SIGNATURE-----