pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAggregating tsqueries
2+ messages / 2 participants
[nested] [flat]
* Aggregating tsqueries
@ 2014-09-17 04:56 Alexander Hill <alex@hill.net.au>
0 siblings, 1 reply; 2+ messages in thread
From: Alexander Hill @ 2014-09-17 04:56 UTC (permalink / raw)
To: pgsql-performance
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?
Cheers,
Alex
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Aggregating tsqueries
@ 2014-09-18 07:20 Heikki Linnakangas <hlinnakangas@vmware.com>
parent: Alexander Hill <alex@hill.net.au>
0 siblings, 0 replies; 2+ messages in thread
From: Heikki Linnakangas @ 2014-09-18 07:20 UTC (permalink / raw)
To: Alexander Hill <alex@hill.net.au>; pgsql-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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2014-09-18 07:20 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-09-17 04:56 Aggregating tsqueries Alexander Hill <alex@hill.net.au>
2014-09-18 07:20 ` Heikki Linnakangas <hlinnakangas@vmware.com>
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