pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedindex on to_char(created, 'YYYY') doesn't work
11+ messages / 5 participants
[nested] [flat]
* index on to_char(created, 'YYYY') doesn't work
@ 2003-01-15 11:37 Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 1 reply; 11+ messages in thread
From: Andreas Joseph Krogh @ 2003-01-15 11:37 UTC (permalink / raw)
To: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
The following does not work:
create index session_u_idx on session (to_char(created, 'YYYY'));
ERROR: parser: parse error at or near "'YYYY'" at character 57
Can I make a function to do this and index using the result of that funtion?
Do anyone have an example of such a function?
- --
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+JUfhUopImDh2gfQRAme7AJ4jDB+e97rvEicGrxBniD1ddQ1gZgCfbbGl
azbrt7/+xGJUuLSQC7fF+vQ=
=3pKN
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: index on to_char(created, 'YYYY') doesn't work
@ 2003-01-15 11:52 Andreas Joseph Krogh <andreak@officenet.no>
parent: Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 1 reply; 11+ messages in thread
From: Andreas Joseph Krogh @ 2003-01-15 11:52 UTC (permalink / raw)
To: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Wednesday 15 January 2003 11:37, you wrote:
> The following does not work:
>
> create index session_u_idx on session (to_char(created, 'YYYY'));
> ERROR: parser: parse error at or near "'YYYY'" at character 57
>
> Can I make a function to do this and index using the result of that
> funtion? Do anyone have an example of such a function?
I tried the following function:
- -----------------------------------------------------------------
create function drus (timestamp) returns varchar AS'
DECLARE
str_created VARCHAR;
created ALIAS FOR $1;
BEGIN
str_created:= to_char(created, ''YYYY'');
RETURN str_created;
END;
' LANGUAGE 'plpgsql';
create index session_u_idx on session (drus(created));
- -----------------------------------------------------------------
But it failes with:
ERROR: DefineIndex: index function must be marked isImmutable
Now the question is how do I mark an index function isImmutable?
- --
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+JUtlUopImDh2gfQRAl1XAKCkEDKxRDys/Di1gLLRDx6h0TGiPwCeI4FN
DNdajyaQTd27f8MeaWZ+xUE=
=T3we
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: index on to_char(created, 'YYYY') doesn't work
@ 2003-01-15 12:08 Andreas Joseph Krogh <andreak@officenet.no>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 11+ messages in thread
From: Andreas Joseph Krogh @ 2003-01-15 12:08 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Wednesday 15 January 2003 16:12, you wrote:
> On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > On Wednesday 15 January 2003 11:37, you wrote:
> > > The following does not work:
> > >
> > > create index session_u_idx on session (to_char(created, 'YYYY'));
> > > ERROR: parser: parse error at or near "'YYYY'" at character 57
> > >
> > > Can I make a function to do this and index using the result of that
> > > funtion? Do anyone have an example of such a function?
> >
> > I tried the following function:
> > - -----------------------------------------------------------------
> > create function drus (timestamp) returns varchar AS'
> > DECLARE
> > str_created VARCHAR;
> > created ALIAS FOR $1;
> > BEGIN
> > str_created:= to_char(created, ''YYYY'');
> > RETURN str_created;
> > END;
> > ' LANGUAGE 'plpgsql';
>
> add
> WITH (iscachable)
Thank you, not _that_ works:-)
But now this doesn't work:
create index session_u_idx on session (drus(created), username);
Can't I have multicolumn-indexes with functions? Any idea how to rewrite that
so it works?
Here is my session table:
CREATE TABLE session (
session_id varchar(256) NOT NULL PRIMARY KEY,
created timestamp DEFAULT 'now' NOT NULL,
last_accessed timestamp NOT NULL,
destroyed timestamp NOT NULL,
username varchar -- Allow sessions from not logged in users
);
Here is my query I wish to optimize using indexes:
SELECT to_char(created, 'IW') as week, count(session_id) from session WHERE
username IS NULL and to_char(created, 'YYYY') = '2002' group by week ORDER BY
week;
Any hints on optimizing this query, index-usage etc?
- --
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+JU8hUopImDh2gfQRAuA5AKCXyqCZk92d6oCgyJ/Auf8c4xkSaQCgr4Lq
/+r2WSydbYWXNomMvbmt2E8=
=N6NQ
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: index on to_char(created, 'YYYY') doesn't work
@ 2003-01-15 15:04 Andreas Joseph Krogh <andreak@officenet.no>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 11+ messages in thread
From: Andreas Joseph Krogh @ 2003-01-15 15:04 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Wednesday 15 January 2003 18:55, Achilleus Mantzios wrote:
> On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > On Wednesday 15 January 2003 16:12, you wrote:
> > > On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote:
> > > > -----BEGIN PGP SIGNED MESSAGE-----
> > > > Hash: SHA1
> > > >
> > > > On Wednesday 15 January 2003 11:37, you wrote:
> > > > > The following does not work:
> > > > >
> > > > > create index session_u_idx on session (to_char(created, 'YYYY'));
> > > > > ERROR: parser: parse error at or near "'YYYY'" at character 57
> > > > >
> > > > > Can I make a function to do this and index using the result of that
> > > > > funtion? Do anyone have an example of such a function?
> > > >
> > > > I tried the following function:
> > > > - -----------------------------------------------------------------
> > > > create function drus (timestamp) returns varchar AS'
> > > > DECLARE
> > > > str_created VARCHAR;
> > > > created ALIAS FOR $1;
> > > > BEGIN
> > > > str_created:= to_char(created, ''YYYY'');
> > > > RETURN str_created;
> > > > END;
> > > > ' LANGUAGE 'plpgsql';
> > >
> > > add
> > > WITH (iscachable)
> >
> > Thank you, not _that_ works:-)
> > But now this doesn't work:
> > create index session_u_idx on session (drus(created), username);
>
> Functinal indexes are single column indexes.
>
> Why dont you change your function to:
>
> create function drus (timestamp,varchar) returns varchar A
>
> and return the concatenation of to_char(created, ''YYYY'')||$2
>
> and then create the index as usual (passing the date and the username
> as params to your function)????
OK, thank you.
Let me explain what I want to do:
I have the following schema for gathering statistics from the web:
CREATE TABLE session (
session_id varchar(256) NOT NULL PRIMARY KEY,
created timestamp DEFAULT 'now' NOT NULL,
last_accessed timestamp NOT NULL,
destroyed timestamp NOT NULL,
username varchar -- Allow sessions from not logged in users
);
create or replace function drus (timestamp) returns varchar AS'
DECLARE
str_created VARCHAR;
created ALIAS FOR $1;
BEGIN
str_created:= to_char(created, ''YYYY'');
RETURN str_created;
END;
' LANGUAGE 'plpgsql' WITH (iscachable);
create index session_u_idx on session (drus(created)) where username is null;
Now I want to get statistics for number of hits pr. week where users are not
lnogged in(username IS NULL) for the year 2002:
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;
week | count
- ------+-------
01 | 6321
18 | 74
19 | 12153
20 | 17125
21 | 22157
22 | 25316
23 | 24265
24 | 26234
25 | 28583
26 | 29156
27 | 28335
28 | 23587
29 | 23203
This table is quite large(900 000 rows) and the query takes several minutes to
run, which makes the browser timeout.
Do I have a design-issue here, should I rather batch-generate the stats in its
own table so I don't have to process all the data(900 000 rows) each time.
Is there any way to optimize/rewrite this query? Is the use of to_char on the
timestamp wrong, should I use another comparation method for getting the year
2002?
- --
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+JXiPUopImDh2gfQRAlf/AJ9xlcUDqa7NcXghtse8PAqQxkf1lACdEGxH
vBXYxoFZnS6J35iQGw+14wE=
=xCVY
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: index on to_char(created, 'YYYY') doesn't work
@ 2003-01-15 15:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andreas Joseph Krogh <andreak@officenet.no>
1 sibling, 0 replies; 11+ messages in thread
From: Tom Lane @ 2003-01-15 15:57 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: Achilleus Mantzios <achill@matrix.gatewaynet.com>; pgsql-sql
Andreas Joseph Krogh <andreak@officenet.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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: index on to_char(created, 'YYYY') doesn't work
@ 2003-01-15 16:12 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 1 reply; 11+ messages in thread
From: Achilleus Mantzios @ 2003-01-15 16:12 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql
On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Wednesday 15 January 2003 11:37, you wrote:
> > The following does not work:
> >
> > create index session_u_idx on session (to_char(created, 'YYYY'));
> > ERROR: parser: parse error at or near "'YYYY'" at character 57
> >
> > Can I make a function to do this and index using the result of that
> > funtion? Do anyone have an example of such a function?
>
> I tried the following function:
> - -----------------------------------------------------------------
> create function drus (timestamp) returns varchar AS'
> DECLARE
> str_created VARCHAR;
> created ALIAS FOR $1;
> BEGIN
> str_created:= to_char(created, ''YYYY'');
> RETURN str_created;
> END;
> ' LANGUAGE 'plpgsql';
add
WITH (iscachable)
>
> create index session_u_idx on session (drus(created));
> - -----------------------------------------------------------------
> But it failes with:
> ERROR: DefineIndex: index function must be marked isImmutable
>
> Now the question is how do I mark an index function isImmutable?
>
> - --
> 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+JUtlUopImDh2gfQRAl1XAKCkEDKxRDys/Di1gLLRDx6h0TGiPwCeI4FN
> DNdajyaQTd27f8MeaWZ+xUE=
> =T3we
> -----END PGP SIGNATURE-----
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: index on to_char(created, 'YYYY') doesn't work
@ 2003-01-15 17:19 Steve Crawford <scrawford@pinpointresearch.com>
parent: Andreas Joseph Krogh <andreak@officenet.no>
1 sibling, 1 reply; 11+ messages in thread
From: Steve Crawford @ 2003-01-15 17:19 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
<disclaimer>I don't have "real-life" experience with partial
indexes</disclaimer> but...
You probably won't see an increase in speed unless the index use can get you
down to a really small fraction of your total row count (I don't know just
how small but remember being surprised at the number but realizing, on
reflection, that it made sense. It was something like 10% or less).
One thing you could try is to use a partial index (one containing only the
rows in which you are interested).
Here's a really dumb example:
create index foo on session (username) where username is null and
to_char(created, 'YYYY') = '2002';
Why dumb? Because the index will only contain nulls. You could probably
choose a more intelligent index based on the other queries you do. Still,
this index could increase your query speed considerably (as long as your
where in creating the index matches the where in your query - if you change
your query to 2003 you will be back to a sequential scan).
BTW, I tried to create an index on the to_char function and had no luck -
seems like it should work but it doesn't on 7.2.3 or 7.3.1.
Cheers,
Steve
On Wednesday 15 January 2003 4:08 am, Andreas Joseph Krogh wrote:
> On Wednesday 15 January 2003 16:12, you wrote:
> > On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote:
> > > -----BEGIN PGP SIGNED MESSAGE-----
> > > Hash: SHA1
> > >
> > > On Wednesday 15 January 2003 11:37, you wrote:
> > > > The following does not work:
> > > >
> > > > create index session_u_idx on session (to_char(created, 'YYYY'));
> > > > ERROR: parser: parse error at or near "'YYYY'" at character 57
> > > >
> > > > Can I make a function to do this and index using the result of that
> > > > funtion? Do anyone have an example of such a function?
> > >
> > > I tried the following function:
> > > - -----------------------------------------------------------------
> > > create function drus (timestamp) returns varchar AS'
> > > DECLARE
> > > str_created VARCHAR;
> > > created ALIAS FOR $1;
> > > BEGIN
> > > str_created:= to_char(created, ''YYYY'');
> > > RETURN str_created;
> > > END;
> > > ' LANGUAGE 'plpgsql';
> >
> > add
> > WITH (iscachable)
>
> Thank you, not _that_ works:-)
> But now this doesn't work:
> create index session_u_idx on session (drus(created), username);
>
> Can't I have multicolumn-indexes with functions? Any idea how to rewrite
> that so it works?
> Here is my session table:
> CREATE TABLE session (
> session_id varchar(256) NOT NULL PRIMARY KEY,
> created timestamp DEFAULT 'now' NOT NULL,
> last_accessed timestamp NOT NULL,
> destroyed timestamp NOT NULL,
> username varchar -- Allow sessions from not logged in users
> );
>
> Here is my query I wish to optimize using indexes:
> SELECT to_char(created, 'IW') as week, count(session_id) from session WHERE
> username IS NULL and to_char(created, 'YYYY') = '2002' group by week ORDER
> BY week;
>
> Any hints on optimizing this query, index-usage etc?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: index on to_char(created, 'YYYY') doesn't work
@ 2003-01-15 17:38 Andreas Joseph Krogh <andreak@officenet.no>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 11+ messages in thread
From: Andreas Joseph Krogh @ 2003-01-15 17:38 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
-----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=>
* Re: index on to_char(created, 'YYYY') doesn't work
@ 2003-01-15 18:15 Josh Berkus <josh@agliodbs.com>
parent: Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 0 replies; 11+ messages in thread
From: Josh Berkus @ 2003-01-15 18:15 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; Andreas Joseph Krogh <andreak@officenet.no>; Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
Steve,
> BTW, I tried to create an index on the to_char function and had no
> luck -
> seems like it should work but it doesn't on 7.2.3 or 7.3.1.
That's because functional indexes can't take any arguments other than
column names. Therefore you'd need to:
CREATE FUNCTION to_year (timestamp) RETURNS varchar AS
'SELECT to_char($1, ''YYYY'');'
LANGUAGE 'sql' WITH (ISCACHABLE, ISSTRICT)
(above is 7.2.3 syntax)
Then use the to_year function in place of to_char for creating your
index.
-Josh Berkus
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: index on to_char(created, 'YYYY') doesn't work
@ 2003-01-15 18:55 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Andreas Joseph Krogh <andreak@officenet.no>
1 sibling, 1 reply; 11+ messages in thread
From: Achilleus Mantzios @ 2003-01-15 18:55 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql
On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Wednesday 15 January 2003 16:12, you wrote:
> > On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote:
> > > -----BEGIN PGP SIGNED MESSAGE-----
> > > Hash: SHA1
> > >
> > > On Wednesday 15 January 2003 11:37, you wrote:
> > > > The following does not work:
> > > >
> > > > create index session_u_idx on session (to_char(created, 'YYYY'));
> > > > ERROR: parser: parse error at or near "'YYYY'" at character 57
> > > >
> > > > Can I make a function to do this and index using the result of that
> > > > funtion? Do anyone have an example of such a function?
> > >
> > > I tried the following function:
> > > - -----------------------------------------------------------------
> > > create function drus (timestamp) returns varchar AS'
> > > DECLARE
> > > str_created VARCHAR;
> > > created ALIAS FOR $1;
> > > BEGIN
> > > str_created:= to_char(created, ''YYYY'');
> > > RETURN str_created;
> > > END;
> > > ' LANGUAGE 'plpgsql';
> >
> > add
> > WITH (iscachable)
>
> Thank you, not _that_ works:-)
> But now this doesn't work:
> create index session_u_idx on session (drus(created), username);
Functinal indexes are single column indexes.
Why dont you change your function to:
create function drus (timestamp,varchar) returns varchar A
and return the concatenation of to_char(created, ''YYYY'')||$2
and then create the index as usual (passing the date and the username
as params to your function)????
>
> Can't I have multicolumn-indexes with functions? Any idea how to rewrite that
> so it works?
> Here is my session table:
> CREATE TABLE session (
> session_id varchar(256) NOT NULL PRIMARY KEY,
> created timestamp DEFAULT 'now' NOT NULL,
> last_accessed timestamp NOT NULL,
> destroyed timestamp NOT NULL,
> username varchar -- Allow sessions from not logged in users
> );
>
> Here is my query I wish to optimize using indexes:
> SELECT to_char(created, 'IW') as week, count(session_id) from session WHERE
> username IS NULL and to_char(created, 'YYYY') = '2002' group by week ORDER BY
> week;
>
> Any hints on optimizing this query, index-usage etc?
>
> - --
> 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+JU8hUopImDh2gfQRAuA5AKCXyqCZk92d6oCgyJ/Auf8c4xkSaQCgr4Lq
> /+r2WSydbYWXNomMvbmt2E8=
> =N6NQ
> -----END PGP SIGNATURE-----
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: index on to_char(created, 'YYYY') doesn't work
@ 2003-01-15 20:02 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Andreas Joseph Krogh <andreak@officenet.no>
1 sibling, 1 reply; 11+ messages in thread
From: Achilleus Mantzios @ 2003-01-15 20:02 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql
On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Wednesday 15 January 2003 18:55, Achilleus Mantzios wrote:
> > On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote:
> > > -----BEGIN PGP SIGNED MESSAGE-----
> > > Hash: SHA1
> > >
> > > On Wednesday 15 January 2003 16:12, you wrote:
> > > > On Wed, 15 Jan 2003, Andreas Joseph Krogh wrote:
> > > > > -----BEGIN PGP SIGNED MESSAGE-----
> > > > > Hash: SHA1
> > > > >
> > > > > On Wednesday 15 January 2003 11:37, you wrote:
> > > > > > The following does not work:
> > > > > >
> > > > > > create index session_u_idx on session (to_char(created, 'YYYY'));
> > > > > > ERROR: parser: parse error at or near "'YYYY'" at character 57
> > > > > >
> > > > > > Can I make a function to do this and index using the result of that
> > > > > > funtion? Do anyone have an example of such a function?
> > > > >
> > > > > I tried the following function:
> > > > > - -----------------------------------------------------------------
> > > > > create function drus (timestamp) returns varchar AS'
> > > > > DECLARE
> > > > > str_created VARCHAR;
> > > > > created ALIAS FOR $1;
> > > > > BEGIN
> > > > > str_created:= to_char(created, ''YYYY'');
> > > > > RETURN str_created;
> > > > > END;
> > > > > ' LANGUAGE 'plpgsql';
> > > >
> > > > add
> > > > WITH (iscachable)
> > >
> > > Thank you, not _that_ works:-)
> > > But now this doesn't work:
> > > create index session_u_idx on session (drus(created), username);
> >
> > Functinal indexes are single column indexes.
> >
> > Why dont you change your function to:
> >
> > create function drus (timestamp,varchar) returns varchar A
> >
> > and return the concatenation of to_char(created, ''YYYY'')||$2
> >
> > and then create the index as usual (passing the date and the username
> > as params to your function)????
>
> OK, thank you.
> Let me explain what I want to do:
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 have the following schema for gathering statistics from the web:
>
> CREATE TABLE session (
> session_id varchar(256) NOT NULL PRIMARY KEY,
> created timestamp DEFAULT 'now' NOT NULL,
> last_accessed timestamp NOT NULL,
> destroyed timestamp NOT NULL,
> username varchar -- Allow sessions from not logged in users
> );
>
> create or replace function drus (timestamp) returns varchar AS'
> DECLARE
> str_created VARCHAR;
> created ALIAS FOR $1;
> BEGIN
> str_created:= to_char(created, ''YYYY'');
> RETURN str_created;
> END;
> ' LANGUAGE 'plpgsql' WITH (iscachable);
>
> create index session_u_idx on session (drus(created)) where username is null;
>
> Now I want to get statistics for number of hits pr. week where users are not
> lnogged in(username IS NULL) for the year 2002:
>
> 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;
> week | count
> - ------+-------
> 01 | 6321
> 18 | 74
> 19 | 12153
> 20 | 17125
> 21 | 22157
> 22 | 25316
> 23 | 24265
> 24 | 26234
> 25 | 28583
> 26 | 29156
> 27 | 28335
> 28 | 23587
> 29 | 23203
>
> This table is quite large(900 000 rows) and the query takes several minutes to
> run, which makes the browser timeout.
> Do I have a design-issue here, should I rather batch-generate the stats in its
> own table so I don't have to process all the data(900 000 rows) each time.
>
> Is there any way to optimize/rewrite this query? Is the use of to_char on the
> timestamp wrong, should I use another comparation method for getting the year
> 2002?
>
> - --
> 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+JXiPUopImDh2gfQRAlf/AJ9xlcUDqa7NcXghtse8PAqQxkf1lACdEGxH
> vBXYxoFZnS6J35iQGw+14wE=
> =xCVY
> -----END PGP SIGNATURE-----
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2003-01-15 20:02 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-15 11:37 index on to_char(created, 'YYYY') doesn't work Andreas Joseph Krogh <andreak@officenet.no>
2003-01-15 11:52 ` Andreas Joseph Krogh <andreak@officenet.no>
2003-01-15 16:12 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-01-15 12:08 ` Andreas Joseph Krogh <andreak@officenet.no>
2003-01-15 17:19 ` Steve Crawford <scrawford@pinpointresearch.com>
2003-01-15 18:15 ` Josh Berkus <josh@agliodbs.com>
2003-01-15 18:55 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-01-15 15:04 ` Andreas Joseph Krogh <andreak@officenet.no>
2003-01-15 15:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-15 20:02 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-01-15 17:38 ` Andreas Joseph Krogh <andreak@officenet.no>
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