DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=KvJwFV/v3kb5eMWZjEtJcdC+G4pUeMFhq/tdgBvtxSven/JgWRjhLvm0cHAswKJQl+
	YB5LNaN3n5QXd7qk8dBJ7Pi4h1ss7cczEmXtxtu1/M9PNnDlSMesC96yQRsRSCQ9nOJ5
	eFdGsbUt63ic6TvgDzM8I9JWNC8hlbmpLT0OA=
MIME-Version: 1.0
In-Reply-To: <4B45E36E.4040706@tweakers.net>
References: <852badbc1001070438j36eb10d3qa22398ffcbef405f@mail.gmail.com>
	<4B45E36E.4040706@tweakers.net>
Date: Thu, 7 Jan 2010 14:47:36 +0100
Message-ID: <852badbc1001070547p48b47175vf87efc473e3bd41b@mail.gmail.com>
Subject: Re: Air-traffic benchmark
From: Lefteris <lsidir@gmail.com>
To: Arjen van der Meijden <acmmailing@tweakers.net>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi Arjen,

so I understand from all of you that you don't consider the use of 25k
for sorting to be the cause of the slowdown? Probably I am missing
something on the specific sort algorithm used by PG. My RAM does fill
up, mainly by file buffers from linux, but postgres process remains to
0.1% consumption of main memory. There is no way to force sort to use
say blocks of 128MB ? wouldn't that make a difference?

lefteris

p.s. i already started the analyze verbose again as Flavio suggested
and reset the parrameters, although I think some of Flavioo's
suggestions have to do with multiple users/queries and not 1 long
running query, like shared_buffers, or not?

On Thu, Jan 7, 2010 at 2:36 PM, Arjen van der Meijden
<acmmailing@tweakers.net> wrote:
> 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-myi=
sam-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 ma=
y
> well be much faster than yours, although its not a new, big or fast serve=
r,
> 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 =A0I 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. Bu=
t
> it'll have to scan about half the table any way, so a seq scan isn't a ba=
d
> idea.
> In this case, a partitioned table with partitions per year and constraint
> exclusion enabled would help a bit more.
>
> Best regards,
>
> Arjen
>