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=mU4s4r6/y9bd436BdY5w2xGqjzjpWu/UyBVIx8k03zB0OEO8xgvHRtMV+PzFdfLbIu
	JRsxOSdnCtpklcqKg7RX66Ozq8VuooStxllSLT+dbl7lr17KsnXlnHJOo3rAUWtnN5AK
	KhVxMOAi9vLbhzAOvwCfxqOqtKX5UulCgkjfs=
MIME-Version: 1.0
In-Reply-To: <852badbc1001071457m42d4042brbb5c6b5fa7efce20@mail.gmail.com>
References: <852badbc1001070438j36eb10d3qa22398ffcbef405f@mail.gmail.com>
	<4B45E36E.4040706@tweakers.net>
	<852badbc1001070547p48b47175vf87efc473e3bd41b@mail.gmail.com>
	<26014999.20100107150535@erwied.eu>
	<852badbc1001070610p698dc3cfr4628b1e7768990e6@mail.gmail.com>
	<4B465E7B.3060904@2ndquadrant.com>
	<852badbc1001071457m42d4042brbb5c6b5fa7efce20@mail.gmail.com>
Date: Fri, 8 Jan 2010 00:08:57 +0100
Message-ID: <852badbc1001071508q2fb0739av79bf4d5bf29f3c9a@mail.gmail.com>
Subject: Re: Air-traffic benchmark
From: Lefteris <lsidir@gmail.com>
To: Greg Smith <greg@2ndquadrant.com>
Cc: Jochen Erwied <jochen@pgsql-performance.erwied.eu>,
	pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Thu, Jan 7, 2010 at 11:57 PM, Lefteris <lsidir@gmail.com> wrote:
> Hi Greg,
>
> thank you for your help. The changes I did on the dataset was just
> removing the last comma from the CSV files as it was interpreted by pg
> as an extra column. The schema I used, the load script and queries can
> be found at:
>
> http://homepages.cwi.nl/~lsidir/postgres/
>
> (I understood that if I attach these files here, my email will not
> reach the list so I give you a link to download them).
>
> Also since you are interesting on the benchmark, you can also check
>
> http://homepages.cwi.nl/~mk/ontimeReport
>
> for a report of various experiments with MonetDB and comparison with
> previously published numbers.
>
> The schema I used for pg is slightly different from that one of
> MonetDB since the delay fields could not be parsed by pg as integers
> but only as varchar(4). Hence the extra index on DepDelay field:)

Sorry, I mean the ArrTime DepTime fields were changed, because they
apear on the data as HHMM, but these fields were not used on the
queries. The index on DepDelay was done for q3,4,5 and 7

>
> Also at http://homepages.cwi.nl/~lsidir/PostgreSQL-ontimeReport you
> can see the detailed times I got from postgres.
>
> I really appreciate your help! this is a great opportunity for me to
> get some feeling and insights on postgres since I never had the chance
> to use it in a large scale project.
>
> lefteris
>
>
> On Thu, Jan 7, 2010 at 11:21 PM, Greg Smith <greg@2ndquadrant.com> wrote:
>> Lefteris wrote:
>>>
>>> So we all agree that the problem is on the scans:)
>>>
>>> So the next question is why changing shared memory buffers will fix
>>> that? i only have one session with one connection, do I have like many
>>> reader workers or something?
>>>
>>
>> I wouldn't expect it to. =A0Large sequential scans like this one are opt=
imized
>> in PostgreSQL to only use up a small portion of the shared_buffers cache=
.
>> =A0Allocating more RAM to the database won't improve the fact that you'r=
e
>> spending the whole time waiting for physical I/O to happen very much.
>>
>> What might help is increasing effective_cache_size a lot though, because
>> there you might discover the database switching to all new sorts of plan=
s
>> for some of these queries. =A0But, again, that doesn't impact the situat=
ion
>> where a sequential scan is the only approach.
>>
>> I have this whole data set on my PC already and have been trying to find
>> time to get it loaded and start my own tests here, it is a quite interes=
ting
>> set of information. =A0Can you tell me what you had to do in order to ge=
t it
>> running in PostgreSQL? =A0If you made any customizations there, I'd like=
 to
>> get a copy of them. =A0Would save me some time and help me get to where =
I
>> could give suggestions out if I had a "pgdumpall --schema-only" dump fro=
m
>> your database for example, or however you got the schema into there, and=
 the
>> set of PostgreSQL-compatible queries you're using.
>>
>> By the way: =A0if anybody else wants to join in, here's a script that
>> generates a script to download the whole data set:
>>
>> #!/usr/bin/env python
>> for y in range(1988,2010):
>> =A0 for m in range(1,13):
>> =A0 =A0 =A0 print "wget --limit-rate=3D100k
>> http://www.transtats.bts.gov/Download/On_Time_On_Time_Performance_%s_%s.=
zip"
>> % (y,m)
>>
>> It's 3.8GB of download that uncompresses into 46GB of CSV data, which is=
 why
>> I put the rate limiter on there--kept it from clogging my entire Interne=
t
>> connection.
>>
>> --
>> Greg Smith =A0 =A02ndQuadrant =A0 Baltimore, MD
>> PostgreSQL Training, Services and Support
>> greg@2ndQuadrant.com =A0www.2ndQuadrant.com
>>
>>
>