Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

08

Chapter 8

Performance & Indexes

Making queries fast. Indexes, EXPLAIN, statistics, partial and expression indexes, and reading a plan without flinching.

In this chapter

76

queries, easy → advanced

  1. 001Create basic B-tree indexRead
  2. 002Partial index: index only active employeesRead
  3. 003GIN index for full-text and array searchRead
  4. 004Index-only scan vs table scanRead
  5. 005Query optimization: avoid functions on indexed columnsRead
  6. 006BRIN index for time-series tablesRead
  7. 007Vacuum and table statisticsRead
  8. 008Connection pooling and pg_stat_activityRead
  9. 009Identify slow queries with pg_stat_statementsRead
  10. 010Table statistics and size analysisRead
  11. 011Index bloat detection and maintenanceRead
  12. 012Check table size and row countRead
  13. 013Find missing indexes using pg_stat_user_tablesRead
  14. 014Hash index for equality-only queriesRead
  15. 015Parallel query configurationRead
  16. 016Query plan hints with pg_hint_plan extensionRead
  17. 017Partial index for soft deletes patternRead
  18. 018Tune autovacuum for high-write tablesRead
  19. 019Covering index: avoid heap fetch completelyRead
  20. 020Create a basic B-Tree indexRead
  21. 021Create a UNIQUE indexRead
  22. 022Create a multi-column (composite) indexRead
  23. 023Create a Partial IndexRead
  24. 024Create an Expression (Function-based) IndexRead
  25. 025Create Index ConcurrentlyRead
  26. 026Drop an IndexRead
  27. 027HikariCP Tuning for PostgreSQL: Config #1Read
  28. 028HikariCP Tuning for PostgreSQL: Config #2Read
  29. 029HikariCP Tuning for PostgreSQL: Config #3Read
  30. 030HikariCP Tuning for PostgreSQL: Config #4Read
  31. 031HikariCP Tuning for PostgreSQL: Config #5Read
  32. 032HikariCP Tuning for PostgreSQL: Config #6Read
  33. 033HikariCP Tuning for PostgreSQL: Config #7Read
  34. 034HikariCP Tuning for PostgreSQL: Config #8Read
  35. 035HikariCP Tuning for PostgreSQL: Config #9Read
  36. 036HikariCP Tuning for PostgreSQL: Config #10Read
  37. 037HikariCP Tuning for PostgreSQL: Config #11Read
  38. 038HikariCP Tuning for PostgreSQL: Config #12Read
  39. 039HikariCP Tuning for PostgreSQL: Config #13Read
  40. 040HikariCP Tuning for PostgreSQL: Config #14Read
  41. 041HikariCP Tuning for PostgreSQL: Config #15Read
  42. 042HikariCP Tuning for PostgreSQL: Config #16Read
  43. 043HikariCP Tuning for PostgreSQL: Config #17Read
  44. 044HikariCP Tuning for PostgreSQL: Config #18Read
  45. 045HikariCP Tuning for PostgreSQL: Config #19Read
  46. 046HikariCP Tuning for PostgreSQL: Config #20Read
  47. 047HikariCP Tuning for PostgreSQL: Config #21Read
  48. 048HikariCP Tuning for PostgreSQL: Config #22Read
  49. 049HikariCP Tuning for PostgreSQL: Config #23Read
  50. 050HikariCP Tuning for PostgreSQL: Config #24Read
  51. 051HikariCP Tuning for PostgreSQL: Config #25Read
  52. 052HikariCP Tuning for PostgreSQL: Config #26Read
  53. 053HikariCP Tuning for PostgreSQL: Config #27Read
  54. 054HikariCP Tuning for PostgreSQL: Config #28Read
  55. 055HikariCP Tuning for PostgreSQL: Config #29Read
  56. 056HikariCP Tuning for PostgreSQL: Config #30Read
  57. 057HikariCP Tuning for PostgreSQL: Config #31Read
  58. 058HikariCP Tuning for PostgreSQL: Config #32Read
  59. 059HikariCP Tuning for PostgreSQL: Config #33Read
  60. 060HikariCP Tuning for PostgreSQL: Config #34Read
  61. 061HikariCP Tuning for PostgreSQL: Config #35Read
  62. 062HikariCP Tuning for PostgreSQL: Config #36Read
  63. 063HikariCP Tuning for PostgreSQL: Config #37Read
  64. 064HikariCP Tuning for PostgreSQL: Config #38Read
  65. 065HikariCP Tuning for PostgreSQL: Config #39Read
  66. 066HikariCP Tuning for PostgreSQL: Config #40Read
  67. 067HikariCP Tuning for PostgreSQL: Config #41Read
  68. 068HikariCP Tuning for PostgreSQL: Config #42Read
  69. 069HikariCP Tuning for PostgreSQL: Config #43Read
  70. 070HikariCP Tuning for PostgreSQL: Config #44Read
  71. 071HikariCP Tuning for PostgreSQL: Config #45Read
  72. 072HikariCP Tuning for PostgreSQL: Config #46Read
  73. 073HikariCP Tuning for PostgreSQL: Config #47Read
  74. 074HikariCP Tuning for PostgreSQL: Config #48Read
  75. 075HikariCP Tuning for PostgreSQL: Config #49Read
  76. 076HikariCP Tuning for PostgreSQL: Config #50Read