indexes

Hi everybody ^^

I have a big, big table (over 1 million rows) that's been logging
sessions on a website for more than a year. And a few queries so I can
have a few stats on it.

One of the problems is, for stats I need to exclude internal IPs (the
web server is also used for the intranet). Another one, I generally need
the stats for each month. One of the queries is something like :

select to_char(created,'YYYY/MM'), count(*) from sessions where not
(ip << '192.168.0.0/16' or ip << '10.0.2.0/24') group by
to_char(created,'YYYY/MM');

As you can guess, with 1 million rows, it has become very, very slow and
I would need a few indexes to make this a little faster.

As you may also guess, making straight indexes does not work (I still
have this damn Seq Scan while EXPLAINing). For the date, I tried this :

create index sess_created on sessions (to_char(created,'YYYY/MM'));

but I get a parse error near "'". What do I have wrong, as the query
seems right ? Is it because of the non-column argument ?

And what would you suggest for the ip column ?

Thanks for your help ^^

--
Isabelle Brette - isabelle(at)apartia(dot)fr