From: Andreas Joseph Krogh <andreak@officenet.no>
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: index on to_char(created, 'YYYY') doesn't work
Date: Wed, 15 Jan 2003 17:38:00 +0000
Message-ID: <200301151738.06087.andreak@officenet.no> (raw)
In-Reply-To: <Pine.LNX.4.44.0301151759230.3008-100000@matrix.gatewaynet.com>
References: <Pine.LNX.4.44.0301151759230.3008-100000@matrix.gatewaynet.com>
-----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@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=
=sPIN
-----END PGP SIGNATURE-----
Attachments:explain.txt (text/plain, 1.1K) download
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=>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreak@officenet.no, achill@matrix.gatewaynet.com
Subject: Re: index on to_char(created, 'YYYY') doesn't work
In-Reply-To: <200301151738.06087.andreak@officenet.no>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox