Re: Window Function API Performance - Mailing list pgsql-general

From Tom Lane
Subject Re: Window Function API Performance
Date
Msg-id 11435.1315944465@sss.pgh.pa.us
Whole thread Raw
In response to Window Function API Performance  ("Schneider, Thilo" <T.Schneider3@Fraport.de>)
List pgsql-general
"Schneider, Thilo" <T.Schneider3@Fraport.de> writes:
> 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.

It might help to increase work_mem ... if that's not large enough to
hold the whole partition in a tuplestore, performance will definitely
suffer.

Also, make sure you're using a release new enough to have this patch:

Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master Release: REL9_1_BR [244407a71] 2010-12-10 11:33:38 -0500
Branch: REL9_0_STABLE Release: REL9_0_2 [14a58a1c9] 2010-12-10 11:33:38 -0500
Branch: REL8_4_STABLE Release: REL8_4_6 [999e82cc8] 2010-12-10 11:33:38 -0500

    Fix efficiency problems in tuplestore_trim().

    The original coding in tuplestore_trim() was only meant to work efficiently
    in cases where each trim call deleted most of the tuples in the store.
    Which, in fact, was the pattern of the original usage with a Material node
    supporting mark/restore operations underneath a MergeJoin.  However,
    WindowAgg now uses tuplestores and it has considerably less friendly
    trimming behavior.  In particular it can attempt to trim one tuple at a
    time off a large tuplestore.  tuplestore_trim() had O(N^2) runtime in this
    situation because of repeatedly shifting its tuple pointer array.  Fix by
    avoiding shifting the array until a reasonably large number of tuples have
    been deleted.  This can waste some pointer space, but we do still reclaim
    the tuples themselves, so the percentage wastage should be pretty small.

    Per Jie Li's report of slow percent_rank() evaluation.  cume_dist() and
    ntile() would certainly be affected as well, along with any other window
    function that has a moving frame start and requires reading substantially
    ahead of the current row.

    Back-patch to 8.4, where window functions were introduced.  There's no
    need to tweak it before that.

I think Hitoshi-san has some ideas for future performance improvements
in this area, but partitions bigger than working memory are probably
always going to be bad news.

            regards, tom lane

pgsql-general by date:

Previous
From: MirrorX
Date:
Subject: Re: warm standby - apply wal archives
Next
From: Brian Fehrle
Date:
Subject: Query performance help with 'shadow table' approach.