Postgres Pro
Downloads
Home   >   mailing lists

Aggregating tsqueries - Mailing list pgsql-performance

From Alexander Hill
Subject Aggregating tsqueries
Date
Msg-id CA+KBOKxv7HOcc=VXpcLZWX=s8=2dFpgOZcFOQC9iY-L60VhB4w@mail.gmail.com
Whole thread Raw
Responses Re: Aggregating tsqueries
List pgsql-performance
Tree view
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

pgsql-performance by date:

Previous
From: Josh Berkus
Date:
Subject: Yet another abort-early plan disaster on 9.3
Next
From: Heikki Linnakangas
Date:
Subject: Re: Aggregating tsqueries