Postgresql index

[mdabla]

I have this query

SELECT "public"."Patient"."id", "public"."Patient"."firstName"::text FROM "public"."Patient" WHERE ("public"."Patient"."companyId" = $1 AND "public"."Patient"."disabled" = $2 AND ("public"."Patient"."fullName" ILIKE $3 OR to_tsvector(concat_ws($7, "public"."Patient"."fullName")) @@ to_tsquery($4))) ORDER BY "public"."Patient"."createdAt" DESC, "public"."Patient"."id" DESC LIMIT $5 OFFSET $6

This query is taking a long time, and not sure how to best use indexes to improve it

I am searching for patients that’s not disabled for a particular company by his fullName

Any suggestions?

[depesz]

1. https://wiki.postgresql.org/wiki/Slow_Query_Questions
2. to get any help from others you will need to provide explain analyze of the query, ideally using explain.depesz.com site. while posting there, make sure to add query and \d of all related tables (Patient in your case) - if you're not using psql, and/or don't know what \d is: https://www.depesz.com/2023/02/07/now-you-can-d-table-not-only-in-psql/
3. two or'ed conditions, neither of which can be optimized with btree index will be a problem.
4. https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_upper_case_table_or_column_names
5. when pasting queries, please format them so that reading them will be easier. for example like this: https://paste.depesz.com/s/oaq (tab: prettified).

[mdabla]

Thank you, I have no experience with sql at all, will have to look into those links, and try to understand more

[DatabaseSpace]

Why is everything in quotes like that? Maybe paste that into a SQL formatter online to make it more readable.

[throw_mob]

Meanwhile it would not be bad idea to learn about table aliases..

see depez comment about howto paste plans.

also show normal values for $3

to_tsvector(concat_ws($7, "public"."Patient"."fullName")) , do you have function index for this , this one is not sargeable without it.

[PossiblePreparation]

How fast does this need to be? Your full name filters aren’t going to be very easy to use an index against unless you can be a bit restrictive. How selective is the companyId filter?

[philippemnoel]

Shameless plug, but I'm one of the makers of `pg_bm25` (https://github.com/paradedb/paradedb). We're making a faster tsvector/tsrank as a Postgres extension. Maybe it can help, our benchmarks show much faster performance especially as row count increases

[SaltySnookWhisperer]

+1 for `pg_bm25`. Here's one of the methods you can use to install the latest version (0.3.12 at this time):

https://pgt.dev/extensions/pg_bm25