When we look at the execution plan, the view is nowhere to be seen. When talking about the PostgreSQL optimizer and query optimization there is no way to ignore views and subselect handling. Indexes are important! View inlining and subselect flattening In order to optimize the query, PostgreSQL will automatically figure out that we can use an index here. Without this optimization, it would be absolutely impossible to use the index we have just created. Index Scan using idx_x on t_demo (cost=0.28.8.29 rows=1 width=8) That opens the door for important optimizations: What we see here is that PostgreSQL has figured that x and y happen to be 4. Seq Scan on t_demo (cost=0.00.20.00 rows=1 width=8)Īgain, the magic is in the execution plan. What PostgreSQL tries to do here is to derive implicit knowledge about the query. The next optimization on our list is the concept of equality constraints. If you want to learn more about function stability in PostgreSQL, here is more information. The query is many thousands of times faster, because now PostgreSQL can turn it into a constant and thus use the index. What if we try to do the same thing using a STABLE function? The reason is that clock_timestamp() is VOLATILE. In this case, the query needs a whopping 2.6 seconds and eats up a ton of resources. Let’s run the query using a VOLATILE function: We have generated a list of 64 million entries containing 1 row per minute since January 1900, which produces 64 million entries. In other words, the PostgreSQL optimizer cannot see the function as a constant, and has to execute it for every row – as shown in the next example:ĭemo=# CREATE INDEX idx_date ON t_date (x) VOLATILE means that a function is not guaranteed to return the same result within the same transaction given the same input parameters. Let’s create some sample data and sort these differences out: It can even make a major difference – especially if you are using indexes. When creating a function, it makes a difference if a function is created as VOLATILE (default), STABLE, or as IMMUTABLE. Something that is often overlooked is the concept of function stability. While the code is basically the same, the programming language does make a major difference. PL/pgSQL and other stored procedure languages are black boxes to the optimizer, so whether these things are possible or not depends on the type of language used.ĭemo=# CREATE OR REPLACE FUNCTION pl_ld(int)įunction Scan on generate_series x (cost=0.00.2.63 rows=1 width=4) Note that this is only possible in the case of SQL functions. The ld function has been replaced with the underlying log function. The interesting point here can be found in the WHERE clause. Now let’s see what happens in a real query:įunction Scan on generate_series x (cost=0.00.0.18 rows=1 width=4)įilter: (log('2'::numeric, (x)::numeric) = '1000'::numeric) Let’s create a function to calculate a logarithm:ĭemo=# CREATE OR REPLACE FUNCTION ld(int)Ģ^10 = 1024. The goal is to reduce function calls as much as possible and thus speed up the query. One more important technique is the idea of function inlining. PostgreSQL query optimizer: Function inlining That’s why you should try to make sure that the filter is on the right side, and not on the column you might want to index. What we see here is that PostgreSQL does not transform the expression to “x = 8” in this case. Why is that important? In case “x” is indexed (assuming it is a table), we can easily look up 8 in the index.įunction Scan on generate_series x (cost=0.00.0.15 rows=1 width=4) What the system does is to “fold” the constant and instead do “x = 8”. What you can see here is that we add a filter to the query: x = 7 + 1. Let’s see what happens during this process:įunction Scan on generate_series x (cost=0.00.0.13 rows=1 width=4) PostgreSQL constant foldingĬonstant folding is one of the more simplistic and easier things to describe. There is a lot more going on, but it makes sense to take a look at the most basic things in order to gain a good understanding of the process. Note that the techniques listed here are in no way complete. So let’s take a tour through the PostgreSQL optimizer and get an overview of some of the most important techniques the optimizer uses to speed up queries. For many people, the workings of the optimizer itself remain a mystery, so we have decided to give users some insight into what is really going on behind the scenes. Just like any advanced relational database, PostgreSQL uses a cost-based query optimizer that tries to turn your SQL queries into something efficient that executes in as little time as possible. Anti-join exists from_collapse_limit optimizer performance postgresql query volatile
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |