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')
groupbyto_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@apartia.fr
