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@cwi.nl>
To: Communication channel for developers of the MonetDB suite. <developers-list@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@monetdb.org
> http://mail.monetdb.org/mailman/listinfo/developers-list

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