Query Plan Selection II
September 21, 2023
Data 101, Fall 2023 @ UC Berkeley
Lisa Yan https://fa23.data101.org/
1
LECTURE 09
Review
Review
Rules of Thumb, Part I
Join Orders
Join Approach I: Nested Loop Join
Join Approach II: Sort-Merge Join
Join Approach III: Hash Join
Physical implementations of other binary operators
Double table demo
Rules of Thumb, Part II
Other Knobs beyond Query Optimizatoin
Lecture 09, Data 101 Fall 2023
Query Plan review
3
Match the query plan tree to the corresponding RA expression.
Are the expressions equivalent?
🤔
A.
B.
⨝
T
⨝
R
S
1.
⨝
R
⨝
T
S
⨝
R
⨝
S
T
⨝
S
×
R
T
2.
3.
4.
C.
D.
5678913
Match the query plan tree to the corresponding RA expression. Answer format: 1-A
ⓘ
Click Present with Slido or install our Chrome extension to activate this poll while presenting.
Query Plan review
All query plans and RA expressions�are equivalent and represent�the following SQL query:
SELECT *�FROM R, S, T�WHERE R.a = S.a� AND S.b = T.b;
5
⨝
T
⨝
R
S
1.
⨝
R
⨝
T
S
⨝
R
⨝
S
T
⨝
S
×
R
T
2.
3.
4.
A.
B.
D.
C.
R
R.a = S.a
R.a = S.a
R.a = S.a
R.a = S.a
5678913
Query Plan review
All query plans and RA expressions�are equivalent and represent�the following SQL query:
SELECT *�FROM R, S, T�WHERE R.a = S.a� AND S.b = T.b;
6
⨝
T
⨝
R
S
1.
⨝
R
⨝
T
S
⨝
R
⨝
S
T
⨝
S
×
R
T
2.
3.
4.
B.
D.
A.
C.
Some join orders have cross products—an expensive operation! The optimizer avoids fully implementing cross products where possible.
5678913
Review of last time: Summary of Query Optimizer
7
There are four steps to executing a SQL query:
The query optimizer (i.e., query planner) in PostgreSQL estimates the cost across various plans and picks the plan with the lowest cost.
Today: understand these steps with respect to joins.
5678913
Review of last time: Query Plan Selection
8
We focus on query-centric as an example process to understand performance.
As data engineers, we need to understand what impacts performance.
5678913
Single Table Demo
EXPLAIN computes:
ANALYZE computes:
9
Demo
Single Table Demo
explain analyze select * from actor;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on actor (cost=0.00..97287.91 rows=4167491 width=74) (actual time=7.025..1278.233 rows=4167491 loops=1)
Planning Time: 5.810 ms
Execution Time: 1475.130 ms
// notice start time and end time for op, rows processed, width = size of output tuples from that operator, loops = number of times the operator is executed
explain select * from actor;
QUERY PLAN
----------------------------------------------------------------
Seq Scan on actor (cost=0.00..97287.91 rows=4167491 width=74)
// just planning
explain analyze select id from actor;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on actor (cost=0.00..97287.91 rows=4167491 width=4) (actual time=0.444..1149.775 rows=4167491 loops=1)
Planning Time: 0.023 ms
Execution Time: 1330.497 ms
// width from 74 -> 4; still 4M output tuples
explain analyze select id from actor where id > 4000000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on actor (cost=0.00..107706.64 rows=164823 width=4) (actual time=265.691..352.988 rows=167491 loops=1)
Filter: (id > 4000000)
Rows Removed by Filter: 4000000
Planning Time: 2.099 ms
Execution Time: 360.612 ms
// notice output tuples is now reduced to 160K, and planning has an imperfect estimate.
explain analyze select id, name from actor where id > 4000000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on actor (cost=0.00..107706.64 rows=164823 width=19) (actual time=267.718..359.796 rows=167491 loops=1)
Filter: (id > 4000000)
Rows Removed by Filter: 4000000
Planning Time: 1.096 ms
Execution Time: 367.407 ms
// width reduced
10
Demo
Your turn: Matching
EXPLAIN ANALYZE SELECT id FROM Actor WHERE id > 4000000 AND name='Tom Hanks'; -- 1
QUERY PLAN
------------------------------------------------------------------------------------------------
Seq Scan on actor (cost=0.00..118125.36 rows=1 width=19) (actual time=297.778..297.778 rows=0 loops=1)
Filter: ((id > 4000000) AND ((name)::text = 'Tom Hanks'::text))
Rows Removed by Filter: 4167491
Planning Time: 0.064 ms
Execution Time: 297.791 ms
EXPLAIN ANALYZE SELECT id FROM Actor WHERE id < 4000000 AND name='Tom Hanks'; -- 2
QUERY PLAN
------------------------------------------------------------------------------------------------
Seq Scan on actor (cost=0.00..118125.36 rows=2 width=19) (actual time=58.376..333.873 rows=1 loops=1)
Filter: ((id < 4000000) AND ((name)::text = 'Tom Hanks'::text))
Rows Removed by Filter: 4167490
Planning Time: 0.866 ms
Execution Time: 333.885 ms
EXPLAIN ANALYZE SELECT id FROM Actor; -- 3
QUERY PLAN
------------------------------------------------------------------------------------------------
Seq Scan on actor (cost=0.00..97287.91 rows=4167491 width=4) (actual time=0.007..508.134 rows=4167491 loops=1)
Planning Time: 0.030 ms
Execution Time: 685.475 ms
EXPLAIN ANALYZE SELECT id FROM Actor LIMIT 10; -- 4
QUERY PLAN
------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.23 rows=10 width=4) (actual time=0.029..0.035 rows=10 loops=1)
-> Seq Scan on actor (cost=0.00..97287.91 rows=4167491 width=4) (actual time=0.028..0.033 rows=10 loops=1)
Planning Time: 0.080 ms
Execution Time: 0.051 ms
EXPLAIN ANALYZE SELECT id FROM Actor ORDER BY name LIMIT 10; -- 5
QUERY PLAN
------------------------------------------------------------------------------------------------
Limit (cost=187345.89..187345.92 rows=10 width=19) (actual time=3510.289..3510.291 rows=10 loops=1)
-> Sort (cost=187345.89..197764.62 rows=4167491 width=19) (actual time=3510.288..3510.289 rows=10 loops=1)
Sort Key: name
Sort Method: top-N heapsort Memory: 26kB
-> Seq Scan on actor (cost=0.00..97287.91 rows=4167491 width=19) (actual time=0.037..606.020 rows=4167491 loops=1)
Planning Time: 0.107 ms
Execution Time: 3510.318 ms
11
Match each of the observations to the corresponding EXPLAIN ANALYZE command.
Answer format: submit one per question, e.g., 1: A
A. just to contrast with� the next query
B. much faster, as soon as 10 � tuples are ready, will stop � execution
C. super slow b/c of sorting
D. 1 row output
E. 0 rows output, since Tom � Hanks’s ID < 4M
🤔
Exercise
5678913
Match each of the observations to the corresponding EXPLAIN ANALYZE command.
Answer format: submit one per question, e.g., 1: A
ⓘ
Click Present with Slido or install our Chrome extension to activate this poll while presenting.
Rules of Thumb, Part I
Review
Rules of Thumb, Part I
Join Orders
Join Approach I: Nested Loop Join
Join Approach II: Sort-Merge Join
Join Approach III: Hash Join
Physical implementations of other binary operators
Double table demo
Rules of Thumb, Part II
Other Knobs beyond Query Optimization
Lecture 09, Data 101 Fall 2023
Summary: Why do we care?
As data engineers, we need to understand what impacts performance.
14
For query-centric: we need to understand just “enough” to guide to good plans.
We have a few knobs under our control, but it is still valuable to understand what is expensive.
For code-centric: even more essential, since we’ll be manually doing the query optimization.
5678913
Rules of Thumb to benefit both Query-Centric and Code-Centric database systems
As data engineers, we need to understand what impacts performance.
15
Use indexes to speed up access. Best if indexes help improve queries that are frequent and slow:
For query-centric: we need to understand just “enough” to guide to good plans.
For code-centric: even more essential, since we’ll be manually doing the query optimization.
Tell the system to avoid doing extra work.
Materialize if expensive.
More next time!
5678913
Join Orders
Review
Rules of Thumb, Part I
Join Orders
Join Approach I: Nested Loop Join
Join Approach II: Sort-Merge Join
Join Approach III: Hash Join
Physical implementations of other binary operators
Double table demo
Rules of Thumb, Part II
Other Knobs beyond Query Optimization
16
Lecture 09, Data 101 Fall 2023
Today: Joins
17
5678913
Query Optimization Process: Four Steps
18
There are four steps to executing a SQL query:
What is a reasonable join order?
5678913
Lots of Bad join Orders
Say we want to do a natural join across these three relations:
1. What is the number of rows in the join result?
2. (no slido) In what order should we do this join?� Why is joining Student and Major first a BAD idea?
19
🤔
1. What is the number of rows in the join result?
2. (no slido) In what order should we do this join?� Why is joining Student and Major first a BAD idea?
A. 1.0e3 (1000)
B. 1.0e6 (1 million)
C. 1.5e6 (1.5 million)
D. 1e9 (1 billion)
E. 1.5e9
F. 1.5e12
G. Something else
5678913
What is the number of rows in the join result?
ⓘ
Click Present with Slido or install our Chrome extension to activate this poll while presenting.
Lots of Bad join orders
1. What is the number of rows in the join result?
2. (no slido) In what order should we do this join?� Why is joining Student and Major first a BAD idea?
Say we want to do a natural join across these three relations:
21
C. 1.5e6 (1.5 million)
Student ⋈ Major → 1e9!!
(StudentMajor ⋈ Major) ⋈ Student�or (StudentMajor ⋈ Student) ⋈ Major
With Joins there is a real danger of having GIANT intermediate relations! Join order therefore really matters to make sure this doesn’t blow up in our face.
StudentID | MajorID |
1 | 23 |
3 | 45 |
StudentMajor
fixed typo 9/28.
10^6 x 10^3
Junction table
5678913
Joins have an exponential search space
In what order do we join the relations R, S, T, and U? The space is exponential!
22
R
S
T
U
Left deep
R
S
T
U
R
S
T
U
Bushy
Right deep
5678913
How do we pick between join algorithms?
Recall: The query optimizer estimates the cost across various plans and picks the plan with the lowest cost
Sometimes an algorithm that figures out the best order of joins
23
5678913
Join Approach I: Nested Loop Join
Review
Rules of Thumb, Part I
Join Orders
Join Approach I: Nested Loop Join
Join Approach II: Sort-Merge Join
Join Approach III: Hash Join
Physical implementations of other binary operators
Double table demo
Rules of Thumb, Part II
Other Knobs beyond Query Optimization
24
Lecture 09, Data 101 Fall 2023
Query Optimization Process: Four Steps
25
Joins (theta or natural) are expensive.
There are four steps to executing a SQL query:
5678913
Join Approach 1: Nested Loop Joins
26
3
4
5
6
15
16
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
1
2
3
4
5
6
7
8
9
10
11
12
1
2
3
4
1
2
R
S
record in 4
record in 2
✅
✅
✅
✅
Nested loops are the simplest approach to joins.
for tuple in R� for tuple in S� if tuples match:� add_to_output
How would this work when you are accessing tuples at the granularity of pages?
for page in R� for page in S� if tuples in pages match:� add_to_output
[animation]
5678913
Join Approach 1: Nested Loop Joins
27
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
1
2
3
4
5
6
7
8
9
10
11
12
R
S
Nested loops are the simplest approach to joins.
for tuple in R� for tuple in S� if tuples match:� add_to_output
5678913
Join Approach 1: Nested Loop Joins
28
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
1
2
3
4
5
6
7
8
9
10
11
12
1
2
3
4
1
2
3
4
5
6
15
16
5
6
7
8
1
2
3
4
R
S
4
2
✅
✅
etc.
Nested loops are the simplest approach to joins.
for tuple in R� for tuple in S� if tuples match:� add_to_output
How would this work when you are accessing tuples at the granularity of pages?
for page in R� for page in S� if tuples in pages match:� add_to_output
5678913
Join Approach 1: Nested Loop Joins
Nested loops are the simplest approach to joins.
for tuple in R� for tuple in S� if tuples match:� add_to_output
How would this work when you are accessing tuples at the granularity of pages?
for page in R� for page in S� if tuples in pages match:� add_to_output
Variants:
29
1
2
3
4
1
2
3
4
5
6
7
8
9
10
11
12
R
S
1
1
2
3
4
5678913
Join Approach II: Sort-Merge Join
Review
Rules of Thumb, Part I
Join Orders
Join Approach I: Nested Loop Join
Join Approach II: Sort-Merge Join
Join Approach III: Hash Join
Physical implementations of other binary operators
Double table demo
Rules of Thumb, Part II
Other Knobs beyond Query Optimization
30
Lecture 09, Data 101 Fall 2023
[CS61B Review] Merge Sort
Sorting an array by recursively merging two smaller, pre-sorted arrays.
Can be extended to units of “pages.”
31
The sort-merge join approach effectively performs sort, plus an additional merge!
5678913
Join Approach 2: Sort-Merge Join
Phase 1: Sort
[analogous to building two sub-arrays in merge-sort]
Phase 2: Merge
Additional benefit: output is sorted!
32
1
2
3
4
5
6
7
8
1
2
3
4
5
6
7
8
5678913
Join Approach 2: Sort-Merge Join
Phase 1: Sort
[analogous to building two sub-arrays in merge-sort]
Phase 2: Merge
Additional benefit: output is sorted!
33
1
2
3
4
5
6
7
8
1
2
3
4
5
6
7
8
1
2
3
4
11
12
13
14
11
12
13
14
5
6
7
8
21
22
23
24
21
22
23
24
1
2
3
4
31
32
33
34
31
32
33
34
5
6
7
8
41
42
43
44
41
42
43
44
Sort
R, sorted within pages
S, sorted within pages
5678913
Join Approach 2: Sort-Merge Join
Phase 1: Sort
[analogous to building two sub-arrays in merge-sort]
Phase 2: Merge
Additional benefit: output is sorted!
34
1
2
3
4
5
21
31
41
22
11
32
42
23
Merge
1
2
3
4
5
6
7
8
1
2
3
4
5
6
7
8
11
12
13
14
21
22
23
24
31
32
33
34
41
42
43
44
1
2
3
4
joined R, S so far
etc.
☑︎: finished parsing
5678913
Join Approach 2: Sort-Merge Join
Phase 1: Sort
[analogous to two sub-arrays in merge-sort]
Phase 2: Merge
Additional benefit: output is sorted!
35
1
2
3
4
1
2
3
4
5
6
7
8
1
2
3
4
5
6
7
8
2
3
4
11
12
13
14
6
7
8
21
22
23
24
2
3
4
31
32
33
34
5
6
7
8
41
42
43
44
21
31
41
1
2
22
3
32
4
42
23
1
5
1
11
Variants:
Merge
joined R, S so far
etc.
☑︎: finished parsing
5678913
Join Approach III: Hash Join
Review
Rules of Thumb, Part I
Join Orders
Join Approach I: Nested Loop Join
Join Approach II: Sort-Merge Join
Join Approach III: Hash Join
Physical implementations of other binary operators
Double table demo
Rules of Thumb, Part II
Other Knobs beyond Query Optimization
36
Lecture 09, Data 101 Fall 2023
Join Approach 3: Hash Join
Phase 1:
Phase 2:
37
1
2
3
4
5
6
7
8
1
2
3
4
5
6
S
R
5678913
Join Approach 3: Hash Join
Phase 1:
Phase 2:
38
1
2
3
4
5
6
7
8
1
2
3
4
5
6
11
12
13
14
1
(Phase I: R start)
S
R
5678913
Join Approach 3: Hash Join
Phase 1:
Phase 2:
39
1
2
3
4
5
6
7
8
1
2
3
4
5
6
11
12
13
14
1
21
5678913
Join Approach 3: Hash Join
Phase 1:
Phase 2:
40
1
2
3
4
5
6
7
8
1
2
3
4
5
6
12
13
14
2
21
11
5678913
Join Approach 3: Hash Join
Phase 1:
Phase 2:
41
1
2
3
4
5
6
7
8
1
2
3
4
5
6
12
13
14
3
21
11
5678913
Join Approach 3: Hash Join
Phase 1:
Phase 2:
42
1
2
3
4
5
6
7
8
1
2
3
4
5
6
12
13
14
4
21
11
5678913
Join Approach 3: Hash Join
Phase 1:
Phase 2:
43
1
2
3
4
5
6
7
8
1
2
3
4
5
6
12
13
14
4
21
11
23
5678913
Join Approach 3: Hash Join
Phase 1:
Phase 2:
44
1
2
3
4
5
6
7
8
1
2
3
4
5
6
12
13
8
21
11
22
14
22
(Phase I: R end)
5678913
Join Approach 3: Hash Join
Phase 1:
Phase 2:
45
1
2
3
4
5
6
7
8
1
2
3
4
5
6
12
13
21
11
22
14
22
1
32
24
31
23
(Phase I: S start)
5678913
Join Approach 3: Hash Join
Phase 1:
Phase 2:
46
1
2
3
4
5
6
7
8
1
2
3
4
5
6
12
13
21
11
22
14
32
31
23
32
24
34
44
41
6
(Phase I: S end)
5678913
Join Approach 3: Hash Join
Phase 1:
Phase 2:
47
1
2
3
4
5
6
7
8
1
2
3
4
5
6
12
13
21
11
22
14
32
31
23
32
24
34
44
41
21
11
31
41
1
Join red buckets
5678913
Join Approach 3: Hash Join
Phase 1:
Phase 2:
48
1
2
3
4
5
6
7
8
1
2
3
4
5
6
12
13
21
11
22
14
32
31
23
32
24
34
44
41
21
11
31
41
1
2
3
4
5
Join red buckets
5678913
Join Approach 3: Hash Join
Phase 1:
Phase 2:
49
1
2
3
4
5
6
7
8
1
2
3
4
5
6
12
13
21
11
22
14
32
31
23
32
24
34
44
41
1
2
3
4
5
13
23
6
Join dark green buckets
5678913
Join Approach 3: Hash Join
Phase 1:
Phase 2:
50
1
2
3
4
5
6
7
8
1
2
3
4
5
6
12
13
21
11
22
14
32
31
23
32
24
34
44
41
1
2
3
4
5
13
23
6
7
8
Join dark green buckets
5678913
Join Approach 3: Hash Join
Phase 1:
Phase 2:
51
1
2
3
4
5
6
7
8
1
2
3
4
5
6
12
13
21
11
22
14
32
31
23
32
24
34
44
41
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
5678913
Join Approach 3: Hash Join
Phase 1:
Phase 2:
Variant:
If one of the relations (say S) fits entirely in memory, can create the hash table for S first, and then cycle through blocks of R, probe the hash table to find all the output tuples
52
1
2
3
4
5
6
7
8
1
2
3
4
5
6
12
13
21
11
22
14
32
31
23
32
24
34
44
41
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
5678913
[From before] How do we pick between join algorithms?
Recall: The query optimizer estimates the cost across various plans and picks the plan with the lowest cost
Sometimes an algorithm that figures out the best order of joins
53
While we won’t cover how to select joins, let’s do some initial comparisons.
5678913
Let’s do a high-level comparison
Cases to consider:
1. If there is a smaller relation so small that its hash table/the relation itself fits in memory
2. If both relations are large relative to available buffer size�
3. If both relations are large, and hash join partitions are not uniformly sized
4. If we want the join relation to be sorted
54
To join two relations, when might you use�A. nested-loop join�B. hash join�C. sort-merge join?
Put your word-cloud answer as 1-A, etc.
🤔
5678913
To join two relations, when might you use
A. nested-loop join
B. hash join
C. sort-merge join?
ⓘ
Click Present with Slido or install our Chrome extension to activate this poll while presenting.
Let’s do a high-level comparison
Cases to consider:
1. If there is a smaller relation so small that its hash table/the relation itself fits in memory
2. If both relations are large relative to available buffer size�
3. If both relations are large, and hash join partitions are not uniformly sized
4. If we want the join relation to be sorted
56
To join two relations, when might you use�A. nested-loop join�B. hash join�C. sort-merge join?
block(page)-nested loops join, hash join equally fast
likely hash join�
likely sort-merge join�
sort-merge join
Put your word-cloud answer as 1-A, etc.
5678913
Physical implementations of other binary operators
Review
Rules of Thumb, Part I
Join Orders
Join Approach I: Nested Loop Join
Join Approach II: Sort-Merge Join
Join Approach III: Hash Join
Physical implementations of other binary operators
Double table demo
Rules of Thumb, Part II
Other Knobs beyond Query Optimization
57
Lecture 09, Data 101 Fall 2023
Are other operators like join?
Join/cross-product is the only operator that “multiplies,” i.e., grows multiplicatively.
The same challenge does not hold for:
58
Potentially problematic:
5678913
Binary Operator implementations
Many binary operators work similarly to joins, particularly if they require sorting.
Suppose we consider the difference set operator, R – S.
How would you compute this with…
1. …hashing?
2. …sorting?
3. …indexing?
59
🤔
(no slido)
5678913
Binary Operator implementations
Many binary operators work similarly to joins, particularly if they require sorting.
Suppose we consider the difference set operator, R – S.
How would you compute this with…
1. …hashing?
2. …sorting?
3. …indexing?
60
5678913
Beyond binary operators: Filter, Projection, Grouping
Filters (e.g. HAVING)/Projections:
61
Aggregation/Grouping:
If an index is present, can use index to retrieve in sorted order (even cheaper!)
Q: How would we use hashing to perform an aggregation per group?
A: Answer in the slide notes!
5678913
Double table demo
Review
Rules of Thumb, Part I
Join Orders
Join Approach I: Nested Loop Join
Join Approach II: Sort-Merge Join
Join Approach III: Hash Join
Physical implementations of other binary operators
Double table demo
Rules of Thumb, Part II
Other Knobs beyond Query Optimization
62
Lecture 09, Data 101 Fall 2023
Two-table Demo; �Benefits of LIMIT, Proj.
explain analyze select * from actor, cast_info where actor.id = cast_info.person_id;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=202289.55..2141712.01 rows=36244344 width=116) (actual time=3234.602..31827.593 rows=36244344 loops=1)
Hash Cond: (cast_info.person_id = actor.id)
-> Seq Scan on cast_info (cost=0.00..615130.44 rows=36244344 width=42) (actual time=0.016..3924.669 rows=36244344 loops=1)
-> Hash (cost=97287.91..97287.91 rows=4167491 width=74) (actual time=3106.362..3106.368 rows=4167491 loops=1)
Buckets: 65536 Batches: 256 Memory Usage: 2273kB
-> Seq Scan on actor (cost=0.00..97287.91 rows=4167491 width=74) (actual time=0.014..525.299 rows=4167491 loops=1)
Planning Time: 0.870 ms
Execution Time: 33433.609 ms
// note hash join
explain analyze select actor.name,movie_id from actor, cast_info where actor.id = cast_info.person_id limit 10 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=173800.55..173800.98 rows=10 width=19) (actual time=1527.263..1527.267 rows=10 loops=1)
-> Hash Join (cost=173800.55..1730786.01 rows=36244344 width=19) (actual time=1527.262..1527.265 rows=10 loops=1)
Hash Cond: (cast_info.person_id = actor.id)
-> Seq Scan on cast_info (cost=0.00..615130.44 rows=36244344 width=8) (actual time=0.565..0.746 rows=562 loops=1)
-> Hash (cost=97287.91..97287.91 rows=4167491 width=19) (actual time=1523.167..1523.167 rows=4167491 loops=1)
Buckets: 65536 Batches: 128 Memory Usage: 2210kB
-> Seq Scan on actor (cost=0.00..97287.91 rows=4167491 width=19) (actual time=0.046..729.716 rows=4167491 loops=1)
Planning Time: 0.780 ms
Execution Time: 1601.537 ms
// notice reduced time by 20x!
explain analyze select actor.name,movie_id from actor, cast_info where actor.id = cast_info.person_id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=173800.55..1730786.01 rows=36244344 width=19) (actual time=1334.186..20502.979 rows=36244344 loops=1)
Hash Cond: (cast_info.person_id = actor.id)
-> Seq Scan on cast_info (cost=0.00..615130.44 rows=36244344 width=8) (actual time=0.009..4943.569 rows=36244344 loops=1)
-> Hash (cost=97287.91..97287.91 rows=4167491 width=19) (actual time=1328.676..1328.677 rows=4167491 loops=1)
Buckets: 65536 Batches: 128 Memory Usage: 2210kB
-> Seq Scan on actor (cost=0.00..97287.91 rows=4167491 width=19) (actual time=0.004..544.952 rows=4167491 loops=1)
Planning Time: 0.074 ms
Execution Time: 22200.379 ms
// notice not as substantial a reduction but still 2/3rd.
explain analyze select actor.name,movie_id from actor, cast_info where actor.id = cast_info.person_id and actor.id > 4000000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=110732.93..1160240.19 rows=1433453 width=19) (actual time=4126.409..10745.165 rows=338271 loops=1)
Hash Cond: (cast_info.person_id = actor.id)
-> Seq Scan on cast_info (cost=0.00..615130.44 rows=36244344 width=8) (actual time=0.036..4504.255 rows=36244344 loops=1)
-> Hash (cost=107706.64..107706.64 rows=164823 width=19) (actual time=494.738..494.740 rows=167491 loops=1)
Buckets: 65536 Batches: 4 Memory Usage: 2720kB
-> Seq Scan on actor (cost=0.00..107706.64 rows=164823 width=19) (actual time=346.150..464.320 rows=167491 loops=1)
Filter: (id > 4000000)
Rows Removed by Filter: 4000000
Planning Time: 2.351 ms
Execution Time: 10759.883 ms
// notice that projection was pushed down below the join “at source”. If we waited until join was done, would be at least as exp
Repeat: explain analyze select actor.name,movie_id from actor, cast_info where actor.id = cast_info.person_id limit 10 ;
explain analyze select actor.name,movie_id,actor.id from actor, cast_info where actor.id = cast_info.person_id limit 10 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=173800.55..173800.98 rows=10 width=23) (actual time=1396.585..1396.590 rows=10 loops=1)
-> Hash Join (cost=173800.55..1730786.01 rows=36244344 width=23) (actual time=1396.585..1396.588 rows=10 loops=1)
Hash Cond: (cast_info.person_id = actor.id)
-> Seq Scan on cast_info (cost=0.00..615130.44 rows=36244344 width=8) (actual time=0.062..0.199 rows=562 loops=1)
-> Hash (cost=97287.91..97287.91 rows=4167491 width=19) (actual time=1382.264..1382.264 rows=4167491 loops=1)
Buckets: 65536 Batches: 128 Memory Usage: 2210kB
-> Seq Scan on actor (cost=0.00..97287.91 rows=4167491 width=19) (actual time=0.023..625.156 rows=4167491 loops=1)
Planning Time: 0.728 ms
Execution Time: 1468.421 ms
// width of the seq scan is same as in the previous query, while the width after the hash join is smaller in the former – this is because actor.id is already extracted for the predicate
63
5678913
Multi-table Demo
explain analyze select * from actor, cast_info, movie where actor.id = cast_info.person_id and movie.id = cast_info.movie_id limit 10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=244945.11..244946.60 rows=10 width=318) (actual time=3557.268..3904.228 rows=10 loops=1)
-> Hash Join (cost=244945.11..3855759.20 rows=24248734 width=318) (actual time=3557.267..3904.224 rows=10 loops=1)
Hash Cond: (cast_info.movie_id = movie.id)
-> Hash Join (cost=202289.55..2141712.01 rows=36244344 width=116) (actual time=3152.976..3586.569 rows=9556 loops=1)
Hash Cond: (cast_info.person_id = actor.id)
-> Seq Scan on cast_info (cost=0.00..615130.44 rows=36244344 width=42) (actual time=0.006..100.851 rows=845147 loops=1)
-> Hash (cost=97287.91..97287.91 rows=4167491 width=74) (actual time=3117.718..3117.718 rows=4167491 loops=1)
Buckets: 65536 Batches: 256 Memory Usage: 2273kB
-> Seq Scan on actor (cost=0.00..97287.91 rows=4167491 width=74) (actual time=0.025..488.901 rows=4167491 loops=1)
-> Hash (cost=15598.25..15598.25 rows=662825 width=202) (actual time=243.959..243.959 rows=662825 loops=1)
Buckets: 32768 Batches: 64 Memory Usage: 1350kB
-> Seq Scan on movie (cost=0.00..15598.25 rows=662825 width=202) (actual time=0.023..70.715 rows=662825 loops=1)
Planning Time: 0.664 ms
Execution Time: 4171.240 ms
// note 2 hash joins where actor and cast_info are joined, followed by a join with movie
explain analyze select * from actor, cast_info, movie where actor.id = cast_info.person_id and movie.id = cast_info.movie_id and name='Hanks, Tom' ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=858753.78..876837.73 rows=11 width=318) (actual time=7447.595..7575.982 rows=130 loops=1)
Hash Cond: (movie.id = cast_info.movie_id)
-> Seq Scan on movie (cost=0.00..15598.25 rows=662825 width=202) (actual time=0.037..95.594 rows=662825 loops=1)
-> Hash (cost=858753.56..858753.56 rows=17 width=116) (actual time=7421.230..7421.232 rows=910 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 125kB
-> Hash Join (cost=107706.66..858753.56 rows=17 width=116) (actual time=1272.740..7414.511 rows=910 loops=1)
Hash Cond: (cast_info.person_id = actor.id)
-> Seq Scan on cast_info (cost=0.00..615130.44 rows=36244344 width=42) (actual time=0.026..3912.520 rows=36244344 loops=1)
-> Hash (cost=107706.64..107706.64 rows=2 width=74) (actual time=705.833..705.833 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on actor (cost=0.00..107706.64 rows=2 width=74) (actual time=69.803..705.820 rows=1 loops=1)
Filter: ((name)::text = 'Hanks, Tom'::text)
Rows Removed by Filter: 4167490
Planning Time: 3.348 ms
Execution Time: 7576.936 ms
// got rid of limit; same as before, except notice the push down of seq scan
explain analyze select * from actor, cast_info, movie where actor.id = cast_info.person_id and movie.id = cast_info.movie_id and title='Snakes on a Plane';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=768303.71..886429.80 rows=73 width=318) (actual time=6610.669..7713.420 rows=163 loops=1)
Hash Cond: (actor.id = cast_info.person_id)
-> Seq Scan on actor (cost=0.00..97287.91 rows=4167491 width=74) (actual time=0.029..665.092 rows=4167491 loops=1)
-> Hash (cost=768302.80..768302.80 rows=73 width=244) (actual time=6595.797..6595.799 rows=163 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Hash Join (cost=17255.34..768302.80 rows=73 width=244) (actual time=190.649..6594.556 rows=163 loops=1)
Hash Cond: (cast_info.movie_id = movie.id)
-> Seq Scan on cast_info (cost=0.00..615130.44 rows=36244344 width=42) (actual time=0.877..3557.223 rows=36244344 loops=1)
-> Hash (cost=17255.31..17255.31 rows=2 width=202) (actual time=156.244..156.245 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on movie (cost=0.00..17255.31 rows=2 width=202) (actual time=60.740..156.231 rows=1 loops=1)
Filter: ((title)::text = 'Snakes on a Plane'::text)
Rows Removed by Filter: 662824
Planning Time: 2.331 ms
Execution Time: 7714.844 ms
64
5678913
Multi-table: Impact of Indexes
REPEAT: explain analyze select * from actor, cast_info, movie where actor.id = cast_info.person_id and movie.id = cast_info.movie_id limit 10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=244945.11..244946.60 rows=10 width=318) (actual time=3557.268..3904.228 rows=10 loops=1)
-> Hash Join (cost=244945.11..3855759.20 rows=24248734 width=318) (actual time=3557.267..3904.224 rows=10 loops=1)
Hash Cond: (cast_info.movie_id = movie.id)
-> Hash Join (cost=202289.55..2141712.01 rows=36244344 width=116) (actual time=3152.976..3586.569 rows=9556 loops=1)
Hash Cond: (cast_info.person_id = actor.id)
-> Seq Scan on cast_info (cost=0.00..615130.44 rows=36244344 width=42) (actual time=0.006..100.851 rows=845147 loops=1)
-> Hash (cost=97287.91..97287.91 rows=4167491 width=74) (actual time=3117.718..3117.718 rows=4167491 loops=1)
Buckets: 65536 Batches: 256 Memory Usage: 2273kB
-> Seq Scan on actor (cost=0.00..97287.91 rows=4167491 width=74) (actual time=0.025..488.901 rows=4167491 loops=1)
-> Hash (cost=15598.25..15598.25 rows=662825 width=202) (actual time=243.959..243.959 rows=662825 loops=1)
Buckets: 32768 Batches: 64 Memory Usage: 1350kB
-> Seq Scan on movie (cost=0.00..15598.25 rows=662825 width=202) (actual time=0.023..70.715 rows=662825 loops=1)
Planning Time: 0.664 ms
Execution Time: 4171.240 ms
// note 2 hash joins where actor and cast_info are joined, followed by a join with movie
create index actoridindex on actor(id);
explain analyze select * from actor, cast_info, movie where actor.id = cast_info.person_id and movie.id = cast_info.movie_id limit 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=42655.99..42661.32 rows=10 width=318) (actual time=236.010..257.950 rows=10 loops=1)
-> Nested Loop (cost=42655.99..12965281.89 rows=24248734 width=318) (actual time=236.009..257.947 rows=10 loops=1)
-> Hash Join (cost=42655.56..1692069.63 rows=24248734 width=244) (actual time=232.978..244.324 rows=10 loops=1)
Hash Cond: (cast_info.movie_id = movie.id)
-> Seq Scan on cast_info (cost=0.00..615130.44 rows=36244344 width=42) (actual time=0.014..10.920 rows=2401 loops=1)
-> Hash (cost=15598.25..15598.25 rows=662825 width=202) (actual time=227.684..227.685 rows=662825 loops=1)
Buckets: 32768 Batches: 64 Memory Usage: 1350kB
-> Seq Scan on movie (cost=0.00..15598.25 rows=662825 width=202) (actual time=0.005..70.086 rows=662825 loops=1)
-> Index Scan using actoridindex on actor (cost=0.43..0.45 rows=1 width=74) (actual time=1.360..1.360 rows=1 loops=10)
Index Cond: (id = cast_info.person_id)
Planning Time: 3.881 ms
Execution Time: 306.243 ms
create index movieid_castinfoindex on cast_info(movie_id);
explain analyze select * from actor, cast_info, movie where actor.id = cast_info.person_id and movie.id = cast_info.movie_id limit 10;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.87..6.40 rows=10 width=318) (actual time=3.695..18.112 rows=10 loops=1)
-> Nested Loop (cost=0.87..13411901.94 rows=24248734 width=318) (actual time=3.694..18.108 rows=10 loops=1)
-> Nested Loop (cost=0.44..2138689.69 rows=24248734 width=244) (actual time=2.218..3.778 rows=10 loops=1)
-> Seq Scan on movie (cost=0.00..15598.25 rows=662825 width=202) (actual time=0.015..0.016 rows=2 loops=1)
-> Index Scan using movieid_castinfoindex on cast_info (cost=0.44..2.83 rows=37 width=42) (actual time=1.854..1.873 rows=5 loops=2)
Index Cond: (movie_id = movie.id)
-> Index Scan using actoridindex on actor (cost=0.43..0.45 rows=1 width=74) (actual time=1.431..1.431 rows=1 loops=10)
Index Cond: (id = cast_info.person_id)
Planning Time: 3.961 ms
Execution Time: 18.660 ms
drop index actoridindex;
drop index movieid_castinfoindex;
65
5678913
Demo [moved to discussion]
Review
Rules of Thumb, Part I
Join Orders
Join Approach I: Nested Loop Join
Join Approach II: Sort-Merge Join
Join Approach III: Hash Join
Physical implementations of other binary operators
Double table demo
Rules of Thumb, Part II
Other Knobs beyond Query Optimization
66
Lecture 09, Data 101 Fall 2023
Aggregation, Sorting
explain analyze select count(*), name from actor_movie group by name;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=859799.63..964293.99 rows=296316 width=23) (actual time=3884.352..7850.063 rows=2537837 loops=1)
Group Key: name
Planned Partitions: 8 Batches: 153 Memory Usage: 4249kB Disk Usage: 367408kB
-> Seq Scan on actor_movie (cost=0.00..193754.95 rows=10396795 width=15) (actual time=1.564..1157.777 rows=10396795 loops=1)
Planning Time: 1.140 ms
Execution Time: 8086.724 ms
// hash based aggregation
explain analyze select count(*), id from actor_movie group by id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=778574.67..862464.04 rows=266441 width=12) (actual time=3648.500..5612.315 rows=634515 loops=1)
Group Key: id
Planned Partitions: 8 Batches: 41 Memory Usage: 4177kB Disk Usage: 220056kB
-> Seq Scan on actor_movie (cost=0.00..193754.95 rows=10396795 width=4) (actual time=1.496..1168.085 rows=10396795 loops=1)
Planning Time: 2.128 ms
Execution Time: 5678.803 ms
// agg on diff attrib
create index id_actormovie_index on actor_movie(id);
explain analyze select count(*), id from actor_movie group by id;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=0.43..252132.74 rows=266441 width=12) (actual time=3.309..5025.132 rows=634515 loops=1)
Group Key: id
-> Index Only Scan using id_actormovie_index on actor_movie (cost=0.43..197484.36 rows=10396795 width=4) (actual time=3.297..4261.535 rows=10396795 loops=1)
Heap Fetches: 0
Planning Time: 2.449 ms
Execution Time: 5054.004 ms
//index is used t
explain analyze select count(*), id from actor_movie group by id order by count(*);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=280697.18..281363.29 rows=266441 width=12) (actual time=2040.411..2105.262 rows=634515 loops=1)
Sort Key: (count(*))
Sort Method: external merge Disk: 13712kB
-> GroupAggregate (cost=0.43..252132.74 rows=266441 width=12) (actual time=0.068..1904.773 rows=634515 loops=1)
Group Key: id
-> Index Only Scan using id_actormovie_index on actor_movie (cost=0.43..197484.36 rows=10396795 width=4) (actual time=0.054..1165.123 rows=10396795 loops=1)
Heap Fetches: 0
Planning Time: 4.937 ms
Execution Time: 2136.633 ms
REPEAT: explain analyze select actor.id, movie_id from actor, cast_info where actor.id = cast_info.person_id order by actor.id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=7751802.38..7842413.24 rows=36244344 width=8) (actual time=29410.031..33954.195 rows=36244344 loops=1)
Sort Key: actor.id
Sort Method: external merge Disk: 638416kB
-> Hash Join (cost=165661.55..1714508.01 rows=36244344 width=8) (actual time=1645.071..17424.844 rows=36244344 loops=1)
Hash Cond: (cast_info.person_id = actor.id)
-> Seq Scan on cast_info (cost=0.00..615130.44 rows=36244344 width=8) (actual time=1.392..4222.344 rows=36244344 loops=1)
-> Hash (cost=97287.91..97287.91 rows=4167491 width=4) (actual time=1626.535..1626.546 rows=4167491 loops=1)
Buckets: 131072 Batches: 64 Memory Usage: 3325kB
-> Seq Scan on actor (cost=0.00..97287.91 rows=4167491 width=4) (actual time=0.883..1042.402 rows=4167491 loops=1)
Planning Time: 7.430 ms
Execution Time: 35602.674 ms
explain analyze select actor_movie.id, movie_id from actor_movie, cast_info where actor_movie.id = cast_info.person_id order by cast_info.person_id;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=9027208.11..28092804.89 rows=1414290648 width=12) (actual time=16986.805..43919.940 rows=94846436 loops=1)
Merge Cond: (actor_movie.id = cast_info.person_id)
-> Index Only Scan using id_actormovie_index on actor_movie (cost=0.43..197484.36 rows=10396795 width=4) (actual time=0.503..3884.640 rows=10396795 loops=1)
Heap Fetches: 0
-> Materialize (cost=6652424.82..6833646.54 rows=36244344 width=8) (actual time=16373.316..26283.404 rows=113645937 loops=1)
-> Sort (cost=6652424.82..6743035.68 rows=36244344 width=8) (actual time=16373.308..18854.510 rows=24573698 loops=1)
Sort Key: cast_info.person_id
Sort Method: external merge Disk: 638416kB
-> Seq Scan on cast_info (cost=0.00..615130.44 rows=36244344 width=8) (actual time=0.033..4319.827 rows=36244344 loops=1)
Planning Time: 8.366 ms
Execution Time: 48747.209 ms
67
5678913
Two-table; �Plan Selection
explain analyze select * from actor, cast_info where actor.id = cast_info.person_id;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=202289.55..2141712.01 rows=36244344 width=116) (actual time=3234.602..31827.593 rows=36244344 loops=1)
Hash Cond: (cast_info.person_id = actor.id)
-> Seq Scan on cast_info (cost=0.00..615130.44 rows=36244344 width=42) (actual time=0.016..3924.669 rows=36244344 loops=1)
-> Hash (cost=97287.91..97287.91 rows=4167491 width=74) (actual time=3106.362..3106.368 rows=4167491 loops=1)
Buckets: 65536 Batches: 256 Memory Usage: 2273kB
-> Seq Scan on actor (cost=0.00..97287.91 rows=4167491 width=74) (actual time=0.014..525.299 rows=4167491 loops=1)
Planning Time: 0.870 ms
Execution Time: 33433.609 ms
// note hash join
select name from pg_settings where name like 'enable%’;
set enable_hashjoin=false;
explain analyze select * from actor, cast_info where actor.id = cast_info.person_id;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=9436529.03..10091110.90 rows=36244344 width=116) (actual time=20982.786..37187.934 rows=36244344 loops=1)
Merge Cond: (actor.id = cast_info.person_id)
-> Sort (cost=925875.12..936293.85 rows=4167491 width=74) (actual time=2269.455..2972.439 rows=4061927 loops=1)
Sort Key: actor.id
Sort Method: external merge Disk: 354800kB
-> Seq Scan on actor (cost=0.00..97287.91 rows=4167491 width=74) (actual time=0.040..488.974 rows=4167491 loops=1)
-> Materialize (cost=8510651.82..8691873.54 rows=36244344 width=42) (actual time=18713.034..27117.141 rows=36244344 loops=1)
-> Sort (cost=8510651.82..8601262.68 rows=36244344 width=42) (actual time=18713.030..22730.095 rows=36244344 loops=1)
Sort Key: cast_info.person_id
Sort Method: external merge Disk: 1318144kB
-> Seq Scan on cast_info (cost=0.00..615130.44 rows=36244344 width=42) (actual time=1.287..3622.967 rows=36244344 loops=1)
Planning Time: 2.769 ms
Execution Time: 38870.629 msexplain analyze select * from actor, cast_info where actor.id = cast_info.person_id limit 10 ;
set enable_mergejoin=false;
explain select * from actor, cast_info where actor.id = cast_info.person_id ;
QUERY PLAN
----------------------------------------------------------------------------------
Nested Loop (cost=0.00..3593291392356.77 rows=36244344 width=116)
Join Filter: (actor.id = cast_info.person_id)
-> Seq Scan on actor (cost=0.00..97287.91 rows=4167491 width=74)
-> Materialize (cost=0.00..1114906.16 rows=36244344 width=42)
-> Seq Scan on cast_info (cost=0.00..615130.44 rows=36244344 width=42)
set enable_mergejoin = true;
set enable_hashjoin = true;
68
5678913
Recap
How to read an EXPLAIN [+ANALYZE]
Speed up queries with:
Filters, projections are done along with other operations
Optimizers’ estimates:
Finally, various shapes for Join trees
69
Demo
Rules of Thumb, Part II
Review
Rules of Thumb, Part I
Join Orders
Join Approach I: Nested Loop Join
Join Approach II: Sort-Merge Join
Join Approach III: Hash Join
Physical implementations of other binary operators
Double table demo
Rules of Thumb, Part II
Other Knobs beyond Query Optimization
70
For next time
Lecture 09, Data 101 Fall 2023
[From Last Time] Summary: Why do we care?
As data engineers, we need to understand what impacts performance.
71
For query-centric: we need to understand just “enough” to guide to good plans.
We have a few knobs under our control, but it is still valuable to understand what is expensive.
For code-centric: even more essential, since we’ll be manually doing the query optimization.
5678913
Summary: Why do we care? Part 2
As data engineers, we need to understand what impacts performance.
72
For query-centric: we need to understand just “enough” to guide to good plans.
We have a few knobs under our control, but it is still valuable to understand what is expensive.
For code-centric: even more essential, since we’ll be manually doing the query optimization.
Examples of things we must do now (whereas query-centric systems do automatically):
5678913
[From Last Time] Rules of Thumb
73
Tell the system to avoid doing extra work.
Materialize if expensive.
Use indexes to speed up access. Best if indexes help improve queries that are frequent and slow:
5678913
Rules of Thumb: A few more
Some additional items:
1. Pipelining
2. Periodic reorganization
74
Tell the system to avoid doing extra work.
Materialize if expensive.
Use indexes to speed up access. Best if indexes help improve queries that are frequent and slow:
3. Periodic statistics recomputation
4. Additional database system knobs
5678913
(1/4) Additional Knobs: Pipelining
So far we have described operators in a sequential manner:
75
Better approach: pipelining
Note: Operators themselves can also be parallelized via a process called partitioned parallelism.
Scan
Filter
Agg.
5678913
Other Knobs beyond Query Optimization
Review
Rules of Thumb, Part I
Join Orders
Join Approach I: Nested Loop Join
Join Approach II: Sort-Merge Join
Join Approach III: Hash Join
Physical implementations of other binary operators
Double table demo
Rules of Thumb, Part II
Other Knobs beyond Query Optimization
76
For next time
Lecture 09, Data 101 Fall 2023
(2/4) Additional Knob: Periodic Reorganization
Many data systems keep old versions of tuples around! Several reasons:
However, old versions of tuples cause bloat.Impacts:
77
5678913
(2/4) Additional Knob: Periodic Reorganization
Many data systems keep old versions of tuples around! Several reasons:
However, old versions of tuples cause bloat.Impacts:
Solution 1: VACUUM command to re-pack.
Solution 2: For extreme UPDATEs, avoid VACUUM FULL.
78
5678913
(3/4) Additional Knob: Statistics Computation
Recall: query optimization relies on statistics!
Statistics can become stale after many updates!
Incorrect statistics can lead to bad query plans. Therefore VACUUM recomputes statistics, too.
Can recompute statistics as part of VACUUM
These can be set to run periodically via the autovacuum program. [Documentation 25.1]
79
5678913
(4/4) Additional Knob: Database System Knobs
There are many settings that control system behavior!
In postgres:
imdb=# select count(*) from pg_settings;
count
-------
329
Some settings of particular importance:
Lots more, see Postgres manual!
80
5678913