datetime extract function performance

metin d metdos at yahoo.com
Fri Oct 19 16:48:40 CEST 2012


Hi Ying,

Here the information yo asked.
Best Regards,
Metin

- queries and execution times (internel time reports of DBs) - there is only one day in the dataset
I used time difference between queries with extract and without extract. For this examples, it is around 4x assuming difference is because of extract function.

MonetDB:

Ex. time: 4.8s
SELECT
    extract(day from event_time) as event_day,
    act,
    count(*)
FROM
    events
GROUP BY
    act,
    event_day;

Ex. time: 70ms
SELECT
    act,
    count(*)
FROM
    events
GROUP BY
    act;

Postgres:

Ex. time: 2.9s
SELECT
    extract(day from event_time) as event_day,
    act,
    count(*)
FROM
    events
GROUP BY
    act,
    event_day;

Ex. time: 1.7s
SELECT
    act,
    count(*)
FROM
    events
GROUP BY
    act;


-db schema

CREATE TABLE events(
    event_time timestamp,
    event varchar(100)
);


- db size
300 MB

- output of 'mserver5 --version'
MonetDB 5 server v11.11.11 "Jul2012-SP2" (64-bit, 64-bit oids)


- OS
Ubuntu 12.04 LTS


- Postgres version
9.1.6





________________________________
 From: Ying Zhang <Y.Zhang at cwi.nl>
To: Communication channel for developers of the MonetDB suite. <developers-list at monetdb.org> 
Sent: Friday, October 19, 2012 5:08 PM
Subject: Re: datetime extract function performance
 
Hello Metin,

Before we can say anything about your use case, would you please provide
us the following information:

- query
- db schema
- db size
- output of 'mserver5 --version'
- OS
- Postgres version (assuming it's running on the same machine?)
- execution time of your "extract" function on MonetDB and Postgres
- how did you measure those times?

Kind regards,

Jennie

On Fri, Oct 19, 2012 at 02:34:02AM -0700, metin d wrote:
> Hey,
> 
> I'm running a small performance test on MonetDB and
> Postgres. According to my results "extract" function in monetDB (http:/
> /www.monetdb.org/Documentation/SQLreference/Temporal) is nearly 10x
> slower then Postgres. 
> 
> Do you know the reason, or do you have any idea which can make
> "extract" function faster in MonetDB ?
> 
> Thanks,
> Metin

> _______________________________________________
> developers-list mailing list
> developers-list at monetdb.org
> http://mail.monetdb.org/mailman/listinfo/developers-list

_______________________________________________
developers-list mailing list
developers-list at monetdb.org
http://mail.monetdb.org/mailman/listinfo/developers-list
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.monetdb.org/pipermail/developers-list/attachments/20121019/c18d1215/attachment.html>
-------------- next part --------------
_______________________________________________
developers-list mailing list
developers-list at monetdb.org
http://mail.monetdb.org/mailman/listinfo/developers-list


More information about the developers-list mailing list