Performance#
This page discusses ways to improve the performance of SQL queries.
Find out more in the Performance Tips chapter of the official PostgreSQL documentation.
For this page, we need to prepare a special table, tenk.data. This command table is used in official PostgreSQL examples. Check how it should be created here.
cat << EOF > performance_files/init.sql
CREATE TABLE tenk1 (
unique1 int4,
unique2 int4,
two int4,
four int4,
ten int4,
twenty int4,
hundred int4,
thousand int4,
twothousand int4,
fivethous int4,
tenthous int4,
odd int4,
even int4,
stringu1 name,
stringu2 name,
string4 name
);
COPY tenk1 FROM '/data/tenk.data' WITH (FORMAT 'text', DELIMITER E'\t');
EOF
docker run -d --rm --name performance_example_pg\
-e POSTGRES_PASSWORD=postgres\
-v $(pwd)/performance_files/tenk.data:/data/tenk.data\
-v $(pwd)/performance_files/init.sql:/docker-entrypoint-initdb.d/init.sql\
postgres:15.4
sleep 5
docker exec -i performance_example_pg psql -U postgres << EOF
SELECT * FROM tenk1 LIMIT 10;
EOF
9efd24a3c47fcd7817c37bd57232116841b53067754061e81f794fab2b6784ad
unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx
1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx
3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx
9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx
7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx
8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx
5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx
6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx
4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx
3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx
(10 rows)
Note: Don’t forget to stop the container after all.
docker stop performance_example_pg
performance_example_pg
Estimate query#
Quick answer: Use the EXPLAIN ANALYZE clause before the query. Read more details about EXPLAIN clause in the particula sectoin.
The following cell shows the query for which EXPLAIN ANALYZE was applied.
docker exec -i performance_example_pg psql -U postgres << EOF
EXPLAIN ANALYSE
SELECT max(ten) FROM tenk1
GROUP BY string4
ORDER BY SUM(hundred);
EOF
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Sort (cost=520.08..520.09 rows=4 width=76) (actual time=1.248..1.248 rows=4 loops=1)
Sort Key: (sum(hundred))
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=520.00..520.04 rows=4 width=76) (actual time=1.236..1.237 rows=4 loops=1)
Group Key: string4
Batches: 1 Memory Usage: 24kB
-> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=72) (actual time=0.001..0.290 rows=10000 loops=1)
Planning Time: 0.094 ms
Execution Time: 1.284 ms
(9 rows)
At the end of the output, there are fields Planning Time and Execution Time - they indicate exactly what is stated.
Explain query#
You can discover details how engine will execute your query. In postgreSQL for this purposes exists EXPLAIN keyword.
The following cell shows the output of applying EXPLAIN to a typical query.
docker exec -i performance_example_pg psql -U postgres << EOF
EXPLAIN SELECT * FROM tenk1;
EOF
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244)
(1 row)