From: Heikki Linnakangas <hlinnakangas@vmware.com>
To: Alexander Hill <alex@hill.net.au>
To: pgsql-performance@postgresql.org
Subject: Re: Aggregating tsqueries
Date: Thu, 18 Sep 2014 10:20:41 +0300
Message-ID: <541A87C9.4030002@vmware.com> (raw)
In-Reply-To: <CA+KBOKxv7HOcc=VXpcLZWX=s8=2dFpgOZcFOQC9iY-L60VhB4w@mail.gmail.com>
References: <CA+KBOKxv7HOcc=VXpcLZWX=s8=2dFpgOZcFOQC9iY-L60VhB4w@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 09/17/2014 07:56 AM, Alexander Hill wrote:
> Hello,
>
> I have a table of tree nodes with a tsquery column. To get a subtree's
> tsquery, I need to OR all of its nodes' tsqueries together.
>
> I defined a custom aggregate using tsquery_or:
>
> CREATE AGGREGATE tsquery_or_agg (tsquery)
> (
> sfunc = tsquery_or,
> stype = tsquery
> );
>
> but I've found that
>
> tsquery_or_agg(query)
>
> is about a hundred times slower than this:
>
> ('(' || string_agg(query::text, ')|(') || ')')::tsquery
>
> That works perfectly so I'm happy to continue doing it, but I'm curious to
> know why the difference is so great and if anything can be done about it?
string_agg's state transition function uses a buffer that's expanded as
needed. At every step, the next string is appended to the buffer. Your
custom aggregate is less efficient, because it constructs a new tsquery
object at every step. In every step, a new tsquery object is allocated
and the old result and the next source tsquery are copied to it. That's
much more expensive.
If you're not shy of writing C code, you could write a more efficient
version of tsquery_or_agg too, using a similar technique.
- Heikki
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hlinnakangas@vmware.com, alex@hill.net.au
Subject: Re: Aggregating tsqueries
In-Reply-To: <541A87C9.4030002@vmware.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox