PostgreSQL Query Performance Tuning Guide: Getting Started (Part 1)

To start with , we will cover following aspects of query tuning in PostgreSQL Database:

1. Find bottlenecks with 'Explain analyze'.
2. Tuning with Indexes.
3. Optimize Joins with key Indexes.
4. Limit rows and columns.
5. Effective Stats housekeeping.
6. Table Partitioning.
7. Avoid leading wildcards in LIKE.
8. Joins vs Sub-queries.

1. Finding bottlenecks with 'Explain analyze'

There are two ways to understand query processing path in PostgreSQL.

First , using Explain command which gives you a query plan without executing the query.

Second, Explain analyse which provides detailed stats for query execution upon executing the actual query.

Example1: A simple query

Select * from student_info order by class;        

For this query with no index but lots of rows , Explain plan is this:

We can see 'Seq scan' on student_info table which means entire table is ben read to execute this query.

Example2: Query with where clause on two columns.

Select * from student_info where name ='b10112' and rollno='10112';        

Let's have a look at it's explain plan and then it's execution plan .

Explain plan - using "Explain select * from student_info where name ='b10112' and rollno='10112';"

Execution plan - This includes above plan as well as query execution stats.

This shows IO as well as planning and query execution time. Also, shows Seq Scan as there are no indexes on table as of now.

2. Tuning with Indexes

Now, we will see performance improvements with index based on above execution plans.

Before indexing: Execution time is 9208 ms and seq scan is there with sort key 'class' so we will create a index on class column.

Create index on class

Create Index idx_student_info_class on student_info(class);        

After indexing: Execution time is 473 ms and instead of seq scan Index scan is there.

There is like 20x improvement with a single index creation at right place.

That's it for now, will write further in next Part of this series.