background

You Can Always Optimize More | Scaling Postgres 352

Join Over 1,000 Engineers & Get New Episodes Weekly!

In this episode of Scaling Postgres, we discuss how you can always optimize more, avoiding deadlocks, casting & indexes and pg_hint_plan best practices.

Content Discussed

YouTube Video

Podcast Audio

Transcript

Optimizing queries is just one of those things you do with postgres on a pretty regular basis. Maybe you've identified a slow process in your application and maybe the database query is at fault. Maybe it's not optimized as much as it could be. Or maybe your CPU is getting pretty high in the database and you want to optimize all the queries on the system in general to make the performance better. This is something that I typically do for clients and review all the top 10 or top 20 queries that are taking the most time in the database to help drop the database's utilization, mainly CPU utilization, because usually there's always something a little more you can do to make a query faster. Now you need to be careful that you're not compromising update or insert performance, but there's usually always little tweaks that you can do. And also be sure to stay till the end because my consulting corner is going to talk about some things to watch out for when you're doing performance optimization, but I hope you, your friends, family and co workers continue to do well.

Our first piece of content is “JUST BECAUSE YOU'RE GETTING AN INDEX SCAN DOESN'T MEAN YOU CAN DO BETTER!”. This is from pgmustard.com and he's going over the scenario where you have a table with a million rows in it and you look at the explain plan and you are getting an index scan. But there's ways to optimize that even more to make the performance better. So he has a table with three columns in it, ID A and B creates an index on column A and then just does this query where he selects the ID from this table where A equals 42 and B equals 42. And you do get a bitmap index scan on that index on the A column and it returns in about 4.5 milliseconds. So even though you have this and you're getting an index scan, you can still see that there are some issues with the performance because if you look at the rest of the Explain plan, you can see it's removing 1000 rows by the filter, but ultimately it's only delivering one row. So you could probably optimize this better to not have to remove so many rows. Plus you're accessing 928 buffers. Now he says you are getting a bitmap index scan, but would a simple index scan be better? So to do that he turned off bitmap scans and tried it again and it did increase the performance by about 2 milliseconds but it's still r emoving a lot of rows from the filter. So he says, okay, what if we add an index on B because the where clause is wanting to narrow it by A and B. And adding the index on B causes the explain plan to look like this. So you're getting a bitmap index scan on each of the indexes and you can see the performance. It's much better. It's now 0.7 milliseconds, so less than 1 millisecond and the number of buffers accessed is only 8. So it's definitely a lot less work that the database has to do. So then the next thing to try if you are narrowing it by two columns is make it a compound index. So an index that covers both columns. So he creates that here, runs it, and now it's even faster. So now it runs less than 1/10 of a millisecond in terms of execution time and it accesses only four by buffers. But you can do even better because all you're asking for is the ID in the query select id. So you could add that to the index. He just made it a three column index, although you could just add it as an additional payload using the include keyword. So have the index on a B include id. That should allow you to achieve the same results. But what this does is it allows you to only do an index scan, so you don't even have to access the heap. You're still hitting 4 of the buffers, but you don't have to access the heap at all. And it is a just a little bit faster. But these are examples of how you can further and further optimize a query to make it as fast as possible. But again, depending on the other statements in your system, you do want to be cautious that you don't impact insert or update performance. For example, if you had an index on column B and column B gets frequently updated, you're going to lose hot updates because you have an index on the column now and hot updates are basically heap only tuple updates. So you'll probably drop some of your update performance if something like column B is frequently updated and you make it a part of an index. But if you want to learn more. Definitely check out this blog post.

Next piece of content “Debugging deadlocks in Postgres”, this is from incident.io and they talk about what is a lock, what is the deadlock. And basically a deadlock happens when we have some sort of transaction that's doing a defined set of operations and one process does it in the order 1, 2, 3, whereas another process does it in the order 3, 2, 1. Well, it's possible that within those transactions, one is waiting on the lock for the other one, but the other one's already locked what the first one needs. So, for example, if you're updating an incident as part of a transaction, and then another process updates the incident summary, then this is locked. When this process now tries to update the incident summary, this is locked. But when it tries to update the incident, this is locked by the first process. So it's basically a deadlock. There's no way to resolve it. So postgres chooses one of these processes to kill itself so that the system can continue operating. And then it says, how do you fix a deadlock? Basically, you have to “Redesign your transactions to operate in a consistent order”. So order is very important here. So in the above example, if this was always designed so first you update the incident, and then you update the incident summary, you should avoid the deadlocks in this example because everything is in a consistent order. Now, he says you can bring in explicit locking, like an advisory lock or an explicit lock with four, no key update. But basically you are adding locking or additional latency to your process. And it's generally always better to just use a consistent order if you can. And they had an example here where they were having an issue with a bulk upsert, so they were trying to insert data, and then if it failed, there's some sort of conflict, then do an update. So they had a process that they were working through, but they were running into deadlocks and they were upserting some batch of rows. So they were a little confused on how this could be happening. And what they found was is the rows being inserted were not sorted. In fact, they were ordered randomly. And postgres acquires locks individually and sequentially during a bulk insert. So even though they thought these upserts were pretty much atomic, really, postgres was still individually and sequentially inserting each row, and they could run into a deadlock with that. So basically, they had to fix the sorting order to ensure that whenever they weren't inserting more than one row, the order was always consistent. And that resolved their deadlock issue. But if you want to learn more. Check out this blog post.

Next piece of content, “PostgreSQL: Indexes and casting”. This is from dbi-services.com and this is basically a warning saying, be careful when you're casting columns or data because you could lose your index scans. So for example, he has a table with an a column as an integer. He creates an index on it, and if you query it in this manner, you see you get an index only scan for it. But if you try casting that column to a text in the where clause and say it equals to the text value of one, you're not going to get an index scan because they're now different data types, so casting to a different data type prevents index scans. So you just want to be aware of that because they say they've seen customers deal with this issue.

Next piece of content “Replacing Oracle Hints: Best Practices with pg_hint_plan on PostgreSQL”,  this is from pganalyze.com and in Oracle they have an ability for you to add hints to queries to tell it use this specific index, don't use this index, do the joins in this way. So you can really manipulate how the Oracle Planner works. Whereas postgres doesn't really provide hints and suggests you to set your configuration appropriately, make sure your statistics are up to date and the system should be able to handle appropriate planning of your queries. But there is an extension called PG Hint Plan that allows you to use some of the hints that Oracle offers within postgres, and this post gives some general guidance on its usage. So again, if you're migrating from Oracle to postgres, their recommendation is don't just replicate the Oracle hints. First focus on your configuration, things like your planner cost settings like random page cost and your server configuration parameters such as effective cache size, your workmem and also make sure your statistics are up to date by running analyze frequently enough and making sure that you create additional statistics when appropriate and only then if you're getting poor query plans, use some of the hints in pg_hint_plann and they have this convenient section where they're showing you how to map Oracle hints to pg_hint_plan hints. So they cover all the different hints that are possible and the recommendations with regard to that. So if you're wanting to try to start to use hints with postgres using pg_hint_plan, you may want to check. Out the blog post.

Next piece of content “Indexing Materialzed Views in Postgres”, this is from crunchydata.com and basically materialized views are actual tables. So a normal view, I think of it as a virtual table. It's basically a query definition that's stored in a database, but it doesn't store all of the data in it. So it is not an actual table. You cannot add indexes to it. But the point of a materialize view is to actually create a table out of that query that view essentially. So they have an example here. You create materialized view and gives the query definition and this actually creates a table called recent_product_sales. Now the benefit of this is that you can add indexes to a materialized view, and really you should because it allows you to get much better query performance, of course. Now the other thing to keep in mind is that with a materialized view you can refresh it. Now normally this locks all reads and writes to the table if you just do a refresh materialized view and then give it the name, but you can do it concurrently. The trick with enabling concurrent refreshes is that you do need a unique index on that materialized view. So in this example they create a unique index on the SKU column of the recent product sales and once that index is in place you can do a concurrent refresh of the materialized view. So if you want to learn more. Check out this blog post.

Next Piece of content “CFP talk proposal ideas for POSETTE: An Event for Postgres 2025”. This is from citusdata.com and they have a list of suggestions topic ideas if you would on talks that could be given about Postgres. So if you're interested in potentially presenting at this conference, you can definitely check. Out this blog post.

Next piece of content “PostgreSQL 18: Per-relation cumulative statistics for [auto]vacuum and [auto]analyze”,  this is from dbi-services.com and apparently in 18 we may be getting the additional columns Total Vacuum time and autovacuum time and total analyze time and total auto analyze time. So it helps us see per table how much cumulative time analyze or vacuum is taking to help you to identify what tables are hot with regard to analyzing frequently or by vacuuming. So that's pretty interesting. If you want to learn more definitely. Check out this blog post.

Next piece of content “PostgreSQL Client-side connection pooling in Golang using pgxpool”, this is from hexacluster.ai and when you are using your application framework it's always important to use a connection pool with postgres, although you can use a separate connection pooler like pgbouncer as well. But basically the more that you can reuse connections without having to create a new connection every time for postgres will make your application much more performant and efficient. Constantly connecting and disconnecting to Postgres with its process based model is just not efficient. So definitely using a pooler of some type is important. And if you use go Maybe you'd like to check out how to configure this one.

Okay, now it's time for the Consulting corner. This week I want to talk about something that I sometimes find when doing query optimizations for clients. Sometimes I see functional indexes that were created, but they're still not being utilized. So if you're querying, say, an email field where the email column, you first lower it using a function to lowercase it and compare it to a lowercase version of what you're searching for. Applying that function to a column prevents using a regular index on the email column, you need a functional index on it. You need the index definition to say lower of the email column. And sometimes those are in place, which is good, but maybe some percent of the queries aren't precisely matching how the function is defined. So one example I ran into recently is is that they were concatenating different values and they created a functional index to do that. The problem is there's two ways to do that. One way to do it is using the double pipe symbol to concatenate text strings together. The other way is to use the concat function. So actually the index was defined using the double pipe method, but some percentage of the queries were using the concat function itself, so even the results are equivalent. The planner wasn't identifying that as an index that it could use, so they weren't getting index scans for this query. So the query just had to be aligned with how the index was defined perfectly using the same syntax, and then everything worked. Just something to keep in mind.

 

episode_image