Re: Help on indexing timestamps - Mailing list pgsql-sql

From Tomasz Myrta
Subject Re: Help on indexing timestamps
Date
Msg-id 3E6C56EF.6050609@klaster.net
Whole thread Raw
In response to Help on indexing timestamps  (Andre Schubert <andre@km3.de>)
Responses Re: Help on indexing timestamps
List pgsql-sql
Andre Schubert wrote:
> Hi all,
> 
> i have a little problem on indexing a table which contains
> about 4 millions of traffic-data.
> My problem is, that a want to select all data from
> a specific month from a specific ip and this select should use the index.
> I use the following select:
> 
> db_nmkm3aue=# explain analyze select sum(inet_up+inet_down) from tbl_traffic where
date_trunc('month',tbl_traffic.time_stamp)::timestamptz= date_trunc('month',now() - timespan('1 months')) and ip =
'80.243.38.57';
> NOTICE:  QUERY PLAN:
> 
> Aggregate  (cost=116.30..116.30 rows=1 width=16) (actual time=1620.79..1620.79 rows=1 loops=1)
>   ->  Index Scan using idx_ip_time_stamp on tbl_traffic  (cost=0.00..116.30 rows=1 width=16) (actual
time=1216.79..1579.89rows=5232 loops=1)
 
> Total runtime: 1620.94 msec
> 
> But it takes a long time to select the traffic for all Ips.
> Is there a way to select these data with using the index correctly ?
> 
> Thanks in advance
> 
I have one more solution - try to rewrite your where clause to NOT USE 
function on time_stamp. If your query will look like:
select ... where time_stamp between (function with now() returning first 
day) and (function with now() returning last day);
your index will work fine.

Regards,
Tomasz Myrta




pgsql-sql by date:

Previous
From: Andre Schubert
Date:
Subject: Re: Help on indexing timestamps
Next
From: "Victor Yegorov"
Date:
Subject: Optimizing view