Index

A

• Aborting transactions 72, 76, 78, 219, 236

• Access method

  • index 313, 365

  • properties 322

  • table 294

• Aggregate 298–299

• Aggregation 298, 303

  • hashing 384, 402

  • sorting 402

• Alignment 65

• Analysis 109, 271, 341, 412

• Anomaly

  • dirty read 40, 42, 45

  • lost update 41, 50–51

  • non-repeatable read 42, 46, 52

  • phantom read 42, 52, 235

  • read skew 48, 50, 53

  • read-only transaction 56, 59, 235

  • write skew 55, 58, 235

• Append 385

• Array 471, 508, 516

• “Asterisk,” the reasons not to use it 32, 369, 399

• Atomicity 40, 78

• autoprewarm leader 161–163

• autoprewarm worker 163

• autosummarize 523

• autovacuum 110

• autovacuum launcher 110–111

• autovacuum worker 110

• autovacuum_analyze_scale_factor 113

• autovacuum_analyze_threshold 113

• autovacuum_enabled 103, 111

• autovacuum_freeze_max_age 127, 131–132

• autovacuum_freeze_min_age 132

• autovacuum_freeze_table_age 132

• autovacuum_max_workers 110, 118, 122

• autovacuum_multixact_freeze_max_age 215

• autovacuum_naptime 110–111

• autovacuum_vacuum_cost_delay 118, 122, 132

• autovacuum_vacuum_cost_limit 118, 122

• autovacuum_vacuum_insert_scale_factor 112

• autovacuum_vacuum_insert_threshold 112

• autovacuum_vacuum_scale_factor 111–112

• autovacuum_vacuum_threshold 111–112

• autovacuum_work_mem 111

• autovacuum_freeze_max_age 131

B

• Backend 34

• Background worker 108, 110, 305

• Background writing 176

  • setup 179

• Batch processing 142, 224

• bgwriter 176, 179–181, 194

• bgwriter_delay 179

• bgwriter_lru_maxpages 179, 181

• bgwriter_lru_multiplier 179

• Binding 267

• Bison 254

• Bitmap 341

  • NULL values 64

• Bitmap Heap Scan 289, 341, 344, 346

• Bitmap Index Scan 289, 341, 344, 346, 348

• BitmapAnd 343

• Bloating 89, 101, 141, 297, 417, 425, 430, 445

• Block see page

• bloom 537

• Bloom filter 466, 536

• Box plot 527

• BRIN 517

  • efficiency factor 527

  • operator class 521, 523, 532, 534–535, 537

  • pages 519

  • properties 528

• B-tree 421, 459, 492, 494, 511

  • operator class 432, 496, 524

  • pages 426

  • properties 441

• btree_gin 511

• btree_gist 459, 471

• Buffer cache 34, 147, 165, 170, 241, 296, 314, 335

  • configuration 158

  • eviction 154

  • local 163, 311

• Buffer pin 148, 151, 242

• Buffer ring 156, 296

C

• Cardinality 262, 271, 335

  • join 356

• Cartesian product 350, 352

• Checkpoint 170, 186

  • monitoring 179

  • setup 177

• checkpoint_completion_target 177–178

• checkpointer 170–171, 176, 178–181, 186

• checkpoint_timeout 177–178, 180

• checkpoint_warning 179

• client_encoding 507

• CLOG 70, 132, 165, 167, 170

• Cluster 20

• Cmin and cmax 86

• Collation 317, 433, 487

• Combo-identifier 86

• Commit 70, 167, 219

  • asynchronous 183

  • synchronous 182

• commit_delay 183

• commit_siblings 183

• Consistency 39–40

• Correlated predicates 263, 289

• Correlation 284, 331, 340, 517, 525, 534

• Cost 258, 262, 264

• cpu_index_tuple_cost 333

• cpu_operator_cost 299, 333, 370, 389, 394, 402

• cpu_tuple_cost 298–299, 333, 360, 370–371, 389, 394

• CTE Scan 309–310

• CTID 64, 96

• cube 470

• Cursor 86, 151, 262, 270, 308

• cursor_tuple_fraction 262, 270

D

• Database 20

• data_checksums 188

• Deadlocks 202, 225, 233

• deadlock_timeout 226, 233, 243

• debug_print_parse 255

• debug_print_plan 257

• debug_print_rewritten 256

• deduplicate_items 430

• Deduplication 429, 492

• default_statistics_target 272, 279, 283, 292

• default_table_access_method 294

• default_text_search_config 464

• default_transaction_isolation 60

• Demo database 251, 446, 518

• Dirty read 42, 45

• Durability 40

E

• effective_cache_size 335–336

• effective_io_concurrency 341

• enable_bitmapscan 334

• enable_hashjoin 390–391

• enable_memoize 361

• enable_mergejoin 362

• enable_parallel_hash 378, 380

• enable_seqscan 229, 334, 509, 535

• Equi-join 350, 382, 388, 411

• Eviction 154, 167, 176

• Execution 264, 267

F

• false_positive_rate 537

• fastupdate 233, 503

• fdatasync 186

• fillfactor 92–93, 98–99, 125, 127, 135, 236, 413, 446, 474, 531

• Finalize Aggregate 304

• Finalize GroupAggregate 404

• Flex 254

• force_parallel_mode 309

• Foreign keys 211, 213, 356

• Fork 24

  • free space map 26, 92, 102

  • initialization 26

  • main 25, 64, 525

  • visibility map 27, 92, 126, 128, 139, 337

• Freezing 124, 139, 152, 215

  • manual 132

• from_collapse_limit 259–260

• fsync 186–187

• Full page image 174

• full_page_writes 189, 191

• Full-text search 462

  • indexing 463, 493

  • partial 498

  • phrase 506

  • ranking 506

• fuzzystrmatch 490

G

• Gather 300–304, 309, 401

• Gather Merge 401–402

• geqo 261

• geqo_threshold 261

• Getting the result 270

• gevel 448, 478

• GIN 492

  • deferred update 233

  • fast update 503

  • operator class 493, 496, 508, 511

  • pages 494

  • properties 505

• gin_fuzzy_search_limit 504

• gin_pending_list_limit 503

• GiST 444, 533

  • operator class 445, 448, 535

  • pages 447

  • properties 459, 469

• GroupAggregate 403

• Grouping 384, 403

H

• Hash 367, 370, 374, 411

  • operator class 417

  • page 412

  • properties 418

• Hash Join 367, 369–370, 374

• Hash table 149, 241–242, 360, 367, 411

• HashAggregate 384–385

• hash_mem_multiplier 360, 368, 379, 385

• Header

  • page 62, 104

  • row version 64

  • tuple 210

• High key 426, 428, 494

• Hint bits see information bits

• Histogram 279

• Horizon 87–88, 92, 104, 141, 338

• HOT updates 96, 430

• hstore 471, 516

I

• idle_in_transaction_session_timeout 141

• ignore_checksum_failure 188

• Incremental Sort 400

• Index 313, 318

  • covering 325, 337, 339, 442, 482

  • include 460

  • integrity constraint 323, 325, 419, 442, 457, 460, 482

  • multicolumn 324, 437, 441–442, 460, 505, 529

  • on expression 287, 319, 535

  • ordering 323, 327, 421, 433, 437, 441–442

  • partial 329

  • pruning 101, 425, 430

  • statistics 287

  • unique 211, 323, 325, 423, 430, 442

  • vacuuming 416, 503

  • versioning 74

• Index Only Scan 337

• Index Scan 330–331, 334, 355, 358

• Indexing engine 313, 322

• Information bits 64, 68, 71, 81, 189, 210

• InitPlan 276, 311

• Instance 20

• intarray 471, 516

• Integrity constraints 39

• Isolation 40

  • snapshot 44, 58, 80, 210

J

• Join

  • anti- and semi- 350, 363

  • cost estimation 353, 358, 360, 370, 376, 389, 393, 395, 398, 400–401

  • different methods 405

  • hashing 367, 372

  • inner 350

  • merging 387, 437

  • nested loop 351

  • order 257, 259, 368, 389

  • outer 350, 362, 388

  • parallel hash 378, 380

  • parameterized 355

• join_collapse_limit 259–260

• JSON 511, 516

• jsquery 516

K

• k-D tree 483

L

• Locks 43, 199, 314

  • advisory 234

  • escalation 210, 238

  • heavyweight 201, 210

  • lightweight 240

  • memory 148

  • no waits 142, 224

  • non-relation 231

  • page 233

  • predicate 235

  • queue 206, 215, 221

  • relation 109, 135, 140, 194, 204

  • relation extension 232

  • row 142, 210

  • spinlocks 240

  • tranche 242

  • transaction ID 202

  • tuple 215

• lock_timeout 224–225

• log_autovacuum_min_duration 122

• log_checkpoints 179

• logical 191, 196

• log_lock_waits 243

• log_temp_files 374, 398

• Lost update 41, 50–51

• ltree 471

M

• maintenance_io_concurrency 341

• maintenance_work_mem 107, 119, 121, 503

• Map

  • bitmap 412, 517, 521, 529

  • free space 26, 92, 102

  • freeze 27, 126, 129, 139

  • visibility 27, 92, 126, 128, 139, 337

• Materialization 309, 352, 359

• Materialize 352, 354–355, 359–360, 362

• max_connections 201, 238

• max_locks_per_transaction 201

• max_parallel_processes 161

• max_parallel_workers 305

• max_parallel_workers_per_gather 305–306

• max_pred_locks_per_page 238

• max_pred_locks_per_relation 239

• max_pred_locks_per_transaction 238–239

• max_wal_senders 192

• max_wal_size 177–178, 180

• max_worker_processes 110, 305

• Memoize 359–361, 406

• Merge Join 387

• Merging 387, 396, 401

• minimal 186, 191–192, 194–195

• min_parallel_index_scan_size 108

• min_parallel_table_scan_size 305

• min_wal_size 178

• MixedAggregate 404

• Multitransactions 213

  • wraparound 215

• Multiversion concurrency control 44, 63, 101, 430

N

• n_distinct_per_range 537

• Nearest neighbor search 327, 452, 461, 483

• Nested Loop 258, 351–352, 355, 360

• Nested Loop Anti Join 363

• Nested Loop Left Join 351, 362

• Nested Loop Semi Join 364

• Non-repeatable read 42, 46, 52

• Non-uniform distribution 277, 374, 413

• NULL 64, 274, 328, 441, 443, 461, 483, 530

O

• OID 21

• old_snapshot_threshold 141

• Operator class 315, 365, 493

  • parameters 468, 533, 537

  • support functions 320

• Operator family 320

• Optimization see planning

P

• Page 28, 412

  • dirty 148

  • fragmentation 64, 94

  • full image 174

  • header 134, 139

  • prefetching 341

  • split 101, 423, 425, 457, 481, 502

• pageinspect 62, 65, 68, 74, 125, 167, 211, 412, 447, 478, 494, 520

• pages_per_range 517

• Parallel Bitmap Heap Scan 348

• Parallel execution 300, 305, 347, 366, 377, 390, 401, 404

  • limitations 308

• Parallel Hash 379

• Parallel Hash Join 379

• Parallel Index Only Scan 378

• Parallel Seq Scan 301–302

• parallel_leader_participation 300, 302

• parallel_setup_cost 303, 401

• parallel_tuple_cost 303, 402

• parallel_workers 305

• Parsing 254

• Partial Aggregate 303

• Partial GroupAggregate 404

• pgbench 184, 189, 245

• pg_buffercache 148, 159

• pg_checksums 187

• pg_controldata 173

• PGDATA 20

• pg_dump 90

• pg_prewarm 161

• pg_prewarm.autoprewarm 161

• pg_prewarm.autoprewarm_interval 161

• pg_rewind 165

• pgrowlocks 214, 229

• pgstattuple 136

• pg_test_fsync 186

• pg_trgm 471, 490, 507

• pg_visibility 104, 126

• pg_wait_sampling 245

• pg_wait_sampling.profile_period 246

• pg_waldump 169, 176, 193

• Phantom read 42, 52, 235

• Plan 257

  • generic and custom 268

• plan_cache_mode 269

• Planning 257, 267

• Pointers to tuples 63

• Portal 264

• postgres 33

• postmaster 33–34, 110, 173, 175, 300

• Preparing a statement 266

• ProcArray 70, 82

• Process 33

• Protocol 35

  • extended query 266

  • simple query 252

• Pruning 92, 98, 101, 425, 430

• psql 15, 18, 21, 79, 244, 251

Q

• Quadtree 473

R

• random_page_cost 297, 334, 345

• RD-tree 464

• Read Committed 42–46, 49–50, 52–53, 61, 80, 87–88, 91, 104, 219

• Read skew 48, 50, 53

• Read Uncommitted 42–45

• Read-only transaction anomaly 56, 59, 235

• Recheck 314, 330, 342, 463, 469, 483, 510

• Recovery 173

• Relation 24

• Repeatable Read 43–45, 52–56, 58, 60–61, 80, 88, 91, 133, 219, 236

• replica 191–192, 194, 196

• Rewriting see transformation

• Row version see tuple

• RTE 254

• R-Tree 445

• Rule system 256

• RUM 506

S

• Savepoint 76

• Scan

  • bitmap 326, 340, 420, 442, 460, 482, 503, 530

  • cost estimation 296, 301, 331, 338, 343, 534

  • index 237, 326, 330, 442, 460, 482

  • index-only 273, 328, 337, 420, 461, 466, 483, 490

  • method comparison 348

  • parallel index 347

  • parallel sequential 301

  • sequential 236, 296

  • skip 439

• Schema 22

• search_path 22

• seg 471

• Segment 24, 168

• Selectivity 262, 296

  • join 356

• Seq Scan 258, 296, 298–299, 310

• seq_page_cost 297, 334, 345, 377

• Serializable 43–44, 58, 60–61, 80, 88, 91, 219, 235–236, 239, 308

• Server 20

• shared_buffers 158

• shared_preload_libraries 161, 245

• Signature 466, 537

• slowfs 246

• Snapshot 80, 83, 194

  • export 90

  • system catalog 89

• Sort 391–394, 402, 404

• Sorting 327, 387, 392, 421, 437, 441

  • external 396

  • heapsort 394

  • incremental 399

  • parallel 401

  • quicksort 393

• Special space 63

• SP-GiST 472

  • operator class 472, 475, 486

  • pages 477

  • properties 482, 490

• Split

  • bucket 411, 414

  • page 423, 425, 457, 481, 502

• startup 173–175

• Starvation 216, 221

• statement_timeout 225

• Statistics 109, 262

  • basic 271, 337

  • correlation 284, 332, 525, 534

  • distinct values 276, 290, 525

  • expression 285, 293

  • extended 286

  • field width 284

  • histogram 279, 389

  • most common values 277, 291, 357, 374

  • multivariate 288

  • non-scalar data types 283

  • NULL fraction 275

• SubPlan 310–311

• Subtransaction 76, 170

• Support functions 320

• Synchronization 182, 186

• synchronous_commit 182–184

• System catalog 21, 193, 255

T

• Tablespace 22

• temp_buffers 163

• temp_file_limit 163, 372

• Tid Scan 330

• Timeline 169

• TOAST 22, 28, 74, 157

• track_commit_timestamp 82

• track_counts 110

• track_io_timing 154

• Transaction 40, 67, 80

  • abort 72, 76, 78, 219, 236

  • age 123

  • commit 70, 167, 182–183, 219

  • status 82, 167

  • subtransaction 76, 170

  • virtual 75, 203

• Transaction ID

  • lock 202

  • wraparound 123, 131