Skip to main content
add more explanation and link
Source Link
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.

In this particular case, Postgres identifies a quarter million rows, just to filter the top 15. A giant waste of time. Depending on missing information, some other query plan (some other query) will be much more efficient. Here is a related question with some generic techniques in the answers that apply in similar fashion to your case:

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.

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.

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.

In this particular case, Postgres identifies a quarter million rows, just to filter the top 15. A giant waste of time. Depending on missing information, some other query plan (some other query) will be much more efficient. Here is a related question with some generic techniques in the answers that apply in similar fashion to your case:

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.

added 607 characters in body
Source Link
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.

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.

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.

Source Link
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.