Skip site navigation (1) Skip section navigation (2)

Re: Windowing Function Patch Review -> Performance Comparison.

From: "David Rowley" <dgrowley(at)gmail(dot)com>
To: "'Hitoshi Harada'" <umi(dot)tanuki(at)gmail(dot)com>
Cc: <pgsql-hackers(at)postgresql(dot)org>,<heikki(dot)linnakangas(at)enterprisedb(dot)com>,<sitnikov(dot)vladimir(at)gmail(dot)com>
Subject: Re: Windowing Function Patch Review -> Performance Comparison.
Date: 2008-11-02 14:09:00
Message-ID: F61C88FA61B840428DB0B5756CCB4963@amd64 (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
Hitoshi Harada Wrote:
> Thanks for your test. Didn't post publicly, I've also tested real
> problems and performed better than I thought. If you can afford it,
> could you add selfjoin cases? It's like:

Ok, did self joins with some. I don't know if it's possible with all.

Test   Sub query Self join Vladimir Windowing UOM        Increase %
Test 1 498.00    N/A       N/A      578.00    Trans/Sec  16.06%
Test 2 336.00    424.00    182.78	481.00    Trans/Sec  13.44%
Test 3 1.30      7.59      1.90     8.45      Trans/Sec  11.33%
Test 4 424.00    361.00    182.00   629.00    Trans/Sec  48.35%
Test 5 8.89      N/A       5844.16  31052.69  Trans/Hour 431.35%
Test 6 253.00    N/A       N/A      305.00    Trans/Sec  20.55%

See attached for details.
The increase % column is now:

Window / max ( Sub query, self join, Vladimir ) - 1

Vladimir, I've included your tests too. I understand that people will
probably use this method as sometimes there is little choice to get the
performance that is required.

Hitoshi Harada Wrote:
> 2008/11/2 David Rowley <dgrowley(at)gmail(dot)com>:
> > Obervations:
> >
> > Test 3 and 5 did not seem to make use of an index to get a sorted list
> of
> > results. I disabled enable_seqscan but the planner still failed to
> choose
> > index_scan. Is there any reason for this? Perhaps I'm missing something.
> > Hitoshi, can you take a look at this?

> Ah, good point. Maybe it's because I haven't paid attention to choose
> index_scan for upper sort node. I just put the sort node whatever the
> downer node is, so it might be needed to sink the information down to
> scan choice process that we use sort node upper. Could someone point
> me out how to do it, or which part of the existing code would be a
> good guide?

I know you need to wait for an answer about this, so I'd like to delay any
further performance tests until that's sorted out as it should affect
performance of larger tables quite a bit.


Attachment: performance_tests v2.txt
Description: text/plain (16.8 KB)

In response to


pgsql-hackers by date

Next:From: Zdenek KotalaDate: 2008-11-02 18:44:32
Subject: Re: PG_PAGE_LAYOUT_VERSION 5 - time for change
Previous:From: Stephen FrostDate: 2008-11-02 13:13:32
Subject: Re: WIP: Column-level Privileges

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group