Re: index on to_char(created, 'YYYY') doesn't work - Mailing list 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?