Hello! In this article we are going to talk about postgres like query performance. Also we will talk about improving like query performance.

To stay with me on the same page, you can prepare a world_cities table following my guide about importing csv file into Postgresql using dbeaver.

How Do We Measure?

Each query has a “cost”. Cost is a unit, which is equals to cost of reading one sequential file.

Lower cost usually means faster queries also costs with lower cost usually consumes less resources.

Analyzing Queries

PostgreSQL has EXPLAIN keyword to analyze queries, analyze shows cost and steps of the execution. (Which is an Execution Plan, which will be covered later on this blog)

Analyze keyword only shows the steps and the cost.

If you run EXPLAIN ANALYZE that will run query. It will show execution times instead of only planning.

If you are trying to discover characteristics of an insert query, create a transaction and roll it back.

BEGIN;
EXPLAIN ANALYZE INSERT QUERY;
ROLLBACK;

Difference Between like and ilike

Let’s start with like explain analyze two queries:

explain analyze select  * from world_cities where subcountry like '%a%' order by country asc;
explain analyze select  * from world_cities where subcountry ilike '%a%' order by country asc;

As you can see, ilike query costs more time and resources. Main difference between those two queries is not scan, it scans the same amount of data. But during filter phase it spends more time.

With Sub Queries

You might wonder that how would those queries perform as subqueries?

I modified queries as following:

explain analyze select * from ( select * from world_cities wc where subcountry like '%a%' ) order by country asc;
explain analyze select * from ( select * from world_cities order by country asc ) where subcountry like '%a%';

If you rewrite the same query with different subqueries, the cost will not change.

Index Effect on Postgres Like Query Performance

I want to show you, what happens if we create an index on the column we search.

Let’s start with creating an index.

CREATE INDEX world_cities_subcountry_idx ON public.world_cities (subcountry);

And execute following explain analyze statements again to see if there will be a performance gain:

explain analyze  select  * from world_cities where subcountry like '%a%' order by country asc;
explain analyze select  * from world_cities where subcountry ilike '%a%' order by country asc;

Expected execution time is slightly improved.

As our dataset is small, performance improvement is small.

Summary

When considering PostgreSQL like query performance, it’s essential to evaluate the impact on query speed and resource consumption. The “cost” of a query, measured in units equivalent to reading one sequential file, directly influences its execution time and resource usage. Lower costs generally translate to faster queries and reduced resource consumption. Analyzing queries using the EXPLAIN keyword provides insights into the execution plan and associated costs, while employing the EXPLAIN ANALYZE command allows for a more comprehensive assessment of execution times. Notably, the distinction between the LIKE and ILIKE operators influences query performance, with ILIKE queries typically incurring higher costs due to prolonged filter phase processing. Furthermore, the strategic use of indexes can contribute to optimizing like query performance, as demonstrated through the potential performance gains observed when creating an index on the queried column.

You can also take a look at Postgresql like operator documentation.

What are your thoughts on the PostgreSQL like query performance analysis presented here? Have you encountered similar challenges with query speed and resource consumption in your own database work? Are there specific optimizations or best practices you’ve found effective in improving query performance? Your insights and experiences are valuable contributions to our ongoing discussions on database optimization. Please feel free to share your thoughts and comments below.

Hi! I’m an IT Specialist

I want to hear from you! I am Working with enterprises for 10+ years to improve their infrastructure and efficiency.

Get in touch with me.

Get in touch