<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Kishan Sharma on Medium]]></title>
        <description><![CDATA[Stories by Kishan Sharma on Medium]]></description>
        <link>https://medium.com/@kshnsink?source=rss-7d0b5f791f11------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*Jj5353q84Ls7NAFYWEJXbg.jpeg</url>
            <title>Stories by Kishan Sharma on Medium</title>
            <link>https://medium.com/@kshnsink?source=rss-7d0b5f791f11------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 12:36:55 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@kshnsink/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[The Postgres Performance Tuning Manual: Indexes]]></title>
            <link>https://medium.com/gojekengineering/the-postgres-performance-tuning-manual-indexes-19a1bdfe28ba?source=rss-7d0b5f791f11------2</link>
            <guid isPermaLink="false">https://medium.com/p/19a1bdfe28ba</guid>
            <category><![CDATA[tech]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[how-to]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[postgresql]]></category>
            <dc:creator><![CDATA[Kishan Sharma]]></dc:creator>
            <pubDate>Fri, 24 May 2019 03:55:54 GMT</pubDate>
            <atom:updated>2019-05-24T03:55:54.448Z</atom:updated>
            <content:encoded><![CDATA[<h4>How to modify and improve query times in Postgres with the help of indexes.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZZ-71QI4UBkpSVKpvhHn8A.jpeg" /><figcaption><a href="http://bit.ly/2Wn08ie">gojek.jobs</a></figcaption></figure><p>In a previous post, we discussed how we can use query plans to analyse the run time of queries. In case you missed it, you can read it here:</p><p><a href="https://blog.gojekengineering.com/the-postgres-performance-tuning-manual-query-plans-52a023c2342d">The Postgres Performance Tuning Manual: Query Plans</a></p><p>In this post, we look at how to overcome slow queries by analysing them with Explain and Analyze, and using indexes to modify and enhance the query timings.</p><p>Postgres supports different kinds of indexing on the table for querying faster.</p><h4>Multiple column indexes</h4><p>A <a href="https://www.postgresql.org/docs/9.6/indexes-multicolumn.html">multi-column B-Tree index</a> can be used with query conditions that involve any subset of the index’s columns. This index is most efficient when there are constraints on the leading <em>(</em>leftmost<em>)</em> columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned.</p><h4>Cover-Index</h4><p>An index containing all the columns needed for a query, which is there in the select statement.</p><h4>Unique Index</h4><p>A unique index is an index used to enforce uniqueness of a column’s value or the uniqueness of a combined value of more than one column.</p><blockquote>One of the most misunderstood concepts around indexing is to understand where to use a primary key, unique constraint, or unique index. Let’s understand this using a problem:</blockquote><h3>Problem statement</h3><p>We require maximum performance with no duplicate data. Which is the better approach? Primary key, unique constraint or unique index?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9h5gH9IxA5wxFgSX-qaoJw.jpeg" /></figure><h3>The Solution</h3><blockquote>Note: Multiple null values are not equal, so they are not considered as a duplicate record.</blockquote><ul><li>Postgres automatically creates a <a href="https://www.postgresql.org/docs/9.4/indexes-unique.html">unique index</a> in the table when a primary key and unique constraint is defined in the table. Creating unique constraint then would be <strong>redundant,</strong> and unnecessarily creating indexes degrades the performance of Postgres. According to suggestions by the Postgres product team, create a unique constraint on the table and then there is no need to create a unique index on those columns.</li><li>Postgres creates an index for the defined primary key itself.</li><li>When we create a unique constraint, Postgres automatically creates an index behind the scene.</li></ul><p>However, there are cases where even indexing can’t improve performance. One such case is when we do case-insensitive searches. Let’s understand the difference between the query cost in case of <em>case sensitive </em>and<em> case insensitive </em>search<em> </em>in our scheme table. Given we have an index on the column scheme_name.</p><p>EXPLAIN ANALYSE SELECT * FROM schemes where scheme_name = &#39;weekend_scheme&#39;</p><blockquote><em>QUERY PLAN | Index scan using idx_scheme_name on schemes (cost=0.28..8.29 rows=1 width=384)<br>Planning Time: 0.155 ms<br>Execution Time: 0.063ms</em></blockquote><p>EXPLAIN ANALYSE SELECT * FROM schemes where lower(scheme_name) = &#39;weekend_scheme&#39;</p><blockquote><em>QUERY PLAN | Seq Scan on schemes (cost=0.00..69.00 rows=5 width=384)<br>Filter: (lower((scheme_name) :: text) = ‘weekend_scheme’ :: text)<br>Rows removed by filter: 999<br>Planning Time: 0.119 ms<br>Execution Time: 0.721ms</em></blockquote><p>Even though we have an index created at scheme_name, the function lowerdegrades the performance as it does an additional effort of converting all the values of scheme_table to lower case.</p><p>Cases when an index is not used (although it is defined).</p><ul><li>LIKE ‘%scheme’will never use an index, but LIKE ‘scheme%’ can possibly use the index.</li><li>The upper/lower case function used in where clause.</li></ul><p>So whenever we want to use a function in our where clause, we could create the index in the following way to optimise the query. CREATE INDEX idx_scheme_name ON schemes (lower(scheme_name))</p><p>EXPLAIN ANALYSE SELECT * FROM schemes where lower(scheme_name) = &#39;weekend_scheme&#39;</p><blockquote><em>QUERY PLAN | Bitmap heap scan on schemes ((cost=4.32..19.83 rows=5 width=384))<br>Recheck cond: (lower ((scheme_name) :: text) = ‘weekend_scheme’ :: text)<br>Heap Blocks: exact=1<br>Bitmap scan on schemes ((cost=0.00..4.32 rows=5 width=0))<br>Index cond: (lower ((scheme_name) :: text) = ‘weekend_scheme’ :: text)<br>Planning Time: 1.784 ms<br>Execution Time: 0.079 ms</em></blockquote><h4>Partial Index</h4><p>Postgres supports an index over a subset of the rows of a table (known as a partial index).<strong> </strong>It’s often the best way to index our data if we want to repeatedly analyse rows that match a given WHERE clause. Let us see how we can enhance the performance of Postgres using partial indexing.</p><h3><strong>Problem statement</strong></h3><p>We want to return all the schemes which are supposed to run before 11:00 am.<br>EXPLAIN ANALYSE SELECT * FROM schemes WHERE start_time &lt; &#39;10:00:00&#39;</p><blockquote><em>QUERY PLAN | Seq Scan on schemes (cost=0.00..66.50 rows=9 width=23)<br>Filter: (start_time &lt; ’10:00:00&#39;)<br>Rows removed by filter: 991<br>Planning Time: 0.082 ms<br>Execution Time: 0.226ms</em></blockquote><p>We can create an index on start_time column but assuming we have a huge database, this may not be optimal for insert, update and delete. So we create an index with a condition. This kind of indexing is used when we know what we need from our select queries. Say we do a heavy read on all the schemes which are started before 10:00:00 and not much when started later.<br>CREATE INDEX idx_scheme_name ON schemes start_time WHERE start_time &lt; &#39;11:00:00&#39;</p><p>EXPLAIN ANALYSE SELECT * FROM schemes WHERE start_time &lt; &#39;10:00:00&#39;</p><blockquote><em>QUERY PLAN | Bitmap heap scan on schemes ((cost=4.21..29.30 rows=9 width=23))<br>Recheck cond: (start_time &lt; ’10:00:00&#39;)<br>Heap Blocks: exact=8<br>Bitmap index scan on schemes ((cost=0.00..4.21 rows=9 width=0))<br>Index cond: (start_time &lt; ’10:00:00&#39;)<br>Planning Time: 1.729 ms<br>Execution Time: 0.075 ms</em></blockquote><p>This reduces the execution time from 0.226to 0.075.</p><p>Let’s validate that we have not indexed all the schemes where start_time is after 11:00 AM. <br>EXPLAIN ANALYSE SELECT * FROM schemes WHERE start_time &gt;&#39;12:00:00&#39;</p><blockquote><em>QUERY PLAN | Seq Scan on schemes (cost=0.00..66.50 rows=6 width=23)<br>Filter: (start_time &lt; ’12:00:00&#39;)<br>Rows removed by filter: 993<br>Planning Time: 0.101 ms<br>Execution Time: 0.228ms</em></blockquote><p>This proves that partial data from schemes table is indexed and the rest of the data is not indexed. Our index size is very small and easy to maintain, helping in the maintaining task of reindexing.</p><h4>Query plan on JOINS</h4><p>The optimizer needs to pick the correct <em>join </em>algorithm when there are multiple tables to be joined in the select statement. Postgres uses 3 different kinds of join algorithm based on the type of join we are using.</p><p><strong>Nested Loop: </strong>Here, the planner can use either sequential scan or index scan for each of the elements in the first table. The planner uses a sequential scan when the second table is small. The basic logic of choosing between a sequential scan and index scan applies here too.<br><strong>Hash Join: </strong>In this algorithm, the planner creates a hash table of the smaller table on the join key. The larger table is then scanned, searching the hash table for the rows which meet the join condition. This requires a lot of memory to store the hash table in the first place.<br><strong>Merge Join: </strong>This is similar to merge sort algorithm. Here the planner sorts both the tables to be joined on the join attribute. The tables are then scanned in parallel to find the matching values.</p><p>EXPLAIN SELECT schemes.rules FROM scheme_rules JOIN schemes ON (scheme_rules.scheme_id = schemes.id ) where scheme_name = &#39;weekend_scheme&#39;;</p><h3>Downsides of indexes in production environments</h3><h4>Finding unused indexes</h4><p>In a large production environment, finding unused indexes is advisable, because indexes eat memory. <a href="https://wiki.postgresql.org/wiki/Index_Maintenance">Postgres wiki page</a> details how we can find index summary, duplicate indexes, and index size.</p><h4>CREATE/DROP index vs CREATE/DROP index concurrently</h4><p>Creating and dropping an index in a large database can take hours or even days and the CREATE INDEX command blocks all the writes on a table (it doesn’t block the reads, but this is still not ideal).</p><p>However, an index created concurrently withCREATE INDEX CONCURRENT will not acquire locks against writes. When creating index concurrently, Postgres first scans the table to build indexes and runs the index once again for the things to be added since the first pass.</p><p>Creating an index concurrently also has a downside though. If something goes wrong during the process, it does not roll back, and leaves an invalid index behind. Invalid indexes can be found using the following query.</p><pre>SELECT * FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;</pre><h4>Rebuilding indexes</h4><p><a href="http://(https//www.postgresql.org/docs/9.4/sql-reindex.html"><strong>REINDEX</strong></a> rebuilds an index using the data stored in the index table, replacing the old copy of the index. If we suspect corruption of an index on a table, we can simply rebuild that index, or all indexes on the table, using REINDEX INDEXor REINDEX TABLE</p><p><em>REINDEX</em> is similar to a drop and recreate of the index in that the index contents are rebuilt from scratch. However, locking considerations are rather different. <em>REINDEX</em> locks out writes but not reads of the index’s parent table. It also takes an exclusive lock on the specific index being processed, which will block reads that attempt to use that index.</p><blockquote>Another option is to drop index concurrently and create again concurrently.</blockquote><h4>Conclusion</h4><p>This post aimed to provide an overview on how Postgres queries the database. By understanding query plans better and carefully taking measures (mostly through indexes), we can get the best performance out of the Postgres database.</p><p>There are other ways to enhance query performance, but we’ll save those for later posts.</p><h4>Further Reading</h4><ul><li><a href="https://www.postgresql.org/docs/8.1/index-locking.html">Index locking consideration</a></li><li><a href="https://www.postgresql.org/docs/8.4/locking-indexes.html">Locking indexes</a></li><li><a href="https://www.postgresql.org/docs/current/routine-reindex.html">Routine reindexing</a></li><li><a href="https://www.postgresql.org/docs/9.3/indexes-examine.html">Examining index usage</a></li><li><a href="https://www.postgresql.org/docs/9.3/monitoring-stats.html">Monitoring stats</a></li></ul><p>Efficient, optimised, lean… These are words you’ll often hear at our offices. At GOJEK, getting something to work is only half the battle. Getting it to work as efficiently as possible without minimum intervention, that’s when we do the high fives. We are also looking for more people to join out teams. Head over to <a href="http://bit.ly/2Wn08ie">gojek.jobs</a>, and grab the chance to help us build better, more efficient solutions.</p><figure><a href="http://bit.ly/2Wn08ie"><img alt="" src="https://cdn-images-1.medium.com/max/892/1*PdKKvtpvxTQcbMlDvIlFzQ.png" /></a><figcaption>gojek.jobs</figcaption></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=19a1bdfe28ba" width="1" height="1" alt=""><hr><p><a href="https://medium.com/gojekengineering/the-postgres-performance-tuning-manual-indexes-19a1bdfe28ba">The Postgres Performance Tuning Manual: Indexes</a> was originally published in <a href="https://medium.com/gojekengineering">Gojek Product + Tech</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The Postgres Performance Tuning Manual: Query Plans]]></title>
            <link>https://medium.com/gojekengineering/the-postgres-performance-tuning-manual-query-plans-52a023c2342d?source=rss-7d0b5f791f11------2</link>
            <guid isPermaLink="false">https://medium.com/p/52a023c2342d</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[tech]]></category>
            <category><![CDATA[how-to]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[postgresql]]></category>
            <dc:creator><![CDATA[Kishan Sharma]]></dc:creator>
            <pubDate>Tue, 07 May 2019 04:01:01 GMT</pubDate>
            <atom:updated>2019-05-28T05:55:39.535Z</atom:updated>
            <content:encoded><![CDATA[<h4>Is Postgres slowing down on you? Here’s how to identify areas of improvement using query plans.</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jPvkS_SsaVulZwG47pcRbQ.jpeg" /><figcaption><a href="http://bit.ly/2H2hW8Q">gojek.jobs</a></figcaption></figure><p>Postgres is one of the most widely used open source databases in the world. At GOJEK, a lot of our products depend on Postgres as well. However, when you’re a building and operating a Super App at scale, the volume of data passing through the pipelines can slow down even the most efficient systems.</p><p>Sometimes, we witness degraded performance on Postgres too. That’s not a sustainable thing for a startup like GOJEK.</p><blockquote>We needed to optimise things to enhance performance. So we targeted the optimizer.</blockquote><p>The optimizer is the brain of the database, which interprets queries and determines the fastest method of execution. A single query optimization technique can increase database performance drastically.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/341/1*8alwVGt6Ph2CB4bzxFX9eA.png" /><figcaption>Postgres query lifecycle</figcaption></figure><p>This post outlines how to analyse Postgres performance using tools such as EXPLAIN and ANALYZE (which Postgres provides).</p><h4>Meet EXPLAIN &amp; ANALYZE</h4><p>EXPLAIN<strong> </strong>gives an exact breakdown of a query. The execution plan is based on the statistics about the table, and it identifies the most efficient path to the data. This takes different database paradigms (such as indexes) into consideration. EXPLAIN only guesses a plan that it thinks it will execute.</p><p>This is where ANALYZE comes into the picture. ANALYZE basically runs a query to find the processing time to execute a query.</p><p>To quickly summarise, EXPLAIN and ANALYZE commands help improve database performance in Postgres by:</p><blockquote>a) Displaying the execution plan that the PostgreSQL planner generates for the supplied statement</blockquote><blockquote>b) Actually running the command to show the run time.</blockquote><h3>Finding the framework</h3><p>Let’s consider we have a table named schemes.</p><p>EXPLAIN SELECT * FROM schemes;</p><blockquote>QUERY PLAN | Seq Scan on schemes (cost=0.00..64 rows=328 width=479)</blockquote><p>SELECT * FROM pg_class where relname = &#39;schemes&#39;;</p><p>pg_class has the metadata about the tables.</p><p>Cost in the query plan is calculated using the following formula</p><p><em>COST = (disk read pages * seq_page_cost) + (rows scanned * cpu_tuple_cost).</em></p><ul><li>Disk read pages and rows scanned are the properties of pg_class.</li><li>Seq page cost is the estimated cost of disk read fetch.</li><li>Cpu tuple cost is the estimated cost of processing each row.</li></ul><p>e.g. COST <em>(</em>54 * 1.0<em>)</em> + <em>(</em>1000 * .01<em>)</em> = 64</p><p>Let’s see how the query plan changes when we apply filters in the select statement.</p><p>EXPLAIN SELECT * FROM schemes WHERE status = ‘active’;</p><blockquote>QUERY PLAN | Seq Scan on schemes (cost=0.00..66.50 rows=960 width=384) Filter: ((status)::text = ‘active’::text)</blockquote><p>The estimated cost here is higher than the previous query, this is because Postgres is performing a seq scan over 1000 rows first and then filtering out the rows based on the WHEREclause.</p><h4>Planner cost constants</h4><p>The cost variables described in this section are measured on an arbitrary scale. Only their relative values matter, hence scaling them all up or down by the same factor will result in no change in the planner’s choices. By default, these cost variables are based on the cost of sequential page fetches; that is, seq_page_cost is conventionally set to 1.0 and the other cost variables are set with reference to that. But you can use a different scale if you prefer, such as actual execution times in milliseconds on a particular machine.</p><p>Unfortunately, there is no well-defined method for determining ideal values for the cost variables. They are best treated as averages over the entire mix of queries that a particular installation will receive. This means that changing them on the basis of just a few experiments is very risky. (Refer <a href="https://www.postgresql.org/docs/current/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS"><em>planner cost constants</em></a><em>).</em></p><p>EXPLAIN SELECT schemes.rules FROM scheme_rules JOIN schemes ON (scheme_rules.scheme_id = schemes.id ) where scheme_name = &#39;weekend_scheme&#39;;</p><p>The query planner sometimes decides to use a two-step plan. The reason for using two-plan node is the first plan sorts the row locations identified by the index into physical order before reading them, and the other plan actually fetches those rows from the table.</p><p><a href="http://bit.ly/2H2hW8Q">GOJEK Careers: Check out the current job openings at GOJEK Tech</a></p><h3>Down to the nuts and bolts</h3><p>The first plan which does the sorting is called <strong>Bitmap scan</strong>.</p><p>Most common occurring matches are scanned using the <em>seq scan</em> and the least common matches are scanned using <em>index</em> <em>scan</em>, anything in between is scanned using <em>bitmap heap scan</em> followed by an <em>index scan</em>. One of the reasons for this is that the random I/O is very slow as compared to sequential I/O. This is all driven by analysing statistics.</p><p><strong>Bitmap heap scan</strong></p><p>A<a href="https://www.postgresql.org/docs/8.1/performance-tips.html"> bitmap heap scan</a> is like a seq scan — the difference being, rather than visiting every disk page, it scans ANDs and ORs of the applicable index together and only visits the disk pages it needs to visit. This is different from index scan where the index is visited row by row in order, which results in disk pages being visited multiple times. A bitmap scan will sequentially open a short list of disk pages and grab every applicable row in each one.</p><p><strong>Sequential scan vs index scan</strong></p><p>There are cases where a sequential scan is faster than an index scan. When reading data from a disk, a sequential method is usually faster than reading in random order. This is because an index scan requires several <em>I/O </em>for each row which includes looking up a row in the index, and based on that, looking up and retrieving the row from memory(heap). On the other hand, a sequential scan requires a single I/O operation to retrieve more than one block containing multiple rows.</p><h3>Query plan on JOINS</h3><p>The optimizer needs to pick the correct <em>join </em>algorithm when there are multiple tables to be joined in the select statement. Postgres uses three different kinds of join algorithms based on the type of join we are using.</p><blockquote>Let’s dive in</blockquote><p><strong>Nested Loop: </strong>Here the planner can use either sequential scan or index scan for each of the elements in the first table. The planner uses a sequential scan when the second table is small. The basic logic of choosing between a sequential scan and index scan applies here too.<br><strong>Hash Join: </strong>In this algorithm, the planner creates a hash table of the smaller table on the join key. The larger table is then scanned, searching the hash table for the rows which meet the join condition. This requires a lot of memory to store the hash table in the first place.<br><strong>Merge Join: </strong>This is similar to merge sort algorithm. Here the planner sorts both the tables to be joined on the join attribute. The tables are then scanned in parallel to find the matching values.</p><blockquote>Further reading on how we can use Explain/Analyze with join statements <a href="https://www.postgresql.org/docs/11/using-explain.html#USING-EXPLAIN-ANALYZE">here</a></blockquote><p>Postgres performance tuning is a complicated task. The complexity comes in identifying the appropriate ‘tunable’ from the many tools that Postgres provides. As you might have now guessed, there is no silver bullet to solving performance issues in Postgres — it’s the use case which dictates the tuning requirements. 😅</p><p>The topics we covered here are about identifying problems and stats. You can read about how we can use this information for a solution to optimise Postgres, here: 👇</p><p><a href="https://blog.gojekengineering.com/the-postgres-performance-tuning-manual-indexes-19a1bdfe28ba">The Postgres Performance Tuning Manual: Indexes</a></p><p>Hope this helped. Keep following the blog for more updates! 🙂</p><p>#DYK that besides building a Super App, we also spend a good chunk of our time on learning and development? Working at GOJEK is as much about acquiring and sharing knowledge as it is about building cool things. Want to be part of this culture of growth? Well, we’re hiring, so head over to <a href="http://bit.ly/2H2hW8Q">gojek.jobs </a>and grab the opportunity to redefine on-demand platforms. And learn something new too. 🙌</p><figure><a href="http://bit.ly/2H2hW8Q"><img alt="" src="https://cdn-images-1.medium.com/max/892/1*PdKKvtpvxTQcbMlDvIlFzQ.png" /></a><figcaption>gojek.jobs</figcaption></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=52a023c2342d" width="1" height="1" alt=""><hr><p><a href="https://medium.com/gojekengineering/the-postgres-performance-tuning-manual-query-plans-52a023c2342d">The Postgres Performance Tuning Manual: Query Plans</a> was originally published in <a href="https://medium.com/gojekengineering">Gojek Product + Tech</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>