-----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 <[EMAIL PROTECTED]>
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-----
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=>
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
