Re: Air-traffic benchmark

Thank you all for your answers!

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

Sort (cost=7407754.12..7407754.13 rows=4 width=2) (actual
time=371188.821..371188.823 rows=7 loops=1)
Seq Scan on ontime (cost=0.00..7143875.40 rows=52775727 width=2)
(actual time=190938.959..346180.079 rows=52484047 loops=1)

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(dot)kretschmer(at)schollglas(dot)com> wrote:
> In response to Lefteris :
>>
>> airtraffic=# EXPLAIN ANALYZE SELECT "DayOfWeek", count(*) AS c FROM
>> ontime WHERE "Year" BETWEEN 2000 AND 2008 GROUP BY "DayOfWeek" ORDER
>> BY c DESC;
>>                                                                QUERY
>> PLAN
>> ------------------------------------------------------------------------------------------------------------------------------------------
>>  Sort  (cost=7407754.12..7407754.13 rows=4 width=2) (actual
>> time=371188.821..371188.823 rows=7 loops=1)
>>   Sort Key: (count(*))
>>   Sort Method:  quicksort  Memory: 25kB
>>   ->  HashAggregate  (cost=7407754.04..7407754.08 rows=4 width=2)
>> (actual time=371163.316..371163.320 rows=7 loops=1)
>>         ->  Seq Scan on ontime  (cost=0.00..7143875.40 rows=52775727
>> width=2) (actual time=190938.959..346180.079 rows=52484047 loops=1)
>>               Filter: (("Year" >= 2000) AND ("Year" <= 2008))
>>  Total 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:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>