Window Function API Performance - Mailing list pgsql-general

From Schneider, Thilo
Subject Window Function API Performance
Date
Msg-id 6E5A4456615D3C4C897B80ADEFB8C8EE028EEAE4@FRAVS010401.srv.fra.fraport.de
Whole thread Raw
Responses Re: Window Function API Performance  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general

Dear all,

 

first let me thank the PostgreSQL developers for implementing the incredibly helpful window functions. This is one of the features I use most often.

 

But, as my requirements are perhaps a bit special, I always want more ;)

Now I am trying to implement my own window function using C-Language-Functions and the window function API, which, by the way, gives a very nice interface for developing my own window functions.

 

My function needs a very long time to execute while the underlying algorithm should be relatively fast.

 

Currently I access rows of the window partition using the WinGetFuncArgInPartition function. However, what I noticed is that this function seems incredibly slow when the partition looked at is rather large – for my case about 1245000 rows. I call that function about 10 times for each row at the first row of each partition (using more or less a random access schema) and compute some summary statistics. When I just randomly stop the running function with a debugger, it nearly always seems to be caught in BufFileRead(), thus my assumption is that this function and the call to WinGetFuncArgInPartition is the bottleneck of my function.

 

Is anybody able to explain why I get such bad runtimes? And even better, do you see a way to speed up WinGetFuncArgInPartition or replace it by something more performant? Would linear access be faster?

 

Thank you for your support.

Thilo Schneider

 

P.S.: I know my question might be better suited the hackers-list. However, as all questions should be asked elsewhere first, let’s have a try :)

 

 



******************************************

******************************************


Fraport AG
Frankfurt Airport Services Worldwide
60547 Frankfurt am Main

Sitz der Gesellschaft:
Frankfurt am Main
Amtsgericht Frankfurt am Main: HRB 7042
Umsatzsteuer-Identifikationsnummer: DE 114150623

Vorsitzender des Aufsichtsrates:
Karlheinz Weimar - Hessischer Finanzminister a.D.

Vorstand:
Dr. Stefan Schulte (Vorsitzender)
Herbert Mai
Peter Schmitz
Dr. Matthias Zieschang
******************************************
Diese E-Mail kann vertrauliche und/oder rechtlich geschuetzte Informationen enthalten.
Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtuemlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.

This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
******************************************

pgsql-general by date:

Previous
From: Grzegorz Jaśkiewicz
Date:
Subject: Re: 8.4.4 locked after power failure
Next
From: jesuthefrog
Date:
Subject: Re: Need help with what I think is likely a simple query - for each distinct val, return only the record with the most recent date.