-
Simple Tips for PostgreSQL Query Optimization

  1. Explain analyze 
  2. One index per query
  3. Column order in a multicolumn index
  4. Filters + joins
Image result for optimization of queries in postgresql
Add caption
Reference : https://statsbot.co/blog/postgresql-query-optimization/



Finding Slow Queries



One obvious way to start with tuning is to find specific statements that are performing poorly.


pg_stats_statements



The pg_stats_statements module is a great place to start. It simply tracks execution statistics of SQL statements and can be an easy way to find poor performing queries.Once you have this module installed, a system view named pg_stat_statements will be available with all sorts of goodness. Once it has had a chance to collect a good amount of data, look for queries that have relatively high total_time value. Focus on these statements first.SELECT * FROM pg_stat_statements ORDER BY total_time DESC;


auto_explain



The auto_explain module is also helpful for finding slow queries but has 2 distinct advantages: it logs the actual execution plan and supports logging nested statements using the log_nested_statements option.

Comments

Post a Comment

Popular posts from this blog

-
Node Js Non-Blocking IO Overview of Blocking vs Non-Blocking This overview covers the difference between  blocking  and  non-blocking  calls in Node.js. This overview will refer to the event loop and libuv but no prior knowledge of those topics is required. Readers are assumed to have a basic understanding of the JavaScript language and Node.js  callback pattern . "I/O" refers primarily to interaction with the system's disk and network supported by  libuv . Blocking Blocking  is when the execution of additional JavaScript in the Node.js process must wait until a non-JavaScript operation completes. This happens because the event loop is unable to continue running JavaScript while a  blocking  operation is occurring. In Node.js, JavaScript that exhibits poor performance due to being CPU intensive rather than waiting on a non-JavaScript operation, such as I/O, isn't typically referred to as  blocking . Synchronous methods in t...
Read more
-
Python for Everyone Course  Link :  http://www.py4inf.com/ Book with all materials and lessons :  https://www.py4e.com/ PPTS :
Read more
-
Geoserver Print Module Geoserver print process involves two steps. First at the  server side , you have to configure your yaml file, called config.yaml. Go through the detailed documentation at  MapFish print module documentation page . Once this done, the second step is for  client side . Considering you are using openlayers for your front-end, in order to get the list of visible layers, you will need a simple loop with visibility check, e.g. var layers = "" ; for ( var i = 0 ; i < map . layers . length ; i ++) { if ( map . layers [ i ]. visibility == true ){ //get a string of visible layers layers = layers + map . layers [ i ]. name + ',' } } //remove the trailing ',' layers = layers . slice ( 0 , - 1 ); var maptitle = "This is the map title" ; var mapcomment = "This is the map comment" var printurl = "http://host:post/geoserver/pdf/print.pdf?spec={" units ...
Read more