Thread: 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')
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

Isabella,

I would do a
create index sess_created on sessions (ip, to_char);

This satisfies the where clause and might help in the group by.

Best regards,
Chris

At 18:05 +0100 02/05/2002, Isabelle Brette wrote:
>Hi everybody ^^
>
[snip]

-- 
Chris Ruprecht
Network grunt and bit pusher extraordinaíre

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Hi again,

On Tue, Feb 05, 2002 at 11:50:52AM -0600, Chris Ruprecht wrote:
> Isabella,

Mh, it's Isabelle, thanks.

> I would do a
> create index sess_created on sessions (ip, to_char);
> 
> This satisfies the where clause and might help in the group by.

This does not seem to work.

One thing or two I did not say in my previous post :

- the 'created' column is a timestamp, so the to_char function needs a second argument to know which format to use. 
- the 'ip' column is of postgres type inet, that's why I use the << operator.

- for some other queries, I also need a filter on IPs but not on the timestamp.

For the ip thing, I just looked at the doc and it seems a '<<' operator
on inet columns just can't use an indexed column properly. All I can do
is simple comparisons such as <, <=, =, >=, > (only the btree access
method is implemented in my current version of Postgres : 7.1.3 ; will
this change in 7.2 ?).

-- 
Isabelle Brette - isabelle@apartia.fr

On Tue, 5 Feb 2002, Isabelle Brette wrote:

> 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 ?

For a functional index that you want to pass constants to, right now you'd
need to make a cacheable function that takes created in this case and
returns the to_char(created, 'YYYY/MM') and then use that in the index and
query.