Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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


Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

Migrating From Oracle to PostgreSQL using ora2pg open source tools

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

How to configure Replication Manager (repmgr) ?