Thread: index on to_char(created, 'YYYY') doesn't work
-----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 youbut 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-----
-----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 youbut 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-----
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
-----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_accessedtimestamp 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 youbut 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-----
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
-----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_accessedtimestamp 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 ALIASFOR $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 | 632118 | 7419 | 1215320 | 1712521 | 2215722 | 2531623 | 2426524 | 2623425 | 2858326 | 2915627 | 2833528 | 2358729 | 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 youbut 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-----
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
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
<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?
-----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 youbut 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-----
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