Kaoru
Kohashigawa

Tuning Postgresql Queries


postgresql database database index benchmark sift science

![Beyond the Cosmos](https://images.unsplash.com/photo-1488229297570-58520851e868?dpr=2&auto=format&fit=crop&w=400&q=80&cs=tinysrgb) <br>[Photo by Joshua Sortino](https://unsplash.com/@sortino) ### TLDR: Lessons learned - Adding an index won't always speed up a query. - PostgreSQL's Query Planner behaves differently depending on the number of rows in the database. - Sorting is more expensive than filtering. - Column order matters when creating an index. - Partial indexes can improve performance but covers less use cases. ### Always Be Benchmarking! There are no hard and fast rules when it comes to optimizing sql queries. At Sift we recently migrated out one of our system's database to PostgreSQL. PostgreSQL acts like a queue holding and sorting tasks to be worked on. Worker threads would pull tasks off the queue and update the results in the database. The queries to claim tasks from PostgreSQL is pretty straight forward: ```psql SELECT * FROM flow WHERE timeout < 1497744525100 AND state = 0 AND state = 1 ORDER BY timeout ASC LIMIT 50; --- And SELECT * FROM flow WHERE timeout > 1497744525100 AND state = 0 ORDER BY last_updated ASC LIMIT 50; ``` During benchmark tests the new design could not keep up with a throughput of a 100 tasks per second. Things started to back up in a really bad way. Throwing up metrics around the work iteration I came to find the slowest part of the iteration wasn't doing the actual work, it was getting tasks to work on! ### Get the Numbers! I ruled out latency right out the gate because these were the only 2 queries taking > 100ms to run. It also wasn't due to network bandwidth because we were pulling out 50 rows at most and the tables were rather skinny. As a sanity check I reduced the `LIMIT` to 10 to see if things improved, it didn't (it actually got worse). Best way to inspect queries in PostgreSQL? [EXPLAIN ANALYZE](https://www.postgresql.org/docs/current/static/using-explain.html#USING-EXPLAIN-ANALYZE). ```psql EXPLAIN ANALYZE SELECT * FROM flow WHERE timeout < 1497744525100 AND (state = 0 OR STATE = 1) ORDER BY timeout ASC LIMIT 50; -- QUERY PLAN -- ------------------------------------------------------------------------------------------------------------------------------------------------ -- Limit (cost=0.43..12.78 rows=50 width=88) (actual time=1790.418..1790.710 rows=50 loops=1) -- -> Index Scan using flow_timeout_idx on flow (cost=0.43..344816.13 rows=1396378 width=88) (actual time=1790.417..1790.702 rows=50 loops=1) -- Index Cond: (timeout < '1497744525100'::bigint) -- Filter: ((state = 0) OR (state = 1)) -- Rows Removed by Filter: 3028429 -- Planning time: 0.305 ms -- Execution time: 1790.764 ms ``` Fantastic! Okay so this query takes about 1.7 seconds to execute, ugh! The query plan translates to: - Using the timeout index, select anything with timeout < 1497744525100 - Select rows with state = 0 or state = 1 (Removing 3,028,429 rows) - Limit by 50 rows Hrmm, we have an index on `state`...why isn't it using that? Oh probably because it's a separate index and queries are limited to 1 index? Cool let's add a multi-column index to help it out! ```psql CREATE INDEX ON flow(timeout, state); EXPLAIN ANALYZE SELECT * FROM flow WHERE timeout < 1497744525100 AND (state = 0 OR STATE = 1) ORDER BY timeout ASC LIMIT 50; -- QUERY PLAN -- ------------------------------------------------------------------------------------------------------------------------------------------------ -- Limit (cost=0.43..12.78 rows=50 width=88) (actual time=1640.746..1641.038 rows=50 loops=1) -- -> Index Scan using flow_timeout_idx on flow (cost=0.43..344816.13 rows=1396378 width=88) (actual time=1640.745..1641.027 rows=50 loops=1) -- Index Cond: (timeout < '1497744525100'::bigint) -- Filter: ((state = 0) OR (state = 1)) -- Rows Removed by Filter: 3028429 -- Planning time: 0.379 ms -- Execution time: 1641.075 ms -- (7 rows) ``` Nope! The query planner ignored the new index, what the deuce? ### Get specific Thinking about what the query plan is trying to tell me...I think it's using the index to select the rows with timeout < 1497744525100 and it's _not_ using an index to select rows with state 0 or 1, so that means **it has to compare each row's state to the condition**. That's 3,028,429 + 1,396,378 = 4,424,807 rows!! That's a ton of rows! Okay okay, we have to compare less rows, which means leveraging the timeout index to filter out even _more_ rows. Playing around with the data more I noticed the majority of the rows in the table had a negative timeout. Soo..... ```psql EXPLAIN ANALYZE SELECT * FROM flow WHERE timeout < 1497744525100 AND timeout > 0 AND (state = 0 OR state = 1) ORDER BY timeout ASC LIMIT 50; -- QUERY PLAN -- --------------------------------------------------------------------------------------------------------------------------------------- -- Limit (cost=0.43..225.64 rows=50 width=88) (actual time=0.030..0.137 rows=50 loops=1) -- -> Index Scan using flow_timeout_idx on flow (cost=0.43..67049.45 rows=14886 width=88) (actual time=0.029..0.132 rows=50 loops=1) -- Index Cond: ((timeout < '1497744525100'::bigint) AND (timeout > 0)) -- Filter: ((state = 0) OR (state = 1)) -- Planning time: 0.381 ms -- Execution time: 0.179 ms -- (6 rows) ``` Sweet! Adding an index here didn't help us because the query planner still had to filter through millions of rows. Next! ### Index column order matters ```psql EXPLAIN ANALYZE SELECT * FROM flow WHERE timeout > 1497744525100 AND state = 0 ORDER BY last_updated ASC LIMIT 50; -- QUERY PLAN -- --------------------------------------------------------------------------------------------------------------------------------------------------- -- Limit (cost=116966.60..116966.72 rows=50 width=88) (actual time=1206.017..1206.034 rows=50 loops=1) -- -> Sort (cost=116966.60..118528.06 rows=624586 width=88) (actual time=1206.015..1206.025 rows=50 loops=1) -- Sort Key: last_updated -- Sort Method: top-N heapsort Memory: 32kB -- -> Index Scan using flow_state_idx on flow (cost=0.43..96218.30 rows=624586 width=88) (actual time=0.140..831.204 rows=1239683 loops=1) -- Index Cond: (state = 0) -- Filter: (timeout > '1497744525100'::bigint) -- Rows Removed by Filter: 501 -- Planning time: 0.328 ms -- Execution time: 1206.083 ms -- (10 rows) ``` Here's the breakdown of what's going: - Using an index on state get all the rows where state is 0. - Compare and select rows where timeout is greater than 1497744525100 (501 rows were removed). - Sort 624,586 rows by last_updated. - Limit by 50. We can actually see what most of the time is being spent on by looking at `actual time`. Sorting thew 624,586 rows in memory takes up the most time! Gross. Let's add an index! ```psql CREATE INDEX on flow(timeout, state, last_updated); EXPLAIN ANALYZE SELECT * FROM flow WHERE timeout > 1497744525100 AND state = 0 ORDER BY last_updated ASC LIMIT 50; -- QUERY PLAN -- --------------------------------------------------------------------------------------------------------------------------------------------------- -- Limit (cost=116966.60..116966.72 rows=50 width=88) (actual time=1179.820..1179.835 rows=50 loops=1) -- -> Sort (cost=116966.60..118528.06 rows=624586 width=88) (actual time=1179.819..1179.829 rows=50 loops=1) -- Sort Key: last_updated -- Sort Method: top-N heapsort Memory: 32kB -- -> Index Scan using flow_state_idx on flow (cost=0.43..96218.30 rows=624586 width=88) (actual time=0.077..810.652 rows=1239683 loops=1) -- Index Cond: (state = 0) -- Filter: (timeout > '1497744525100'::bigint) -- Rows Removed by Filter: 501 -- Planning time: 0.451 ms -- Execution time: 1179.879 ms -- (10 rows) ``` Uhh wait what?! It's not using the index at all! Digging around the internet it appears the order of the columns in an index matters. The most expensive operation this query has to do is sort, so if it can sort first then match the conditions I bet things will look a lot better! ```psql CREATE INDEX on flow(last_updated, timeout, state); EXPLAIN ANALYZE SELECT * FROM flow WHERE timeout > 1497744525100 AND state = 0 ORDER BY last_updated ASC LIMIT 50; -- QUERY PLAN -- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- -- Limit (cost=0.56..38.47 rows=50 width=88) (actual time=140.145..140.243 rows=50 loops=1) -- -> Index Scan using flow_last_updated_timeout_state_idx on flow (cost=0.56..473538.62 rows=624586 width=88) (actual time=140.143..140.230 rows=50 loops=1) -- Index Cond: ((timeout > '1497744525100'::bigint) AND (state = 0)) -- Planning time: 0.357 ms -- Execution time: 140.296 ms -- (5 rows) ``` Huzzah! Way faster, yet... not fast enough, we're aiming for < 50ms. ### Partial Index Poking around the internet some more I stubbled on Heroku's blog post, "[Efficient Use of PostgreSQL](https://devcenter.heroku.com/articles/postgresql-indexes)". > A partial index covers just a subset of a table’s data. It is an index with a WHERE clause. The idea is to increase the efficiency of the index by reducing its size. A smaller index takes less storage, is easier to maintain, and **is faster to scan**. For this particular query we can create an index that mirrors the exact `WHERE` clause which in turn will make the index smaller and the scan blazing fast! ```psql CREATE index scheduler_query_idx ON flow (last_updated, timeout, state) WHERE timeout > 0 AND state = 0; EXPLAIN ANALYZE SELECT * FROM flow WHERE timeout > 1497744525100 AND state = 0 ORDER BY last_updated ASC LIMIT 50; -- QUERY PLAN -- ------------------------------------------------------------------------------------------------------------------------------------------------------------- -- Limit (cost=0.43..24.68 rows=50 width=88) (actual time=0.077..0.189 rows=50 loops=1) -- -> Index Scan using flow_last_updated_timeout_state_idx1 on flow (cost=0.43..302935.79 rows=624586 width=88) (actual time=0.075..0.180 rows=50 loops=1) -- Index Cond: (timeout > '1497744525100'::bigint) -- Planning time: 2.255 ms -- Execution time: 0.227 ms -- (5 rows) ``` Dang....that's what I'm talking about! ### Knowing the Trade Offs Having an understanding of what PostgreSQL is doing under the hood is key to understanding the trade offs you're making to accomplish your goal. Blindly throwing indexes at the problem doesn't always work and will sometimes bite you in the end. The partial index we created above will likely only benefit one query. Other parts of the system will have to pay a cost in order to maintain the index. But it's worth the price because this query powers the heart of our work engine and is mission critical. ### References - PostgreSQL Performance tips <br>[https://www.postgresql.org/docs/9.6/static/performance-tips.html](https://www.postgresql.org/docs/9.6/static/performance-tips.html) - Heroku: Efficient Use of PostgreSQL <br> [https://devcenter.heroku.com/articles/postgresql-indexes](https://devcenter.heroku.com/articles/postgresql-indexes) - Understanding Execution Plans <br> [http://www.vertabelo.com/blog/technical-articles/understanding-execution-plans-in-postgresql](http://www.vertabelo.com/blog/technical-articles/understanding-execution-plans-in-postgresql) - Create a similar database to follow along using<br> [https://github.com/KaoruDev/learning_psql/blob/master/create-test-flow.sql](https://github.com/KaoruDev/learning_psql/blob/master/create-test-flow.sql)