Faster array_length()

From: Hadi Moshayedi <hadi(at)moshayedi(dot)net>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Faster array_length()
Date: 2014-05-02 18:55:08
Message-ID: CAK=1=Wr_xjz_h_EuDRRroME9gASURyaowR1A+OO9hUc3dQysaw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

The attached patch improves the performance of array_length() by detoasting
only the overhead part of the datum.

Here is a test case:

postgres=# create table array_length_test as select array_agg(a) a from
generate_series(1, 10000) a, generate_series(1, 10000) b group by b;

Without the patch:

postgres=# select sum(array_length(a, 1)) from array_length_test;
sum
-----------
100000000
(1 row)

Time: 199.002 ms

With the patch:

postgres=# select sum(array_length(a, 1)) from array_length_test;
sum
-----------
100000000
(1 row)

Time: 34.599 ms

The motivation for patch is that some of our customers use arrays to store
a sequence of tens of thousands of events in each row. They often need to
get the last 10 event for each row, for which we do A[array_length(A, 1) -
9: 1000000] (assuming 1M is an upper-bound. we could use array_length()
instead of this constant too, but that is unnecessary if we know the
upper-bound and only slows down the query). Without this optimization,
array gets detoasted twice. With this patch, array_length() becomes much
faster, and the whole query saves few seconds.

Of course this technique is applicable to some other functions too, but
they have never become a bottleneck for me, so I decided to keep the
changes only to this function.

Another alternative I could think of was introducing python style slicing,
in which negative indexes start from end of array, so -10 means 10th
element from end. I thought this would be a bigger change and is probably
unnecessary, so I decided to improve array_length() instead.

Feedback is welcome.

Thanks,
-- Hadi

Attachment Content-Type Size
faster_array_length_v1.patch text/x-patch 694 bytes

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2014-05-02 19:31:30 Re: [COMMITTERS] pgsql: Fix "quiet inline" configure test for newer clang compilers.
Previous Message Stephen Frost 2014-05-02 18:22:23 Re: pgaudit - an auditing extension for PostgreSQL