11. Indexes ..... 371 11.1. Introduction ..... 371 11.2. Index Types ..... 372 11.3. Multicolumn Indexes ..... 374 11.4. Indexes and ORDER BY ..... 375 11.5. Combining Multiple Indexes .... 376 11.6. Unique Indexes ..... 377 11.7. Indexes on Expressions ..... 378 11.8. Partial Indexes ..... 378 11.9. Operator Classes and Operator Families ..... 381 11.10. Indexes and Collations . 12.1.3. Configurations ..... 388 12.2. Tables and Indexes ..... 388 12.2.1. Searching a Table ..... 388 12.2.2. Creating Indexes ..... 389 12.3. Controlling Text Search ..... 390

11. Indexes ..... 444 11.1. Introduction ..... 444 11.2. Index Types ..... 445 11.3. Multicolumn Indexes ..... 447 11.4. Indexes and ORDER BY ..... 448 11.5. Combining Multiple Indexes .... . 449 11.6. Unique Indexes ..... 450 11.7. Indexes on Expressions ..... 450 11.8. Partial Indexes ..... 451 11.9. Index-Only Scans and Covering Indexes ..... 454 11.10. Operator Classes and Operator .... 457 11.11. Indexes and Collations ..... 458 11.12. Examining Index Usage ..... 459 12. Full Text Search ..... 461 12.1. Introduction ..... 461 12.2. Tables and Indexes ..... 465 12.3.

316 11. Indexes .....318 11.1. Introduction .....318 11.2. Index Types .....319 11.3. Multicolumn Indexes .....321 11.4. Indexes and ORDER BY .....322 11.5. Combining Multiple Indexes .....323 323 11.6. Unique Indexes .....324 11.7. Indexes on Expressions .....324 11.8. Partial Indexes .....325 11.9. Operator Classes and Operator Families .....327 11.10. Indexes and Collations .....329 ...333 12.1.3. Configurations .....334 12.2. Tables and Indexes .....334 12.2.1. Searching a Table .....334 12.2.2. Creating Indexes .....335 12.3. Controlling Text Search .....336 12.3.1.

10.2.7 Troubleshoot Lock Conflicts ··· 1380 10.2.8 Troubleshoot Inconsistency Between Data and Indexes ··· 1393 10.3 Diagnostic Methods ··· 1396 10.3.1 SQL Diagnostics ··· 1396 10.3.2 Statement Summary ··· 3958 14.12.3 Data Types ··· 4392 14.12.4 Functions and Operators ··· 4418 14.12.5 Clustered Indexes ··· 4617 14.12.6 Vector Search Index ··· 4623 14.12.7 Constraints ··· 4631 14.12.8 Generated Columns href="https://docs.pingcap.com/tidb/v8.4/partitioned-table#global- → indexes">Global indexes for partitioned tables (GA) Global indexes can effectively improve the efficiency of retrieving non- → partitioned

1. Indexes ..... 298 11.1. Introduction ..... 298 11.2. Index Types ..... 299 11.3. Multicolumn Indexes ..... 301 11.4. Indexes and ORDER BY ..... 302 11.5. Combining Multiple Indexes .... 302 11.6. Unique Indexes ..... 303 11.7. Indexes on Expressions ..... 304 11.8. Partial Indexes ..... 304 11.9. Operator Classes and Operator Families ..... 307 11.10. Indexes and Collations .. 311 12.1.3. Configurations ..... 312 12.2. Tables and Indexes ..... 313 12.2.1. Searching a Table ..... 313 12.2.2. Creating Indexes ..... 314 12.3. Controlling Text Search ..... 315 12.3

326 11. Indexes .....329 11.1. Introduction .....329 11.2. Index Types .....330 11.3. Multicolumn Indexes .....332 11.4. Indexes and ORDER BY .....333 11.5. Combining Multiple Indexes .....334 334 11.6. Unique Indexes .....335 11.7. Indexes on Expressions .....335 11.8. Partial Indexes .....336 11.9. Operator Classes and Operator Families .....338 11.10. Indexes and Collations .....340 ...344 12.1.3. Configurations .....345 12.2. Tables and Indexes .....345 12.2.1. Searching a Table .....345 12.2.2. Creating Indexes .....346 12.3. Controlling Text Search .....347 12.3.1.

11. Indexes ..... 302 11.1. Introduction ..... 302 11.2. Index Types ..... 303 11.3. Multicolumn Indexes ..... 305 11.4. Indexes and ORDER BY ..... 306 11.5. Combining Multiple Indexes .... 11.6. Unique Indexes ..... 307 11.7. Indexes on Expressions ..... 308 11.8. Partial Indexes ..... 308 11.9. Operator Classes and Operator Families ..... 311 11.10. Indexes and Collations 12.1.3. Configurations ..... 318 12.2. Tables and Indexes ..... 318 12.2.1. Searching a Table ..... 318 12.2.2. Creating Indexes ..... 319 12.3. Controlling Text Search ..... 320

67 4.6 Building Indexes 67 4.6.1 GiST Indexes 67 4.6.2 BRIN Indexes 68 4.6.3 SP-GiST Indexes 69 4.6.4 Using Indexes 70 4.7 Complex Queries 70 4.7.1 Taking Advantage of Indexes 70 4.7.2 Examples 12.4.2 standardize_address 699 **13 PostGIS Extras** 702 13.1 Tiger Geocoder 702 13.1.1 Drop_Indexes_Generate_Script 702 13.1.2 Drop_Nation_Tables_Generate_Script 703 13.1.3 Drop_State_Tables_Generate_Script 13.1.8 Install_Missing_Indexes 710 13.1.9 Loader_Generate_Census_Script 710 13.1.10 Loader_Generate_Script 712 13.1.11 Loader_Generate_Nation_Script 714 13.1.12 Missing_Indexes_Generate_Script 715 13

Extract Data 49 4.8.2 Using the Shapefile Dumper 50 4.9 Spatial Indexes 50 4.9.1 GiST Indexes 51 4.9.2 BRIN Indexes 51 4.9.3 SP-GiST Indexes 53 4.9.4 Tuning Index Usage 53 5 Spatial Queries 55 5.1 Determining 5.1.2 Named Spatial Relationships 57 5.1.3 General Spatial Relationships 58 5.2 Using Spatial Indexes 60 5.3 Examples of Spatial SQL 60 6 Performance Tips 64 6.1 Small tables of large geometries 64 ST_SetGeoReference . . . . . 12. 12.4 ST_Intersection . . . . . 12. 16.5 @ . . . . . 14.2.1 Drop_Indexes_Generate_Script 720 14.2.2 Drop_Nation_Tables_Generate_Script 721 14.2.3 Drop_State_Tables_Generate_Script

288 11. Indexes .....290 11.1. Introduction .....290 11.2. Index Types .....291 11.3. Multicolumn Indexes .....292 11.4. Indexes and ORDER BY .....294 11.5. Combining Multiple Indexes .....294 294 11.6. Unique Indexes .....295 11.7. Indexes on Expressions .....296 11.8. Partial Indexes .....296 11.9. Operator Classes and Operator Families .....299 11.10. Examining Index Usage .....300 12.1.3. Configurations ..... 305 12.2. Tables and Indexes ..... 305 12.2.1. Searching a Table ..... 305 12.2.2. Creating Indexes ..... 306 12.3. Controlling Text Search ..... 307