pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp on indexing timestamps
15+ messages / 4 participants
[nested] [flat]
* Help on indexing timestamps
@ 2003-03-07 10:26 Andre Schubert <andre@km3.de>
0 siblings, 2 replies; 15+ messages in thread
From: Andre Schubert @ 2003-03-07 10:26 UTC (permalink / raw)
To: pgsql-sql
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.89 rows=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
BTW: Table-schema as follows
db_test=# \d tbl_traffic
Table "tbl_traffic"
Column | Type | Modifiers
------------+--------------------------+-----------
time_stamp | timestamp with time zone | not null
ip | inet |
local_up | bigint | not null
local_down | bigint | not null
inet_up | bigint | not null
inet_down | bigint | not null
Indexes: idx_ip_time_stamp
db_test=# \d idx_ip_time_stamp
Index "idx_ip_time_stamp"
Column | Type
------------+--------------------------
ip | inet
time_stamp | timestamp with time zone
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Help on indexing timestamps
@ 2003-03-07 11:27 Andre Schubert <andre@km3.de>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 15+ messages in thread
From: Andre Schubert @ 2003-03-07 11:27 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
On Fri, 7 Mar 2003 13:01:16 -0200 (GMT+2)
Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> On Fri, 7 Mar 2003, 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:
>
> Did you try to use BETWEEN ??
> E.g.
> ... and time_stamp between '2003-01-01 00:00:00'::timestamp and
> '2003-02-01 00:00:00'::timestamp
>
Yes and it works if i write the dates by hand, every new month.
But the query is executed automatically and i dont want
to write in the dates before the query is executed. Maybe the
the start and enddate should also be alculated with sql,
because i want to create a view from this statement and execute it every month.
Or did i miss something.
Regards, as
> >
> > 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.89 rows=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
> >
> > BTW: Table-schema as follows
> >
> > db_test=# \d tbl_traffic
> > Table "tbl_traffic"
> > Column | Type | Modifiers
> > ------------+--------------------------+-----------
> > time_stamp | timestamp with time zone | not null
> > ip | inet |
> > local_up | bigint | not null
> > local_down | bigint | not null
> > inet_up | bigint | not null
> > inet_down | bigint | not null
> > Indexes: idx_ip_time_stamp
> >
> > db_test=# \d idx_ip_time_stamp
> > Index "idx_ip_time_stamp"
> > Column | Type
> > ------------+--------------------------
> > ip | inet
> > time_stamp | timestamp with time zone
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
> --
> ==================================================================
> Achilleus Mantzios
> S/W Engineer
> IT dept
> Dynacom Tankers Mngmt
> Nikis 4, Glyfada
> Athens 16610
> Greece
> tel: +30-210-8981112
> fax: +30-210-8981877
> email: achill@matrix.gatewaynet.com
> mantzios@softlab.ece.ntua.gr
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Help on indexing timestamps
@ 2003-03-07 12:02 Andre Schubert <andre@km3.de>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 15+ messages in thread
From: Andre Schubert @ 2003-03-07 12:02 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
On Fri, 7 Mar 2003 13:48:04 -0200 (GMT+2)
Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> On Fri, 7 Mar 2003, Andre Schubert wrote:
>
> > On Fri, 7 Mar 2003 13:01:16 -0200 (GMT+2)
> > Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> >
> > > On Fri, 7 Mar 2003, 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:
> > >
> > > Did you try to use BETWEEN ??
> > > E.g.
> > > ... and time_stamp between '2003-01-01 00:00:00'::timestamp and
> > > '2003-02-01 00:00:00'::timestamp
> > >
> >
> > Yes and it works if i write the dates by hand, every new month.
> > But the query is executed automatically and i dont want
> > to write in the dates before the query is executed. Maybe the
> > the start and enddate should also be alculated with sql,
> > because i want to create a view from this statement and execute it every month.
> > Or did i miss something.
>
> You could have an index on the
> whole
> date_trunc('month',tbl_traffic.time_stamp),ip
>
> How does it perform?
>
I'am not sure how to create such an index...
First: create or replace function trunc_ip(timestamp with time zone) returns timestamptz as
'select date_trunc(''month'',$1)' language 'sql' with (iscachable);
Then: create index idx_test on tbl_traffic using btree( trunc(time_stamp) );
Result: db_km3aue=# explain analyze select sum(inet_up+inet_down) from tbl_traffic where trunc(tbl_traffic.time_stamp) = trunc('2003-02-01'::timestamptz) and ip = '80.243.40.56';
NOTICE: QUERY PLAN:
Aggregate (cost=108.78..108.78 rows=1 width=16) (actual time=2278.48..2278.48 rows=1 loops=1)
-> Index Scan using idx_test on tbl_traffic (cost=0.00..108.78 rows=1 width=16) (actual time=0.23..2240.50 rows=5346 loops=1)
Total runtime: 2278.62 msec
Maybe the problem is, that the index is created without ip as the second column....
[schnipp]
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Help on indexing timestamps
@ 2003-03-07 12:47 Andre Schubert <andre@km3.de>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 15+ messages in thread
From: Andre Schubert @ 2003-03-07 12:47 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
On Fri, 7 Mar 2003 14:17:36 -0200 (GMT+2)
Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> On Fri, 7 Mar 2003, Andre Schubert wrote:
>
> > On Fri, 7 Mar 2003 13:48:04 -0200 (GMT+2)
> > Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> >
> > > On Fri, 7 Mar 2003, Andre Schubert wrote:
> > >
> > > > On Fri, 7 Mar 2003 13:01:16 -0200 (GMT+2)
> > > > Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> > > >
> > > > > On Fri, 7 Mar 2003, 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:
> > > > >
> > > > > Did you try to use BETWEEN ??
> > > > > E.g.
> > > > > ... and time_stamp between '2003-01-01 00:00:00'::timestamp and
> > > > > '2003-02-01 00:00:00'::timestamp
> > > > >
> > > >
> > > > Yes and it works if i write the dates by hand, every new month.
> > > > But the query is executed automatically and i dont want
> > > > to write in the dates before the query is executed. Maybe the
> > > > the start and enddate should also be alculated with sql,
> > > > because i want to create a view from this statement and execute it every month.
> > > > Or did i miss something.
> > >
> > > You could have an index on the
> > > whole
> > > date_trunc('month',tbl_traffic.time_stamp),ip
> > >
> > > How does it perform?
> > >
> >
> > I'am not sure how to create such an index...
> >
> > First: create or replace function trunc_ip(timestamp with time zone) returns timestamptz as
> > 'select date_trunc(''month'',$1)' language 'sql' with (iscachable);
> > Then: create index idx_test on tbl_traffic using btree( trunc(time_stamp) );
> >
> > Result: db_km3aue=# explain analyze select sum(inet_up+inet_down) from tbl_traffic where trunc(tbl_traffic.time_stamp) = trunc('2003-02-01'::timestamptz) and ip = '80.243.40.56';
> > NOTICE: QUERY PLAN:
> >
> > Aggregate (cost=108.78..108.78 rows=1 width=16) (actual time=2278.48..2278.48 rows=1 loops=1)
> > -> Index Scan using idx_test on tbl_traffic (cost=0.00..108.78 rows=1 width=16) (actual time=0.23..2240.50 rows=5346 loops=1)
> > Total runtime: 2278.62 msec
> >
> > Maybe the problem is, that the index is created without ip as the second column....
>
> Sorry, it sliped my mind that we cannot have compound indexes on
> functions. :(
>
> Anyway.
> Did the explicit BETWEEN gave you satisfactory performance?
>
Yes if i use BETWEEN and type in the dates by hand the query takes about 200ms with explain analyze.
I think these is a good performance.
But if i use now() instead of manually typed dates the query take about 1400ms :(
I thought the somebody posted to this list, that now() is a function that is not cached,
and thatswhy does not work pretty well with indexes.
I created a cached function cached_now() which returns now() but is declared with "isCacheable".
If i use cached_now() instead of now the query takes also about 200ms :)
Is it safe, or better could it have any side-effects, if i use my "own" cached_now() in such a query ?
> >
> > [schnipp]
> >
>
> --
> ==================================================================
> Achilleus Mantzios
> S/W Engineer
> IT dept
> Dynacom Tankers Mngmt
> Nikis 4, Glyfada
> Athens 16610
> Greece
> tel: +30-210-8981112
> fax: +30-210-8981877
> email: achill@matrix.gatewaynet.com
> mantzios@softlab.ece.ntua.gr
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Help on indexing timestamps
@ 2003-03-07 13:11 Andre Schubert <andre@km3.de>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 15+ messages in thread
From: Andre Schubert @ 2003-03-07 13:11 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
On Fri, 7 Mar 2003 15:03:01 -0200 (GMT+2)
Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> On Fri, 7 Mar 2003, Andre Schubert wrote:
>
> > On Fri, 7 Mar 2003 14:17:36 -0200 (GMT+2)
> > Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> >
> > > On Fri, 7 Mar 2003, Andre Schubert wrote:
> > >
> > > > On Fri, 7 Mar 2003 13:48:04 -0200 (GMT+2)
> > > > Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> > > >
> > > > > On Fri, 7 Mar 2003, Andre Schubert wrote:
> > > > >
> > > > > > On Fri, 7 Mar 2003 13:01:16 -0200 (GMT+2)
> > > > > > Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> > > > > >
> > > > > > > On Fri, 7 Mar 2003, 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:
> > > > > > >
> > > > > > > Did you try to use BETWEEN ??
> > > > > > > E.g.
> > > > > > > ... and time_stamp between '2003-01-01 00:00:00'::timestamp and
> > > > > > > '2003-02-01 00:00:00'::timestamp
> > > > > > >
> > > > > >
> > > > > > Yes and it works if i write the dates by hand, every new month.
> > > > > > But the query is executed automatically and i dont want
> > > > > > to write in the dates before the query is executed. Maybe the
> > > > > > the start and enddate should also be alculated with sql,
> > > > > > because i want to create a view from this statement and execute it every month.
> > > > > > Or did i miss something.
> > > > >
> > > > > You could have an index on the
> > > > > whole
> > > > > date_trunc('month',tbl_traffic.time_stamp),ip
> > > > >
> > > > > How does it perform?
> > > > >
> > > >
> > > > I'am not sure how to create such an index...
> > > >
> > > > First: create or replace function trunc_ip(timestamp with time zone) returns timestamptz as
> > > > 'select date_trunc(''month'',$1)' language 'sql' with (iscachable);
> > > > Then: create index idx_test on tbl_traffic using btree( trunc(time_stamp) );
> > > >
> > > > Result: db_km3aue=# explain analyze select sum(inet_up+inet_down) from tbl_traffic where trunc(tbl_traffic.time_stamp) = trunc('2003-02-01'::timestamptz) and ip = '80.243.40.56';
> > > > NOTICE: QUERY PLAN:
> > > >
> > > > Aggregate (cost=108.78..108.78 rows=1 width=16) (actual time=2278.48..2278.48 rows=1 loops=1)
> > > > -> Index Scan using idx_test on tbl_traffic (cost=0.00..108.78 rows=1 width=16) (actual time=0.23..2240.50 rows=5346 loops=1)
> > > > Total runtime: 2278.62 msec
> > > >
> > > > Maybe the problem is, that the index is created without ip as the second column....
> > >
> > > Sorry, it sliped my mind that we cannot have compound indexes on
> > > functions. :(
> > >
> > > Anyway.
> > > Did the explicit BETWEEN gave you satisfactory performance?
> > >
> >
> > Yes if i use BETWEEN and type in the dates by hand the query takes about 200ms with explain analyze.
> > I think these is a good performance.
> > But if i use now() instead of manually typed dates the query take about 1400ms :(
> > I thought the somebody posted to this list, that now() is a function that is not cached,
> > and thatswhy does not work pretty well with indexes.
> > I created a cached function cached_now() which returns now() but is declared with "isCacheable".
> > If i use cached_now() instead of now the query takes also about 200ms :)
> > Is it safe, or better could it have any side-effects, if i use my "own" cached_now() in such a query ?
>
> I think you should use a high level language to wrap your PostgreSQL
> access (e.g. java, c, perl, ....).
>
> Since you are gonna be running this query as a batch job,
> you must use a high level lang that can handle dates in
> a UNIX-like way. (milisecs from 1970-01-01 00:00:00)
>
Ok, i think you are right.
Thanks for the quick advice.
Regards, as
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Help on indexing timestamps
@ 2003-03-07 15:01 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Andre Schubert <andre@km3.de>
1 sibling, 1 reply; 15+ messages in thread
From: Achilleus Mantzios @ 2003-03-07 15:01 UTC (permalink / raw)
To: Andre Schubert <andre@km3.de>; +Cc: pgsql-sql
On Fri, 7 Mar 2003, 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:
Did you try to use BETWEEN ??
E.g.
... and time_stamp between '2003-01-01 00:00:00'::timestamp and
'2003-02-01 00:00:00'::timestamp
>
> 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.89 rows=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
>
> BTW: Table-schema as follows
>
> db_test=# \d tbl_traffic
> Table "tbl_traffic"
> Column | Type | Modifiers
> ------------+--------------------------+-----------
> time_stamp | timestamp with time zone | not null
> ip | inet |
> local_up | bigint | not null
> local_down | bigint | not null
> inet_up | bigint | not null
> inet_down | bigint | not null
> Indexes: idx_ip_time_stamp
>
> db_test=# \d idx_ip_time_stamp
> Index "idx_ip_time_stamp"
> Column | Type
> ------------+--------------------------
> ip | inet
> time_stamp | timestamp with time zone
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Help on indexing timestamps
@ 2003-03-07 15:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andre Schubert <andre@km3.de>
1 sibling, 1 reply; 15+ messages in thread
From: Tom Lane @ 2003-03-07 15:05 UTC (permalink / raw)
To: Andre Schubert <andre@km3.de>; +Cc: Achilleus Mantzios <achill@matrix.gatewaynet.com>; pgsql-sql
Andre Schubert <andre@km3.de> writes:
> Yes if i use BETWEEN and type in the dates by hand the query takes about 200ms with explain analyze.
> I think these is a good performance.
> But if i use now() instead of manually typed dates the query take about 1400ms :(
> I thought the somebody posted to this list, that now() is a function that is not cached,
> and thatswhy does not work pretty well with indexes.
Update to 7.3, and that problem will go away.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Help on indexing timestamps
@ 2003-03-07 15:48 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Andre Schubert <andre@km3.de>
0 siblings, 1 reply; 15+ messages in thread
From: Achilleus Mantzios @ 2003-03-07 15:48 UTC (permalink / raw)
To: Andre Schubert <andre@km3.de>; +Cc: pgsql-sql
On Fri, 7 Mar 2003, Andre Schubert wrote:
> On Fri, 7 Mar 2003 13:01:16 -0200 (GMT+2)
> Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
>
> > On Fri, 7 Mar 2003, 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:
> >
> > Did you try to use BETWEEN ??
> > E.g.
> > ... and time_stamp between '2003-01-01 00:00:00'::timestamp and
> > '2003-02-01 00:00:00'::timestamp
> >
>
> Yes and it works if i write the dates by hand, every new month.
> But the query is executed automatically and i dont want
> to write in the dates before the query is executed. Maybe the
> the start and enddate should also be alculated with sql,
> because i want to create a view from this statement and execute it every month.
> Or did i miss something.
You could have an index on the
whole
date_trunc('month',tbl_traffic.time_stamp),ip
How does it perform?
>
> Regards, as
>
> > >
> > > 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.89 rows=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
> > >
> > > BTW: Table-schema as follows
> > >
> > > db_test=# \d tbl_traffic
> > > Table "tbl_traffic"
> > > Column | Type | Modifiers
> > > ------------+--------------------------+-----------
> > > time_stamp | timestamp with time zone | not null
> > > ip | inet |
> > > local_up | bigint | not null
> > > local_down | bigint | not null
> > > inet_up | bigint | not null
> > > inet_down | bigint | not null
> > > Indexes: idx_ip_time_stamp
> > >
> > > db_test=# \d idx_ip_time_stamp
> > > Index "idx_ip_time_stamp"
> > > Column | Type
> > > ------------+--------------------------
> > > ip | inet
> > > time_stamp | timestamp with time zone
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo@postgresql.org so that your
> > > message can get through to the mailing list cleanly
> > >
> >
> > --
> > ==================================================================
> > Achilleus Mantzios
> > S/W Engineer
> > IT dept
> > Dynacom Tankers Mngmt
> > Nikis 4, Glyfada
> > Athens 16610
> > Greece
> > tel: +30-210-8981112
> > fax: +30-210-8981877
> > email: achill@matrix.gatewaynet.com
> > mantzios@softlab.ece.ntua.gr
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Help on indexing timestamps
@ 2003-03-07 16:17 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Andre Schubert <andre@km3.de>
0 siblings, 1 reply; 15+ messages in thread
From: Achilleus Mantzios @ 2003-03-07 16:17 UTC (permalink / raw)
To: Andre Schubert <andre@km3.de>; +Cc: pgsql-sql
On Fri, 7 Mar 2003, Andre Schubert wrote:
> On Fri, 7 Mar 2003 13:48:04 -0200 (GMT+2)
> Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
>
> > On Fri, 7 Mar 2003, Andre Schubert wrote:
> >
> > > On Fri, 7 Mar 2003 13:01:16 -0200 (GMT+2)
> > > Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> > >
> > > > On Fri, 7 Mar 2003, 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:
> > > >
> > > > Did you try to use BETWEEN ??
> > > > E.g.
> > > > ... and time_stamp between '2003-01-01 00:00:00'::timestamp and
> > > > '2003-02-01 00:00:00'::timestamp
> > > >
> > >
> > > Yes and it works if i write the dates by hand, every new month.
> > > But the query is executed automatically and i dont want
> > > to write in the dates before the query is executed. Maybe the
> > > the start and enddate should also be alculated with sql,
> > > because i want to create a view from this statement and execute it every month.
> > > Or did i miss something.
> >
> > You could have an index on the
> > whole
> > date_trunc('month',tbl_traffic.time_stamp),ip
> >
> > How does it perform?
> >
>
> I'am not sure how to create such an index...
>
> First: create or replace function trunc_ip(timestamp with time zone) returns timestamptz as
> 'select date_trunc(''month'',$1)' language 'sql' with (iscachable);
> Then: create index idx_test on tbl_traffic using btree( trunc(time_stamp) );
>
> Result: db_km3aue=# explain analyze select sum(inet_up+inet_down) from tbl_traffic where trunc(tbl_traffic.time_stamp) = trunc('2003-02-01'::timestamptz) and ip = '80.243.40.56';
> NOTICE: QUERY PLAN:
>
> Aggregate (cost=108.78..108.78 rows=1 width=16) (actual time=2278.48..2278.48 rows=1 loops=1)
> -> Index Scan using idx_test on tbl_traffic (cost=0.00..108.78 rows=1 width=16) (actual time=0.23..2240.50 rows=5346 loops=1)
> Total runtime: 2278.62 msec
>
> Maybe the problem is, that the index is created without ip as the second column....
Sorry, it sliped my mind that we cannot have compound indexes on
functions. :(
Anyway.
Did the explicit BETWEEN gave you satisfactory performance?
>
> [schnipp]
>
--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Help on indexing timestamps
@ 2003-03-07 17:03 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Andre Schubert <andre@km3.de>
1 sibling, 1 reply; 15+ messages in thread
From: Achilleus Mantzios @ 2003-03-07 17:03 UTC (permalink / raw)
To: Andre Schubert <andre@km3.de>; +Cc: pgsql-sql
On Fri, 7 Mar 2003, Andre Schubert wrote:
> On Fri, 7 Mar 2003 14:17:36 -0200 (GMT+2)
> Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
>
> > On Fri, 7 Mar 2003, Andre Schubert wrote:
> >
> > > On Fri, 7 Mar 2003 13:48:04 -0200 (GMT+2)
> > > Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> > >
> > > > On Fri, 7 Mar 2003, Andre Schubert wrote:
> > > >
> > > > > On Fri, 7 Mar 2003 13:01:16 -0200 (GMT+2)
> > > > > Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> > > > >
> > > > > > On Fri, 7 Mar 2003, 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:
> > > > > >
> > > > > > Did you try to use BETWEEN ??
> > > > > > E.g.
> > > > > > ... and time_stamp between '2003-01-01 00:00:00'::timestamp and
> > > > > > '2003-02-01 00:00:00'::timestamp
> > > > > >
> > > > >
> > > > > Yes and it works if i write the dates by hand, every new month.
> > > > > But the query is executed automatically and i dont want
> > > > > to write in the dates before the query is executed. Maybe the
> > > > > the start and enddate should also be alculated with sql,
> > > > > because i want to create a view from this statement and execute it every month.
> > > > > Or did i miss something.
> > > >
> > > > You could have an index on the
> > > > whole
> > > > date_trunc('month',tbl_traffic.time_stamp),ip
> > > >
> > > > How does it perform?
> > > >
> > >
> > > I'am not sure how to create such an index...
> > >
> > > First: create or replace function trunc_ip(timestamp with time zone) returns timestamptz as
> > > 'select date_trunc(''month'',$1)' language 'sql' with (iscachable);
> > > Then: create index idx_test on tbl_traffic using btree( trunc(time_stamp) );
> > >
> > > Result: db_km3aue=# explain analyze select sum(inet_up+inet_down) from tbl_traffic where trunc(tbl_traffic.time_stamp) = trunc('2003-02-01'::timestamptz) and ip = '80.243.40.56';
> > > NOTICE: QUERY PLAN:
> > >
> > > Aggregate (cost=108.78..108.78 rows=1 width=16) (actual time=2278.48..2278.48 rows=1 loops=1)
> > > -> Index Scan using idx_test on tbl_traffic (cost=0.00..108.78 rows=1 width=16) (actual time=0.23..2240.50 rows=5346 loops=1)
> > > Total runtime: 2278.62 msec
> > >
> > > Maybe the problem is, that the index is created without ip as the second column....
> >
> > Sorry, it sliped my mind that we cannot have compound indexes on
> > functions. :(
> >
> > Anyway.
> > Did the explicit BETWEEN gave you satisfactory performance?
> >
>
> Yes if i use BETWEEN and type in the dates by hand the query takes about 200ms with explain analyze.
> I think these is a good performance.
> But if i use now() instead of manually typed dates the query take about 1400ms :(
> I thought the somebody posted to this list, that now() is a function that is not cached,
> and thatswhy does not work pretty well with indexes.
> I created a cached function cached_now() which returns now() but is declared with "isCacheable".
> If i use cached_now() instead of now the query takes also about 200ms :)
> Is it safe, or better could it have any side-effects, if i use my "own" cached_now() in such a query ?
I think you should use a high level language to wrap your PostgreSQL
access (e.g. java, c, perl, ....).
Since you are gonna be running this query as a batch job,
you must use a high level lang that can handle dates in
a UNIX-like way. (milisecs from 1970-01-01 00:00:00)
>
> > >
> > > [schnipp]
> > >
> >
> > --
> > ==================================================================
> > Achilleus Mantzios
> > S/W Engineer
> > IT dept
> > Dynacom Tankers Mngmt
> > Nikis 4, Glyfada
> > Athens 16610
> > Greece
> > tel: +30-210-8981112
> > fax: +30-210-8981877
> > email: achill@matrix.gatewaynet.com
> > mantzios@softlab.ece.ntua.gr
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Help on indexing timestamps
@ 2003-03-10 09:12 Tomasz Myrta <jasiek@klaster.net>
parent: Andre Schubert <andre@km3.de>
1 sibling, 1 reply; 15+ messages in thread
From: Tomasz Myrta @ 2003-03-10 09:12 UTC (permalink / raw)
To: Andre Schubert <andre@km3.de>; +Cc: 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.89 rows=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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Help on indexing timestamps
@ 2003-03-10 09:17 Andre Schubert <andre@km3.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Andre Schubert @ 2003-03-10 09:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: achill@matrix.gatewaynet.com; pgsql-sql
On Fri, 07 Mar 2003 10:05:03 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Andre Schubert <andre@km3.de> writes:
> > Yes if i use BETWEEN and type in the dates by hand the query takes about 200ms with explain analyze.
> > I think these is a good performance.
> > But if i use now() instead of manually typed dates the query take about 1400ms :(
> > I thought the somebody posted to this list, that now() is a function that is not cached,
> > and thatswhy does not work pretty well with indexes.
>
> Update to 7.3, and that problem will go away.
>
What is the difference between 7.2 and 7.3 related to my problem ?
Thanks, as
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Help on indexing timestamps
@ 2003-03-10 14:24 Andre Schubert <andre@km3.de>
parent: Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 15+ messages in thread
From: Andre Schubert @ 2003-03-10 14:24 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
On Mon, 10 Mar 2003 10:12:15 +0100
Tomasz Myrta <jasiek@klaster.net> wrote:
> 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.89 rows=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.
>
Thanks for the hint, i will test this and report to this list.
Thanks, as
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Help on indexing timestamps
@ 2003-03-10 15:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andre Schubert <andre@km3.de>
0 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2003-03-10 15:07 UTC (permalink / raw)
To: Andre Schubert <andre@km3.de>; +Cc: achill@matrix.gatewaynet.com; pgsql-sql
Andre Schubert <andre@km3.de> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Update to 7.3, and that problem will go away.
>>
> What is the difference between 7.2 and 7.3 related to my problem ?
7.3 is willing to index comparisons to now(), that's what ;-)
You may care to study the difference between cachable/noncachable
functions in 7.2, versus the difference between
immutable/stable/volatile functions in 7.3. 7.2 has to treat now()
as noncachable and therefore unsafe to index. 7.3 treats it as
stable and therefore safe to index.
You can work around this in 7.2 by cheating with subselects or wrapper
functions, but that's a poor substitute for a real solution.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Help on indexing timestamps
@ 2003-03-12 06:27 Andre Schubert <andre@km3.de>
parent: Andre Schubert <andre@km3.de>
0 siblings, 0 replies; 15+ messages in thread
From: Andre Schubert @ 2003-03-12 06:27 UTC (permalink / raw)
To: Tomasz Myrta <jasiek@klaster.net>; +Cc: pgsql-sql
On Mon, 10 Mar 2003 15:24:09 +0100
Andre Schubert <andre@km3.de> wrote:
> On Mon, 10 Mar 2003 10:12:15 +0100
> Tomasz Myrta <jasiek@klaster.net> wrote:
>
> > 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.89 rows=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.
> >
> Thanks for the hint, i will test this and report to this list.
>
I have tested and it doesnt work as it should do.
I think its the same problem posted by Tom Lane,
and it should go away after an update to 7.3.
Regards, as
Thanks as
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2003-03-12 06:27 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-07 10:26 Help on indexing timestamps Andre Schubert <andre@km3.de>
2003-03-07 15:01 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-03-07 11:27 ` Andre Schubert <andre@km3.de>
2003-03-07 15:48 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-03-07 12:02 ` Andre Schubert <andre@km3.de>
2003-03-07 16:17 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-03-07 12:47 ` Andre Schubert <andre@km3.de>
2003-03-07 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-10 09:17 ` Andre Schubert <andre@km3.de>
2003-03-10 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-07 17:03 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2003-03-07 13:11 ` Andre Schubert <andre@km3.de>
2003-03-10 09:12 ` Tomasz Myrta <jasiek@klaster.net>
2003-03-10 14:24 ` Andre Schubert <andre@km3.de>
2003-03-12 06:27 ` Andre Schubert <andre@km3.de>
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