Merge join performance

From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
To: pgsql-patches(at)postgresql(dot)org
Subject: Merge join performance
Date: 2006-08-23 11:57:44
Message-ID: 44EC42B8.7070708@enterprisedb.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

It seems that the page-at-a-time-index-scan patch applied in the spring
caused a slight performance regression to merge joins. The btree
mark/restore became much more expensive, as btmarkpos now has to copy
the array of item pointers retrieved from the current index page. That
adds up, because merge join calls markpos for every tuple.

We can make markpos fast, if we make the copy lazily in _bt_steppage,
see attached patch.

I did some micro-benchmarking of merge join performance, see attached
test. Test results, on my laptop:

8_1_STABLE: 1.77 s
HEAD, with patch: 1.65 s
HEAD, without patch: 2.46 s

The results are pretty stable, within 0.1 s.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Attachment Content-Type Size
mergejointest.sh application/x-shellscript 514 bytes
markopt.diff text/x-patch 5.5 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alban Hertroys 2006-08-23 12:07:49 Re: Queries joining views
Previous Message Greg Stark 2006-08-23 11:54:08 Re: Tricky bugs in concurrent index build

Browse pgsql-patches by date

  From Date Subject
Next Message Teodor Sigaev 2006-08-23 12:08:12 Re: [HACKERS] Use of backslash in tsearch2
Previous Message Karel Zak 2006-08-23 09:17:19 Leaving... (was: Re: [HACKERS] COPY view)