You are previewing PostgreSQL: Up and Running.

PostgreSQL: Up and Running

Cover of PostgreSQL: Up and Running by Regina Obe... Published by O'Reilly Media, Inc.
  1. PostgreSQL: Up and Running
  2. SPECIAL OFFER: Upgrade this ebook with O’Reilly
  3. Preface
    1. What Makes PostgreSQL Special and Why Use It?
    2. Why Not PostgreSQL?
    3. For More Information on PostgreSQL
    4. Conventions Used in This Book
    5. Using Code Examples
    6. Safari® Books Online
    7. How to Contact Us
  4. 1. The Basics
    1. Where to Get PostgreSQL
      1. Notable PostgreSQL Forks
    2. Administration Tools
    3. What’s New in Latest Versions of PostgreSQL?
      1. Why Upgrade?
      2. What to Look for in PostgreSQL 9.2
      3. PostgreSQL 9.1 Improvements
    4. Database Drivers
    5. Server and Database Objects
    6. Where to Get Help
  5. 2. Database Administration
    1. Configuration Files
      1. The postgresql.conf File
      2. The pg_hba.conf File
      3. Reload the Configuration Files
    2. Setting Up Groups and Login Roles (Users)
      1. Creating an Account That Can Log In
      2. Creating Group Roles
      3. Roles Inheriting Rights
    3. Databases and Management
      1. Creating and Using a Template Database
      2. Organizing Your Database Using Schemas
      3. Permissions
    4. Extensions and Contribs
      1. Installing Extensions
      2. Common Extensions
    5. Backup
      1. Selective Backup Using pg_dump
      2. Systemwide Backup Using pg_dumpall
    6. Restore
      1. Terminating Connections
      2. Using psql to Restore Plain Text SQL backups
      3. Using pg_restore
    7. Managing Disk Space with Tablespaces
      1. Creating Tablespaces
      2. Moving Objects Between Tablespaces
    8. Verboten
      1. Delete PostgreSQL Core System Files and Binaries
      2. Giving Full Administrative Rights to the Postgres System (Daemon) Account
      3. Setting shared_buffers Too High
      4. Trying to Start PostgreSQL on a Port Already in Use
  6. 3. psql
    1. Interactive psql
    2. Non-Interactive psql
    3. Session Configurations
      1. Changing Prompts
      2. Timing Details
      3. AUTOCOMMIT
      4. Shortcuts
      5. Retrieving Prior Commands
    4. psql Gems
      1. Executing Shell Commands
      2. Lists and Structures
    5. Importing and Exporting Data
    6. Basic Reporting
  7. 4. Using pgAdmin
    1. Getting Started
      1. Overview of Features
      2. Connecting to a PostgreSQL server
      3. Navigating pgAdmin
    2. pgAdmin Features
      1. Accessing psql from pgAdmin
      2. Editing postgresql.conf and pg_hba.conf from pgAdmin
      3. Creating Databases and Setting Permissions
      4. Backup and Restore
    3. pgScript
    4. Graphical Explain
    5. Job Scheduling with pgAgent
      1. Installing pgAgent
      2. Scheduling Jobs
      3. Helpful Queries
  8. 5. Data Types
    1. Numeric Data Types
      1. Serial
      2. Generate Series Function
    2. Arrays
      1. Array Constructors
      2. Referencing Elements in An Array
      3. Array Slicing and Splicing
    3. Character Types
      1. String Functions
      2. Splitting Strings into Arrays, Tables, or Substrings
      3. Regular Expressions and Pattern Matching
    4. Temporal Data Types
      1. Time Zones: What It Is and What It Isn’t
      2. Operators and Functions for Date and Time Data Types
    5. XML
      1. Loading XML Data
      2. Querying XML Data
    6. Custom and Composite Data Types
      1. All Tables Are Custom
      2. Building Your Own Custom Type
  9. 6. Of Tables, Constraints, and Indexes
    1. Tables
      1. Table Creation
      2. Multi-Row Insert
      3. An Elaborate Insert
    2. Constraints
      1. Foreign Key Constraints
      2. Unique Constraints
      3. Check Constraints
      4. Exclusion Constraints
    3. Indexes
      1. PostgreSQL Stock Indexes
      2. Operator Class
      3. Functional Indexes
      4. Partial Indexes
      5. Multicolumn Indexes
  10. 7. SQL: The PostgreSQL Way
    1. SQL Views
    2. Window Functions
      1. Partition By
      2. Order By
    3. Common Table Expressions
      1. Standard CTE
      2. Writeable CTEs
      3. Recursive CTE
    4. Constructions Unique to PostgreSQL
      1. DISTINCT ON
      2. LIMIT and OFFSET
      3. Shorthand Casting
      4. ILIKE for Case Insensitive Search
      5. Set Returning Functions in SELECT
      6. Selective DELETE, UPDATE, and SELECT from Inherited Tables
      7. RETURNING Changed Records
      8. Composite Types in Queries
  11. 8. Writing Functions
    1. Anatomy of PostgreSQL Functions
      1. Function Basics
      2. Trusted and Untrusted Languages
    2. Writing Functions with SQL
    3. Writing PL/pgSQL Functions
    4. Writing PL/Python Functions
      1. Basic Python Function
    5. Trigger Functions
    6. Aggregates
  12. 9. Query Performance Tuning
    1. EXPLAIN and EXPLAIN ANALYZE
    2. Writing Better Queries
      1. Overusing Subqueries in SELECT
      2. Avoid SELECT *
      3. Make Good Use of CASE
    3. Guiding the Query Planner
      1. Strategy Settings
      2. How Useful Is Your Index?
      3. Table Stats
      4. Random Page Cost and Quality of Drives
    4. Caching
  13. 10. Replication and External Data
    1. Replication Overview
      1. Replication Lingo
      2. PostgreSQL Built-in Replication Advancements
      3. Third-Party Replication Options
    2. Setting Up Replication
      1. Configuring the Master
      2. Configuring the Slaves
      3. Initiate the Replication Process
    3. Foreign Data Wrappers (FDW)
      1. Querying Simple Flat File Data Sources
      2. Querying More Complex Data Sources
  14. A. Install, Hosting, and Command-Line Guides
    1. Installation Guides and Distributions
      1. Windows, Mac OS X, Linux Desktops
      2. Other Linux, Unix, Mac Distributions
    2. Where to Host PostgreSQL
      1. Virtual Private Server (VPS)/Virtual Dedicated Server
      2. Cloud Server Hosters
      3. PostgreSQL Database as a Service
    3. PostgreSQL Packaged Command-Line Tools
      1. Database Backup: pg_dump
      2. Server Backup: pg_dumpall
      3. Database Backup: pg_restore
      4. psql: Interactive and Scriptable
  15. About the Authors
  16. SPECIAL OFFER: Upgrade this ebook with O’Reilly
  17. Copyright
O'Reilly logo

Chapter 9. Query Performance Tuning

Sooner or later, we’ll all face a query that takes just a bit longer to execute than what we have patience for. The best and easiest fix to a sluggish query is to perfect the underlying SQL, followed by adding indexes, and updating planner statistics. To guide you in these pursuits, Postgres comes with a built-in explainer that informs you how the query planner is going to execute your SQL. Armed with your knack for writing flawless SQL, your instinct to sniff out useful indexes, and the insight of the explainer, you should have no trouble getting your queries to run as fast as what your hardware budget will allow.

EXPLAIN and EXPLAIN ANALYZE

The easiest tool for targeting query performance problems is using the EXPLAIN and EXPLAIN ANALYZE commands. These have been around ever since the early years of PostgreSQL. Since then it has matured into a full-blown tool capable of reporting highly detailed information about the query execution. Along the way, it added to its number of output formats. In PostgreSQL 9.0+, you can even dump the output to XML or JSON. Perhaps the most exciting enhancement for the common user came when pgAdmin introduced graphical EXPLAIN several years back. With a hard and long stare, you can identify where the bottlenecks are in your query, which tables are missing indexes, and whether the path of execution took an unexpected turn.

EXPLAIN will give you just an idea of how the planner intends to execute the query without running ...

The best content for your career. Discover unlimited learning on demand for around $1/day.