Assuming what you really want to do is find the most relevant record for a search, neither option is going to give you good performance on a large database. The "right tool for the job" is either the Postgres fulltext search feature, or using a second database like ElasticSearch.

The built-in postgres keyword search isn't as good as ElasticSearch, but it's right at your fingertips when your database is already postgres. It works like this:

1. Declare a column for your keywords, and write some clever code to fill it with all the keyword combinations that matter, including duplicates for the important ones.
2. Put a fulltext index on that column
3. Sort your postgres query by the ts_rank

Here's an example of the DDL:

CREATE TABLE sometable (
  ...
  keyword_search text not null
);
CREATE INDEX sometable_fulltext ON sometable
  USING GIN (to_tsvector('english', keyword_search));
[download]

obviously change 'english' if your users have a different primary language.

If you have multiple columns to search, you can make a generated column that combines them into the keywords:

   keyword_search text not null GENERATED ALWAYS AS (
     name || ' ' || COALESCE(description, '')
     || ' ' || COALESCE(tags, '')) STORED,
[download]

You select from this table like

SELECT * FROM sometable
WHERE to_tsvecor('english',keyword_search) @@ plainto_tsquery('english
+', ?)
ORSER BY ts_rank(to_tsvecor('english',keyword_search), plainto_tsquery
+('english', ?))
[download]

And then substitute your search for both '?' in that query.

If you're interested in integrating this with DBIx::Class, I can show some neat code for that.

The ElasticSearch solution is more involved. You would set up a separate ElasticSsearch database and then every time your application modifies content you also push a summary of that content into the elasticsearch database, then query that database instead of postgres to get your list of entity IDs to show to the user, then query postgres for the entities using the list of IDs, then render them for the user.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.