From: Isabelle Brette <isabelle@apartia.fr>
To: pgsql-sql@postgresql.org
Subject: indexes
Date: Tue, 5 Feb 2002 18:05:25 +0100
Message-ID: <20020205170524.GA1990@aparima.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: isabelle@apartia.fr
Subject: Re: indexes
In-Reply-To: <20020205170524.GA1990@aparima.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox