In psql I have the following query.
Suggestions on how to speed it up/optimize it?
I've tried various indexes on title and headline but they aren't getting used.
"SELECT \"people\".* FROM \"people\" WHERE (((TITLE IS NOT NULL AND title ~* '(^| )(one|two|three)( |$|,)' AND title !~* '(^| )(four|five|six)( |$|,)') OR (TITLE IS NULL AND headline ~* '(^| )(one|two|three)( |$|,)' AND headline !~* '(^| )(four|five|six)( |$|,)')) AND ((TITLE IS NOT NULL AND title ~* '(^| )(seven|eight|nine)( |$|,)' AND title !~* '(^| )(ten|eleven)( |$|,)') OR (TITLE IS NULL AND headline ~* '(^| )(seven|eight|nine)( |$|,)' AND headline !~* '(^| )(ten|eleven)( |$|,)')))"
Here's the EXPLAIN:
Gather (cost=1000.00..286343.58 rows=61760 width=715)
Workers Planned: 2
-> Parallel Seq Scan on people (cost=0.00..279167.58 rows=25733 width=715)
Filter: ((((title IS NOT NULL) AND ((title)::text ~* '(^| )(one|two|three)( |$|,)'::text) AND ((title)::text !~* '(^| )(four|five|six)( |$|,)'::text)) OR ((title IS NULL) AND ((headline)::text ~* '(^| )(one|two|three)( |$|,)'::text) AND ((headline)::text !~* '(^| )(four|five|six)( |$|,)'::text))) AND (((title IS NOT NULL) AND ((title)::text ~* '(^| )(seven|eight|nine)( |$|,)'::text) AND ((title)::text !~* '(^| )(ten|eleven)( |$|,)'::text)) OR ((title IS NULL) AND ((headline)::text ~* '(^| )(seven|eight|nine)( |$|,)'::text) AND ((headline)::text !~* '(^| )(ten|eleven)( |$|,)'::text))))
JIT:
Functions: 2
Options: Inlining false, Optimization false, Expressions true, Deforming true
(7 rows)
Traditional relational databases won't use an index on a column unless the leading part of the column is specified in the condition, ie:
... where my_column like 'FOO%' -- will (usually) use index
... where my_column like '%FOO%' -- will (usually) not use index
To efficiently search for terms within content you need a text-based search technology.
Fortunately, postgres provides support for full text search, which will give you great performance, and convenient syntax, for your task.