Improving speed using clustered tables
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. The runtime of such queries will vary depending on the amount of data and the physical arrangement of data on 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 a 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 mundefined
(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 data will be on disk in 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, 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 ways off. The buffers true parameter will tell us how many 8,000 blocks were touched by the query. In this example, a total of 85 blocks were touched. shared hit means that data was coming from the PostgreSQL I/O cache (shared buffers). All together it took PostgreSQL around four 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 random order, you can simply use ORDER BY random(). It will make sure that 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. Those 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 mind that VACUUM will be discussed later in this book in broader detail:
test=# VACUUM ANALYZE t_random;
VACUUM
Now let's run the same query as before:
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 8057 blocks were needed and the runtime has skyrocketed to over 14 milliseconds. The only thing here that somewhat rescued performance was the fact that data was again coming from memory and not from disk. Just imagine what it would mean if you had to access the disk 8057 times just to answer this query. It would be a total disaster because disk wait would certainly slow down things dramatically.
However, there is more to see. You can even see that the plan has changed. PostgreSQL is now using a bitmap scan instead of a normal index scan. This is done to reduce the number of blocks needed in the query to prevent even worse behavior.
How does the planner know how data is stored on disk? pg_stats is a system view containing all the statistics about the content of 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 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 so called autovacuum 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 values 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 physical order.
Why does this lead to so many blocks 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.