Tuning SQL

The first things people do when writing applications is to write code that does the desired job. However, the first solution might not always be the best one. In many cases, the next step is to tune an application to reach higher performance. In this section, you will learn to tune SQL statements efficiently .

Rewriting Queries

Tuning is not only concerned with indexing. Especially complex queries can be made faster by choosing the right SQL code. Most problems can be solved in many ways with SQL.

Imagine a query where we want to find all records that are higher than a certain value or lower than a certain value. The problem can be solved with two different queries:

 SELECT COUNT(*)         FROM perftest         WHERE anumber > 90000000 UNION SELECT COUNT(*)         FROM perftest         WHERE anumber<10000000; 

or

 SELECT COUNT(*)         FROM perftest         WHERE anumber > 90000000                 OR anumber < 10000000; 

The first query calculates the result with the help of two queries that are combined using UNION . The second query simply uses OR and processes the whole operation with only one SELECT statement. Take a look at the execution plans of the two queries. Query number one has a rather complicated execution plan because the result of the two SELECT statements have to be merged. This will be ghastly slow, and it is not a good choice to write an SQL statement like the following:

 performance=#  EXPLAIN SELECT COUNT(*) FROM perftest WHERE anumber > 90000000   UNION SELECT COUNT(*) FROM perftest WHERE anumber<10000000;  NOTICE:  QUERY PLAN: Unique  (cost=391988.01..391988.01 rows=0 width=4)   ->  Sort  (cost=391988.01..391988.01 rows=2 width=4)         ->  Append  (cost=195994.00..391988.00 rows=2 width=4)                 ->  Aggregate  (cost=195994.00..195994.00 rows=1 width=4)                       ->  Seq Scan on perftest  (cost=0.00..193494.00 rows=999998 width=4)                 ->  Aggregate  (cost=195994.00..195994.00 rows=1 width=4)                       ->  Seq Scan on perftest  (cost=0.00..193494.00 rows=1000000 width=4) EXPLAIN 

The second query works more efficiently because the full table scan has only to be performed once:

 performance=#  EXPLAIN SELECT COUNT(*) FROM perftest WHERE anumber > 90000000   OR anumber < 10000000;  NOTICE:  QUERY PLAN: Aggregate  (cost=223244.00..223244.00 rows=1 width=4)   ->  Seq Scan on perftest  (cost=0.00..218494.00 rows=1899998 width=4) EXPLAIN 

The previous example shows that a lot of performance can be gained with very little effort.

Influencing the Optimizer

If you have to deal with extremely complex queries with a dozen of very big tables involved, it may occur that the planner of the database won't find the fastest way through the query. In many cases, even VACUUM ANALYZE may not help the optimizer.

No matter which database you will use, you will always face critical situations where the database's optimizer does not find the fastest way through a query. This is not a problem only affecting PostgreSQL. All databases can do something wrong in special situations, including Oracle, DB2, and Informix databases. It is hard to say which database actually has the optimizer that makes at least mistakes.

But what are the main reasons that prevent a planner from finding the fastest way of executing a query? One explanation for the problem can be very simple. If the number of tables involved in a query increases, the number of possible join orders increases exponentially. If the number of table reaches a certain limit, it is no longer useful for the database.

But what do to if the optimizer of PostgreSQL fails? Since 7.1, it is possible to influence the order PostgreSQL joins the tables. This is an extremely convenient feature because it offers a lot of tuning potential, especially for very complex queries. Before we get to an example, let's take a look at view_perftest :

 performance=#  \   d   view_perftest  View "view_perftest"  Attribute   Type    Modifier -----------+---------+----------  first      integer   astring    text     View definition: SELECT (perftest.anumber / perftest.id), perftest.astring FROM perftest; 

To make the code we are going to write a little easier, we rename the name of the column:

  ALTER TABLE view_perftest RENAME COLUMN "?column?" TO first;  

We want to write a query that retrieves all values where the first column of view_perftest is equal to id in the perftest table and id in the jointab table. The SQL-statement is rather easy but has some tuning potential:

 SELECT perftest.id         FROM view_perftest, perftest, jointab         WHERE view_perftest.first=perftest.id                 AND jointab.id=perftest.id; 

Let's take a look at the execution plan of the query:

 performance=#  EXPLAIN SELECT perftest.id FROM view_perftest, perftest,   jointab WHERE view_perftest.first=perftest.id AND jointab.id=perftest.id;  NOTICE:  QUERY PLAN: Nested Loop  (cost=0.00..956914.02 rows=3 width=16)   ->  Nested Loop  (cost=0.00..16.02 rows=3 width=8)         ->  Seq Scan on jointab  (cost=0.00..1.03 rows=3 width=4)         ->  Index Scan using idx_id_perftest on perftest (cost=0.00..4.98 rows=1 width=4)   ->  Seq Scan on perftest  (cost=0.00..168966.00 rows=10000000 width=8) EXPLAIN 

Wow, that looks awful ! The database has to process nested loops and a sequential scan on the biggest table. Luckily, the other tables are very small and the nested loops won't take too long. Because the first column in view_perftest contains values that have to be calculated, the database can't use an index to query the view efficiently.

As we promised , it is possible to tell the planner in which order the tables have to be joined. A planner is responsible for generating the execution plan of a query so that the database knows how to execute a query best. The following query is equivalent to the query shown previously:

 SELECT perftest.id         FROM view_perftest CROSS JOIN perftest CROSS JOIN jointab         WHERE view_perftest.first=perftest.id                 AND jointab.id=perftest.id; 

Although the query will produce the same result, the execution plan looks different:

 performance=#  EXPLAIN SELECT perftest.id FROM view_perftest CROSS JOIN perftest   CROSS JOIN jointab WHERE view_perftest.first=perftest.id AND   jointab.id=perftest.id;  NOTICE:  QUERY PLAN: Hash Join  (cost=1.04..50588969.10 rows=3 width=16)   ->  Nested Loop  (cost=0.00..50188968.03 rows=10000000 width=12)         ->  Seq Scan on perftest  (cost=0.00..168966.00 rows=10000000 width=8)         ->  Index Scan using idx_id_perftest on perftest (cost=0.00..4.99 rows=1 width=4)   ->  Hash  (cost=1.03..1.03 rows=3 width=4)         ->  Seq Scan on jointab  (cost=0.00..1.03 rows=3 width=4) EXPLAIN 

The execution plan may, at first sight, not look very different but when we try to execute the queries we will see that there is a significant difference in speed.

When executing the first query on the test system we will get theis result:

 real    12m50.877s user    0m0.030s sys     0m0.030s 

The query takes nearly 14 minutes on the test system to be processed but what about the second query:

 real    10m13.528s user    0m0.010s sys     0m0.060s 

The second query is about 20 percent faster ”a significant performance gain.

If you want to gain performance by influencing the way your planner works, you have to do a lot of testing. Testing not only means that you have to see which query is the fastest, you have also to make sure that the query will return the same result. This is very important because the whole process is about achieving higher performance and not about producing bugs .

For checking whether the first and the second query are equal, we can use an SQL statement. We take all records returned by query one, except those returned by query two:

 SELECT count(perftest.id)         FROM view_perftest, perftest, jointab         WHERE view_perftest.first=perftest.id                 AND jointab.id=perftest.id EXCEPT SELECT count(perftest.id)         FROM view_perftest CROSS JOIN perftest CROSS JOIN jointab         WHERE view_perftest.first=perftest.id                 AND jointab.id=perftest.id; 

It can sometimes be useful to tell the planner to use indexes instead of sequential scans . As we have discussed before, indexes can sometimes decrease the performance of your system because the overhead of processing the index can take longer than a sequential scan. In general, the database will find the right decision for you, but if you know for sure that an index scan is more efficient than a sequential scan, PostgreSQL offers possibilities to influence the result of the planner by setting special variables to the desired value. Take a look at the following example:

 performance=#  EXPLAIN SELECT * FROM jointab WHERE id=207;  NOTICE:  QUERY PLAN: Seq Scan on jointab  (cost=0.00..1.04 rows=1 width=20) EXPLAIN 

Because the table contains only a few values, the database decides to use a sequential scan to find the result. Now we want to force the database to use an index. We have to turn sequential scans off:

  SET ENABLE_SEQSCAN TO OFF;  

As you can see in the following code, the database now performs an index scan instead of a sequential scan:

 performance=#  EXPLAIN SELECT * FROM jointab WHERE id=207;  NOTICE:  QUERY PLAN: Index Scan using idx_id_jointab on jointab  (cost=0.00..2.01 rows=1 width=20) EXPLAIN 

Now we want to set ENABLE_SEQSCAN back to the original value:

  SET ENABLE_SEQSCAN TO DEFAULT;  

You can influence more than the way indexes are processed. Since PostgreSQL 7.1, a list of parameters concerning the optimizer can be compiled in postgresql.conf . The following is an overview:

 #       Optimizer Parameters # #enable_seqscan = true #enable_indexscan = true #enable_tidscan = true #enable_sort = true #enable_nestloop = true #enable_mergejoin = true #enable_hashjoin = true #ksqo = false #geqo = true #effective_cache_size = 1000  # default in 8k pages #random_page_cost = 4 #cpu_tuple_cost = 0.01 #cpu_index_tuple_cost = 0.001 #cpu_operator_cost = 0.0025 #geqo_selection_bias = 2.0 # range 1.5-2.0 #       GEQO Optimizer Parameters # #geqo_threshold = 11 #geqo_pool_size = 0  #default based in tables, range 128-1024 #geqo_effort = 1 #geqo_generations = 0 #geqo_random_seed = -1 # auto-compute seed 

In this list, you can see a lot of optimizer parameters. Feel free to modify these parameters to your needs and try to figure out which effect a change of these parameters has on the performance and the behavior of your system. If you want to find out more about postgresql.conf , check out Chapter 6, "Database Administration."

Caching the Result of Functions

In many applications, programmers use self-defined functions to perform special operations. Imagine a situation where a function is used to perform deterministic operations such as calculating the geometric mean of two values. The same values passed to the function will always lead to the same result. If a function is called with the same values several times, it may be useful to cache the result instead of repeatedly calculating it. PostgreSQL is able to cache the result of a function. You only have to tell the database that the result of a function has to be changed when creating it.

We have included a PL/pgSQL function next that supports caching:

 CREATE FUNCTION geomean_c(int4, int4) RETURNS numeric(10,3) AS '         BEGIN                 RETURN numeric_sqrt(* + *);         END; ' LANGUAGE 'plpgsql' WITH (iscachable); 

Before using this function, the PL/pgSQL has to be added to the database:

 [hs@athlon postgres]$  createlang plpgsql performance  

Let's see how it works:

 performance=#  SELECT geomean(94,57);  geomean ----------------  109.9317970380 (1 row) 

Simply add WITH (iscachable) to the definition of the function, and the results will be cached. If the function is very simple, such as the one shown previously, the performance gain won't be very high because parsing the SQL statement, displaying the result, and so on is more complex than performing the calculation. For complex functions, caching is a good idea to speed up your applications.