Message-ID: <4B45E36E.4040706@tweakers.net>
Date: Thu, 07 Jan 2010 14:36:46 +0100
From: Arjen van der Meijden <acmmailing@tweakers.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US;
	rv:1.9.1.5) Gecko/20091204 Thunderbird/3.0
MIME-Version: 1.0
To: Lefteris <lsidir@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Air-traffic benchmark
References: <852badbc1001070438j36eb10d3qa22398ffcbef405f@mail.gmail.com>
In-Reply-To: <852badbc1001070438j36eb10d3qa22398ffcbef405f@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 7-1-2010 13:38 Lefteris wrote:
> I decided to run the benchmark over postgres to get some more
> experience and insights. Unfortunately, the query times I got from
> postgres were not the expected ones:

Why were they not expected? In the given scenario, column databases are 
having a huge advantage. Especially the given simple example is the type 
of query a column database *should* excel.
You should, at the very least, compare the queries to MyISAM:
http://www.mysqlperformanceblog.com/2009/11/05/air-traffic-queries-in-myisam-and-tokutek-tokudb/

But unfortunately, that one also beats your postgresql-results.

> The hardware characteristics are:
> Platform Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.40GHz with 8GB RAM and
> ample disk space (2x 500 GB SATA disk @ 7200 RPM as SW-RAID-0)

Unfortunately, the blogpost fails to mention the disk-subsystem. So it 
may well be much faster than yours, although its not a new, big or fast 
server, so unless it has external storage, it shouldn't be too different 
for sequential scans.

> SELECT "DayOfWeek", count(*) AS c FROM ontime WHERE "Year" BETWEEN
> 2000 AND 2008 GROUP BY "DayOfWeek" ORDER BY c DESC;
>
> Reported query times are (in sec):
> MonetDB 7.9s
> InfoBright 12.13s
> LucidDB 54.8s
>
> For pg-8.4.2  I got with 3 consecutive runs on the server:
> 5m52.384s
> 5m55.885s
> 5m54.309s

Maybe an index of the type 'year, dayofweek' will help for this query. 
But it'll have to scan about half the table any way, so a seq scan isn't 
a bad idea.
In this case, a partitioned table with partitions per year and 
constraint exclusion enabled would help a bit more.

Best regards,

Arjen