In this section, you will learn about the power of correlation and the power of clustered tables. What is the whole idea? Consider you want to read a whole area of data. This might be a certain time range, some block, IDs, or so on.
The runtime of such queries will vary depending on the amount of data and the physical arrangement of data on the disk. So, even if you are running queries that return the same number of rows, two systems might not provide the answer within the same time span, as the physical disk layout might make a difference.
Here is an example:
test=# EXPLAIN (analyze true, buffers true, timing true)
SELECT *
FROM t_test WHERE id < 10000;
QUERY PLAN
----------------------------------------------------------
Index Scan using idx_id on t_test
(cost=0.43..370.87 rows=10768 width=9)
(actual time=0.011..2.897 rows=9999 loops=1)
Index Cond: (id < 10000)
Buffers: shared hit=85
Planning time: 0.078 ms
Execution time: 4.081 ms
(5 rows)
As you might remember, the data has been loaded in an organized and sequential way. Data has been added ID after ID, and so it can be expected that the data will be on the disk in a sequential order. This holds true if data is loaded into an empty table using some auto-increment column.
You have already seen EXPLAIN in action. In this example, EXPLAIN (analyze true, buffers true, and timing true) has been utilized. The idea is that analyze will not just show the plan but also execute the query and show us what has happened.
EXPLAIN analyze is perfect for comparing planner estimates with what really happened. It is the best way to figure out whether the planner was correct or way off. The buffers true parameter will tell us how many 8 k blocks were touched by the query. In this example, a total of 85 blocks were touched. A shared hit means that data was coming from the PostgreSQL I/O cache (shared buffers). Altogether, it took PostgreSQL around 4 milliseconds to retrieve the data.
What happens if the data in your table is somewhat random? Will things change?
To create a table containing the same data but in a random order, you can simply use ORDER BY random(). It will make sure that the data is indeed shuffled on disk:
test=# CREATE TABLE t_random AS SELECT * FROM t_test ORDER BY random(); SELECT 4000000
To ensure a fair comparison, the same column is indexed:
test=# CREATE INDEX idx_random ON t_random (id); CREATE INDEX
To function properly, PostgreSQL will need optimizer statistics. These statistics will tell PostgreSQL how much data there is, how values are distributed, and whether the data is correlated on disk. To speed things up even more, I have added a VACUUM call. Please note that VACUUM will be discussed in more depth later in this book:
test=# VACUUM ANALYZE t_random; VACUUM
Now, let's run the same query as previously:
test=# EXPLAIN (analyze true, buffers true, timing true)
SELECT * FROM t_random WHERE id < 10000;
QUERY PLAN
----------------------------------------------------------------
Bitmap Heap Scan on t_random
(cost=203.27..18431.86 rows=10689 width=9)
(actual time=5.087..13.822 rows=9999 loops=1)
Recheck Cond: (id < 10000)
Heap Blocks: exact=8027
Buffers: shared hit=8057
-> Bitmap Index Scan on idx_random
(cost=0.00..200.60 rows=10689 width=0)
(actual time=3.558..3.558 rows=9999 loops=1)
Index Cond: (id < 10000)
Buffers: shared hit=30
Planning time: 0.075 ms
Execution time: 14.411 ms
(9 rows)
There are a couple of things to observe here. First of all, a staggering total of 8,057 blocks were needed and the runtime has skyrocketed to over 14 milliseconds. The only thing here is that the somewhat rescued performance was the fact that data was served from the memory and not from the disk. Just imagine what it would mean if you had to access the disk 8,057 times just to answer this query. It would be a total disaster because the disk wait would certainly slow things down, dramatically.
However, there is more to see. You can even see that the plan has changed. PostgreSQL now uses a bitmap scan instead of a normal index scan. This is done to reduce the number of blocks needed in the query to prevent the even worse behavior.
How does the planner know how data is stored on the disk? pg_stats is a system view containing all the statistics about the content of the columns. The following query reveals the relevant content:
test=# SELECT tablename, attname, correlation
FROM pg_stats
WHERE tablename IN ('t_test', 't_random')
ORDER BY 1, 2;
tablename | attname | correlation
------------+---------+-------------
t_random | id | -0.0114944
t_random | name | 0.493675
t_test | id | 1
t_test | name | 1
(4 rows)
You can see that PostgreSQL takes care of every single column. The content of the view is created by a thing called ANALYZE, which is vital to the performance:
test=# h ANALYZE
Command: ANALYZE
Description: Collect statistics about a database
Syntax:
ANALYZE [ VERBOSE ] [ table_name [ ( column_name [, ...] ) ] ]
Usually, ANALYZE is automatically executed in the background using the auto-vacuum daemon, which will be covered later in this book.
Back to our query. As you can see, both tables have two columns (id and name). In the case of t_test.id, the correlation is 1, which means that the next value somewhat depends on the previous one. In my example, numbers are simply ascending. The same applies to t_test.name. First, we have entries containing hans and then we have entries containing paul. All identical names are therefore stored together.
In t_random, the situation is quite different; a negative correlation means that data is shuffled. You can also see that the correlation for the name column is around 0.5. In reality, it means that there is usually no straight sequence of identical names in the table, but it rather means that names keep switching all the time when the table is read in the physical order.
Why does this lead to so many blocks being hit by the query? The answer is relatively simple. If the data we need is not packed together tightly but spread out over the table evenly, more blocks are needed to extract the same amount of information, which in turn leads to worse performance.