CODEWITHSUNDEEP
sqlpostgresqlcount
Gautam
Gautam

Reputation: 3384

postgres count with like condition

I am new to Postgres and trying to figure out how can i improve performance of count query with 'Like' in where clause.

    select    count(*) 
    from master_data 
    where name ilike '%<userInput>%';

I am even fine with estimated value.I googled few links and found out we can quickly get a count of entire table by using below query.

 SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = '<TableName>';

However for count with like condition i am still not able to figure out. Please suggest.Below are the links which i referred already.

https://wiki.postgresql.org/wiki/Count_estimate

https://www.citusdata.com/blog/2016/10/12/count-performance/#dup_counts_estimated_filtered

Postgres Version - 9.5.4

Upvotes: 1

Views: 341

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You might be interested in a trigram index. This can specifically handle like with wildcards. You can start with the documentation.

Upvotes: 2

Super Mario
Super Mario

Reputation: 939

Do you must use LIKE? Maybe it's possible to make something with IN? http://www.postgresqltutorial.com/postgresql-in/

Upvotes: 3

Related Questions