Optimize full text search with a CTE user/dbs/CTE_search
authorDan Scott <dscott@laurentian.ca>
Wed, 16 May 2012 04:29:11 +0000 (00:29 -0400)
committerDan Scott <dan@coffeecode.net>
Thu, 17 May 2012 13:45:49 +0000 (09:45 -0400)
commita10416a99ab2c87a223d9aff91b083633cb790e6
tree467b4fbd7ea09d7f20db51aa34871b08e6a10cf7
parentebdf3e9527c2755195d3126b5468e4636ea5cc6a
Optimize full text search with a CTE

The tsquery used directly in a JOIN was resulting in bad
plan with lots of nested loops and operations. Using a CTE
to define the query improves the plan.

Also, instead of ARRAY_AGG(DISTINCT foo), just return the
whole thing in an array. Not great for dupes, but we can
handle those inside search.query_parser_fts if we really
need to.

Signed-off-by: Dan Scott <dscott@laurentian.ca>
Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm