PostgreSQL Slow Running Queries Tips

• A quick recap! Earlier we saw: - How you can find session and details of slow running query - How you can cancel a slow running query - How you can log a slow running query
• What are we going to do today - See the explain plan and analyze the slow running query - Some basic tips for tuning the query - Headsup for the next session!
• Explain Plan - Syntax: explain [analyze] <query> - Importance- - Gives you important information about query performance and plan - See steps involved in execution - Exposes the statistics assumed by your query for planning the execution How to Read Explain Plan Explain Analyze

Tips for Tuning 

• These are all incremental step ? I prefer implementing in same order - Tune the query first and optimize it to remove costly clauses - Create required indexes if they are missing - Feed the Optimizer with correct info- Tune the optimizer parameters if required - e.g seq_page_cost, random_page_cost, effective_cache_size etc - Provision more resources for hungry (costly) queries- Set some session/transaction/user level parameters for certain costly queries - e.g. work_mem - Consider other changes - e.g application architecture, application logic, hardware upgrade

Tips for Tuning the Query

• Tips 1- Avoid select * - Select only what you need - Everyone wants to use “select *” but no one needs it

 Example

• Tip 2- Filter the rows - Neither you need all the rows in one go nor your application can handle them all - Avoid filtering in application - Fetch only what you need- use WHERE clause - Limit huge recordsets- use LIMIT clause - Iterate using OFFSET clause

Example

• Tip 3- Avoid Sorting - Avoid Sorting if you don’t actually need it - ORDER BY Clause uses sorting memory (work_mem) - If not enough memory then it spills over to disk

 Example

•  Tip 4- UPDATE from a Source Table - In lot of cases one has to update data in one table based on data from another table or with data from another table - Example 

update update_target t1 set columne_to_update=t2.source_for_update , 
t1.column_2_for_update=t2.column_2_source from update_source t2 where t1.column_id=t2.column_id; 

update update_target t1 set columne_to_update =(select source_for_update from 
update_source t2 where t1.column_id=t2.column_id), column_2_for_update=(select column_2_source 
from update_source t2 where t1.column_id=t2.column_id) where exists (select 1 from 
update_source t3 where t3.column_id=t1.column_id);

UPDATE FROM update update_target t1 set columne_to_update=t2.source_for_update , 
t1.column_2_for_update=t2.column_2_source from update_source t2 where t1.column_id=t2.column_id;

Tip 5- Use High School Mathematics - Shift the Calculation from RHS to LHS - Instead of using expressions on Columns use the inverse expression with literals - Examples?

select * from emp where salary+200=1200; select * from emp where salary=1200-200; 
select * from emp where date_trunc(‘d’,hiredatetime)=‘2014-01-03’::date 
select * from emp where hiredatetime>=‘2014-01-03 00:00:00’::timezone and hiredatetime<‘2014-01-03 00:00:00’::timezone +’1 day’::interval

When Basic Tuning Does not Help

• Tip 1- Look for Usual Suspects Try to Look for Costly Clauses in Explain Plan
• Tip 2- Indexing can Help Create Indexes and test them
• Some facts to remember while Indexing - PostgreSQL can do “index-only-scans” - PostgreSQL can do intersection between result of two indexes - You can use filter indexes - Pattern searches may use indexes - e.g. select empid from emp where empname like ‘SA%’ - Pattern searches may not use indexes - e.g. select empid from emp where empname like ‘%ER’ - PostgreSQL has expression based indexes - Indexes can not replace a flawed schema design and poorly written query
• Heads-up for the next Hangout! - We will be discussing some basic configuration tuning techniques