From: Andreas Joseph Krogh <andreak@officenet.no>
Organization: OfficeNet AS
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Subject: Re: index on to_char(created, 'YYYY') doesn't work
Date: Wed, 15 Jan 2003 17:38:00 +0000
User-Agent: KMail/1.5
References: <Pine.LNX.4.44.0301151759230.3008-100000@matrix.gatewaynet.com>
In-Reply-To: <Pine.LNX.4.44.0301151759230.3008-100000@matrix.gatewaynet.com>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: Multipart/Mixed;
  boundary="Boundary-00=_4xZJ+jNqMRMtQzF"
Message-Id: <200301151738.06087.andreak@officenet.no>

--Boundary-00=_4xZJ+jNqMRMtQzF
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Description: clearsigned data
Content-Disposition: inline

-----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) =3D '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=
=20
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=3D> select count(*) from session;
 count
- --------
 899691
(1 row)

nbeweb=3D> 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=20
the time is spent sorting and grouping. Are there any ways to optimize this?

- --=20
Andreas Joseph Krogh <andreak@officenet.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=3D
=3DsPIN
-----END PGP SIGNATURE-----

--Boundary-00=_4xZJ+jNqMRMtQzF
Content-Type: text/plain;
  charset="iso-8859-1";
  name="explain.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="explain.txt"

nbeweb=> EXPLAIN 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;
NOTICE:  QUERY PLAN:

Aggregate  (cost=92.62..92.74 rows=2 width=154)
  ->  Group  (cost=92.62..92.68 rows=22 width=154)
        ->  Sort  (cost=92.62..92.62 rows=22 width=154)
              ->  Index Scan using session_u_idx on session  (cost=0.00..92.12 rows=22 width=154)

EXPLAIN
nbeweb=> EXPLAIN ANALYZE 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;
NOTICE:  QUERY PLAN:

Aggregate  (cost=92.62..92.74 rows=2 width=154) (actual time=764457.99..837660.30 rows=36 loops=1)
  ->  Group  (cost=92.62..92.68 rows=22 width=154) (actual time=763934.48..830755.34 rows=846053 loops=1)
        ->  Sort  (cost=92.62..92.62 rows=22 width=154) (actual time=763934.45..798906.16 rows=846053 loops=1)
              ->  Index Scan using session_u_idx on session  (cost=0.00..92.12 rows=22 width=154) (actual time=140.71..253768.07 rows=846053 loops=1)
Total runtime: 837759.64 msec

EXPLAIN
nbeweb=>

--Boundary-00=_4xZJ+jNqMRMtQzF--