Skip to main content
2 of 3
added 607 characters in body
Erwin Brandstetter
  • 182.2k
  • 28
  • 457
  • 620

The core performance problem is the selectivity estimation for the predicate path ilike '%cat%', which is off by a factor of 130:

(cost=0.00..809.15 rows=1753 width=0) (actual time=2108.286..2108 .286 rows=223590 loops=1)

Probably leading to a sub-optimal query plan.

Selectivity estimation for pattern matching is hard, but there have been major improvements in Postgres 9.6. Detailed explanation in this closely related answer:

Your best course of action may be to install a current version of Postgres.

Of course, there may be all kinds of additional problems, your question does not reveal details.

Or you may be using the wrong tool altogether, you did not define what you are trying to search exactly.


Concerning:

It looks like I need a compound index with the gin, but it won't let me build one based on peers desc nulls last ...

Again: essential details like your CREATE INDEX command and the resulting error msg are missing. My educated guess: You need to install the additional module btree_gin first. You probably know that by now, since you just commented on this related answer explaining as much:

But don't bother, it probably won't help with your query.

Erwin Brandstetter
  • 182.2k
  • 28
  • 457
  • 620