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=eUTF8/Wur3YEf9MyVgI3Nv5aDkGu9nFEWL7ah4Xfh2t7hA+Ac3Gu7WQI5XV5HEhn3T
	8IkIHqJ/FU1Bg8gvwmzNDfrtZzyUdvCwUZVyVDbuj51Kw6F5hAC3jOedgFUmOxjiGfK/
	pJwN4DrW0MLx9bOicABionidaccPKMtkSiyDA=
MIME-Version: 1.0
In-Reply-To: <20100107133225.GB9884@a-kretschmer.de>
References: <852badbc1001070438j36eb10d3qa22398ffcbef405f@mail.gmail.com>
	<20100107133225.GB9884@a-kretschmer.de>
Date: Thu, 7 Jan 2010 14:40:39 +0100
Message-ID: <852badbc1001070540v6f4f799dyae246d39977fa34f@mail.gmail.com>
Subject: Re: Air-traffic benchmark
From: Lefteris <lsidir@gmail.com>
To: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Thank you all for your answers!

Andrea, I see the other way around what you are saying:

Sort  (cost=3D7407754.12..7407754.13 rows=3D4 width=3D2) (actual
time=3D371188.821..371188.823 rows=3D7 loops=3D1)
Seq Scan on ontime  (cost=3D0.00..7143875.40 rows=3D52775727 width=3D2)
(actual time=3D190938.959..346180.079 rows=3D52484047 loops=3D1)


I dont see the seq scan to ba a problem, and it is the correct choice
here because Year spans from 1999 to 2009 and the query asks from 2000
and on, so PG correctly decides to use seq scan and not index access.

lefteris

On Thu, Jan 7, 2010 at 2:32 PM, A. Kretschmer
<andreas.kretschmer@schollglas.com> wrote:
> In response to Lefteris :
>>
>> airtraffic=3D# EXPLAIN ANALYZE SELECT "DayOfWeek", count(*) AS c FROM
>> ontime WHERE "Year" BETWEEN 2000 AND 2008 GROUP BY "DayOfWeek" ORDER
>> BY c DESC;
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QUERY
>> PLAN
>> ------------------------------------------------------------------------=
------------------------------------------------------------------
>> =A0Sort =A0(cost=3D7407754.12..7407754.13 rows=3D4 width=3D2) (actual
>> time=3D371188.821..371188.823 rows=3D7 loops=3D1)
>> =A0 Sort Key: (count(*))
>> =A0 Sort Method: =A0quicksort =A0Memory: 25kB
>> =A0 -> =A0HashAggregate =A0(cost=3D7407754.04..7407754.08 rows=3D4 width=
=3D2)
>> (actual time=3D371163.316..371163.320 rows=3D7 loops=3D1)
>> =A0 =A0 =A0 =A0 -> =A0Seq Scan on ontime =A0(cost=3D0.00..7143875.40 row=
s=3D52775727
>> width=3D2) (actual time=3D190938.959..346180.079 rows=3D52484047 loops=
=3D1)
>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 Filter: (("Year" >=3D 2000) AND ("Year" <=3D=
 2008))
>> =A0Total runtime: 371201.156 ms
>> (7 rows)
>>
>>
>> I understand that the problem here is the memory used by the sort
>> method. *But*, I already changed the work_mem parameter to 6gigs:)
>
> No.
>
> The problem here is the Seq-Scan. It returns about 52.000.000 rows,
> approximately roughly table, it needs 346 seconds.
>
> The sort needs only 25 KByte and only 0.02ms.
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: =A0Heynitz: 035242/47150, =A0 D1: 0160/7141639 (mehr: -> Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 =A02EB0 389D 1DC2 3172 0C99
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org=
)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>