Date: Tue, 5 Feb 2002 18:05:25 +0100
From: Isabelle Brette <isabelle@apartia.fr>
To: pgsql-sql@postgresql.org
Subject: indexes
Message-ID: <20020205170524.GA1990@aparima.com>
Reply-To: isabelle@apartia.fr
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.3.27i

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