Logo PostgreSQL Documentation
Back to index
  • Preface
    • 1. What is PostgreSQL?
    • 2. A Brief History of PostgreSQL
      • 2.1. The Berkeley POSTGRES Project
      • 2.2. Postgres95
      • 2.3. PostgreSQL
    • 3. Conventions
    • 4. Further Information
    • 5. Bug Reporting Guidelines
      • 5.1. Identifying Bugs
      • 5.2. What to Report
      • 5.3. Where to Report Bugs
  • I. Tutorial
    • 1. Getting Started
      • 1.1. Installation
      • 1.2. Architectural Fundamentals
      • 1.3. Creating a Database
      • 1.4. Accessing a Database
    • 2. The SQL Language
      • 2.1. Introduction
      • 2.2. Concepts
      • 2.3. Creating a New Table
      • 2.4. Populating a Table With Rows
      • 2.5. Querying a Table
      • 2.6. Joins Between Tables
      • 2.7. Aggregate Functions
      • 2.8. Updates
      • 2.9. Deletions
    • 3. Advanced Features
      • 3.1. Introduction
      • 3.2. Views
      • 3.3. Foreign Keys
      • 3.4. Transactions
      • 3.5. Window Functions
      • 3.6. Inheritance
      • 3.7. Conclusion
  • II. The SQL Language
    • 4. SQL Syntax
      • 4.1. Lexical Structure
      • 4.2. Value Expressions
      • 4.3. Calling Functions
    • 5. Data Definition
      • 5.1. Table Basics
      • 5.2. Default Values
      • 5.3. Constraints
      • 5.4. System Columns
      • 5.5. Modifying Tables
      • 5.6. Privileges
      • 5.7. Row Security Policies
      • 5.8. Schemas
      • 5.9. Inheritance
      • 5.10. Table Partitioning
      • 5.11. Foreign Data
      • 5.12. Other Database Objects
      • 5.13. Dependency Tracking
    • 6. Data Manipulation
      • 6.1. Inserting Data
      • 6.2. Updating Data
      • 6.3. Deleting Data
      • 6.4. Returning Data From Modified Rows
    • 7. Queries
      • 7.1. Overview
      • 7.2. Table Expressions
      • 7.3. Select Lists
      • 7.4. Combining Queries
      • 7.5. Sorting Rows
      • 7.6. LIMIT and OFFSET
      • 7.7. VALUES Lists
      • 7.8. WITH Queries (Common Table Expressions)
    • 8. Data Types
      • 8.1. Numeric Types
      • 8.2. Monetary Types
      • 8.3. Character Types
      • 8.4. Binary Data Types
      • 8.5. Date/Time Types
      • 8.6. Boolean Type
      • 8.7. Enumerated Types
      • 8.8. Geometric Types
      • 8.9. Network Address Types
      • 8.10. Bit String Types
      • 8.11. Text Search Types
      • 8.12. UUID Type
      • 8.13. XML Type
      • 8.14. JSON Types
      • 8.15. Arrays
      • 8.16. Composite Types
      • 8.17. Range Types
      • 8.18. Object Identifier Types
      • 8.19. pg_lsn Type
      • 8.20. Pseudo-Types
    • 9. Functions and Operators
      • 9.1. Logical Operators
      • 9.2. Comparison Functions and Operators
      • 9.3. Mathematical Functions and Operators
      • 9.4. String Functions and Operators
      • 9.5. Binary String Functions and Operators
      • 9.6. Bit String Functions and Operators
      • 9.7. Pattern Matching
      • 9.8. Data Type Formatting Functions
      • 9.9. Date/Time Functions and Operators
      • 9.10. Enum Support Functions
      • 9.11. Geometric Functions and Operators
      • 9.12. Network Address Functions and Operators
      • 9.13. Text Search Functions and Operators
      • 9.14. XML Functions
      • 9.15. JSON Functions and Operators
      • 9.16. Sequence Manipulation Functions
      • 9.17. Conditional Expressions
      • 9.18. Array Functions and Operators
      • 9.19. Range Functions and Operators
      • 9.20. Aggregate Functions
      • 9.21. Window Functions
      • 9.22. Subquery Expressions
      • 9.23. Row and Array Comparisons
      • 9.24. Set Returning Functions
      • 9.25. System Information Functions
      • 9.26. System Administration Functions
      • 9.27. Trigger Functions
      • 9.28. Event Trigger Functions
    • 10. Type Conversion
      • 10.1. Overview
      • 10.2. Operators
      • 10.3. Functions
      • 10.4. Value Storage
      • 10.5. UNION, CASE, and Related Constructs
      • 10.6. SELECT Output Columns
    • 11. Indexes
      • 11.1. Introduction
      • 11.2. Index Types
      • 11.3. Multicolumn Indexes
      • 11.4. Indexes and ORDER BY
      • 11.5. Combining Multiple Indexes
      • 11.6. Unique Indexes
      • 11.7. Indexes on Expressions
      • 11.8. Partial Indexes
      • 11.9. Operator Classes and Operator Families
      • 11.10. Indexes and Collations
      • 11.11. Index-Only Scans
      • 11.12. Examining Index Usage
    • 12. Full Text Search
      • 12.1. Introduction
      • 12.2. Tables and Indexes
      • 12.3. Controlling Text Search
      • 12.4. Additional Features
      • 12.5. Parsers
      • 12.6. Dictionaries
      • 12.7. Configuration Example
      • 12.8. Testing and Debugging Text Search
      • 12.9. GIN and GiST Index Types
      • 12.10. psql Support
      • 12.11. Limitations
    • 13. Concurrency Control
      • 13.1. Introduction
      • 13.2. Transaction Isolation
      • 13.3. Explicit Locking
      • 13.4. Data Consistency Checks at the Application Level
      • 13.5. Caveats
      • 13.6. Locking and Indexes
    • 14. Performance Tips
      • 14.1. Using EXPLAIN
      • 14.2. Statistics Used by the Planner
      • 14.3. Controlling the Planner with Explicit JOIN Clauses
      • 14.4. Populating a Database
      • 14.5. Non-Durable Settings
    • 15. Parallel Query
      • 15.1. How Parallel Query Works
      • 15.2. When Can Parallel Query Be Used?
      • 15.3. Parallel Plans
      • 15.4. Parallel Safety
  • III. Server Administration
    • 16. Installation from Source Code
      • 16.1. Short Version
      • 16.2. Requirements
      • 16.3. Getting The Source
      • 16.4. Installation Procedure
      • 16.5. Post-Installation Setup
      • 16.6. Supported Platforms
      • 16.7. Platform-specific Notes
    • 17. Installation from Source Code on Windows
      • 17.1. Building with Visual C++ or the Microsoft Windows SDK
    • 18. Server Setup and Operation
      • 18.1. The PostgreSQL User Account
      • 18.2. Creating a Database Cluster
      • 18.3. Starting the Database Server
      • 18.4. Managing Kernel Resources
      • 18.5. Shutting Down the Server
      • 18.6. Upgrading a PostgreSQL Cluster
      • 18.7. Preventing Server Spoofing
      • 18.8. Encryption Options
      • 18.9. Secure TCP/IP Connections with SSL
      • 18.10. Secure TCP/IP Connections with SSH Tunnels
      • 18.11. Registering Event Log on Windows
    • 19. Server Configuration
      • 19.1. Setting Parameters
      • 19.2. File Locations
      • 19.3. Connections and Authentication
      • 19.4. Resource Consumption
      • 19.5. Write Ahead Log
      • 19.6. Replication
      • 19.7. Query Planning
      • 19.8. Error Reporting and Logging
      • 19.9. Run-time Statistics
      • 19.10. Automatic Vacuuming
      • 19.11. Client Connection Defaults
      • 19.12. Lock Management
      • 19.13. Version and Platform Compatibility
      • 19.14. Error Handling
      • 19.15. Preset Options
      • 19.16. Customized Options
      • 19.17. Developer Options
      • 19.18. Short Options
    • 20. Client Authentication
      • 20.1. The pg_hba.conf File
      • 20.2. User Name Maps
      • 20.3. Authentication Methods
      • 20.4. Authentication Problems
    • 21. Database Roles
      • 21.1. Database Roles
      • 21.2. Role Attributes
      • 21.3. Role Membership
      • 21.4. Dropping Roles
      • 21.5. Default Roles
      • 21.6. Function Security
    • 22. Managing Databases
      • 22.1. Overview
      • 22.2. Creating a Database
      • 22.3. Template Databases
      • 22.4. Database Configuration
      • 22.5. Destroying a Database
      • 22.6. Tablespaces
    • 23. Localization
      • 23.1. Locale Support
      • 23.2. Collation Support
      • 23.3. Character Set Support
    • 24. Routine Database Maintenance Tasks
      • 24.1. Routine Vacuuming
      • 24.2. Routine Reindexing
      • 24.3. Log File Maintenance
    • 25. Backup and Restore
      • 25.1. SQL Dump
      • 25.2. File System Level Backup
      • 25.3. Continuous Archiving and Point-in-Time Recovery (PITR)
    • 26. High Availability, Load Balancing, and Replication
      • 26.1. Comparison of Different Solutions
      • 26.2. Log-Shipping Standby Servers
      • 26.3. Failover
      • 26.4. Alternative Method for Log Shipping
      • 26.5. Hot Standby
    • 27. Recovery Configuration
      • 27.1. Archive Recovery Settings
      • 27.2. Recovery Target Settings
      • 27.3. Standby Server Settings
    • 28. Monitoring Database Activity
      • 28.1. Standard Unix Tools
      • 28.2. The Statistics Collector
      • 28.3. Viewing Locks
      • 28.4. Progress Reporting
      • 28.5. Dynamic Tracing
    • 29. Monitoring Disk Usage
      • 29.1. Determining Disk Usage
      • 29.2. Disk Full Failure
    • 30. Reliability and the Write-Ahead Log
      • 30.1. Reliability
      • 30.2. Write-Ahead Logging (WAL)
      • 30.3. Asynchronous Commit
      • 30.4. WAL Configuration
      • 30.5. WAL Internals
    • 31. Logical Replication
      • 31.1. Publication
      • 31.2. Subscription
      • 31.3. Conflicts
      • 31.4. Restrictions
      • 31.5. Architecture
      • 31.6. Monitoring
      • 31.7. Security
      • 31.8. Configuration Settings
      • 31.9. Quick Setup
    • 32. Regression Tests
      • 32.1. Running the Tests
      • 32.2. Test Evaluation
      • 32.3. Variant Comparison Files
      • 32.4. TAP Tests
      • 32.5. Test Coverage Examination
  • IV. Client Interfaces
    • 33. libpq - C Library
      • 33.1. Database Connection Control Functions
      • 33.2. Connection Status Functions
      • 33.3. Command Execution Functions
      • 33.4. Asynchronous Command Processing
      • 33.5. Retrieving Query Results Row-By-Row
      • 33.6. Canceling Queries in Progress
      • 33.7. The Fast-Path Interface
      • 33.8. Asynchronous Notification
      • 33.9. Functions Associated with the COPY Command
      • 33.10. Control Functions
      • 33.11. Miscellaneous Functions
      • 33.12. Notice Processing
      • 33.13. Event System
      • 33.14. Environment Variables
      • 33.15. The Password File
      • 33.16. The Connection Service File
      • 33.17. LDAP Lookup of Connection Parameters
      • 33.18. SSL Support
      • 33.19. Behavior in Threaded Programs
      • 33.20. Building libpq Programs
      • 33.21. Example Programs
    • 34. Large Objects
      • 34.1. Introduction
      • 34.2. Implementation Features
      • 34.3. Client Interfaces
      • 34.4. Server-side Functions
      • 34.5. Example Program
    • 35. ECPG - Embedded SQL in C
      • 35.1. The Concept
      • 35.2. Managing Database Connections
      • 35.3. Running SQL Commands
      • 35.4. Using Host Variables
      • 35.5. Dynamic SQL
      • 35.6. pgtypes Library
      • 35.7. Using Descriptor Areas
      • 35.8. Error Handling
      • 35.9. Preprocessor Directives
      • 35.10. Processing Embedded SQL Programs
      • 35.11. Library Functions
      • 35.12. Large Objects
      • 35.13. C++ Applications
      • 35.14. Embedded SQL Commands
      • 35.15. Informix Compatibility Mode
      • 35.16. Internals
    • 36. The Information Schema
      • 36.1. The Schema
      • 36.2. Data Types
      • 36.3. information_schema_catalog_name
      • 36.4. administrable_role_authorizations
      • 36.5. applicable_roles
      • 36.6. attributes
      • 36.7. character_sets
      • 36.8. check_constraint_routine_usage
      • 36.9. check_constraints
      • 36.10. collations
      • 36.11. collation_character_set_applicability
      • 36.12. column_domain_usage
      • 36.13. column_options
      • 36.14. column_privileges
      • 36.15. column_udt_usage
      • 36.16. columns
      • 36.17. constraint_column_usage
      • 36.18. constraint_table_usage
      • 36.19. data_type_privileges
      • 36.20. domain_constraints
      • 36.21. domain_udt_usage
      • 36.22. domains
      • 36.23. element_types
      • 36.24. enabled_roles
      • 36.25. foreign_data_wrapper_options
      • 36.26. foreign_data_wrappers
      • 36.27. foreign_server_options
      • 36.28. foreign_servers
      • 36.29. foreign_table_options
      • 36.30. foreign_tables
      • 36.31. key_column_usage
      • 36.32. parameters
      • 36.33. referential_constraints
      • 36.34. role_column_grants
      • 36.35. role_routine_grants
      • 36.36. role_table_grants
      • 36.37. role_udt_grants
      • 36.38. role_usage_grants
      • 36.39. routine_privileges
      • 36.40. routines
      • 36.41. schemata
      • 36.42. sequences
      • 36.43. sql_features
      • 36.44. sql_implementation_info
      • 36.45. sql_languages
      • 36.46. sql_packages
      • 36.47. sql_parts
      • 36.48. sql_sizing
      • 36.49. sql_sizing_profiles
      • 36.50. table_constraints
      • 36.51. table_privileges
      • 36.52. tables
      • 36.53. transforms
      • 36.54. triggered_update_columns
      • 36.55. triggers
      • 36.56. udt_privileges
      • 36.57. usage_privileges
      • 36.58. user_defined_types
      • 36.59. user_mapping_options
      • 36.60. user_mappings
      • 36.61. view_column_usage
      • 36.62. view_routine_usage
      • 36.63. view_table_usage
      • 36.64. views
  • V. Server Programming
    • 37. Extending SQL
      • 37.1. How Extensibility Works
      • 37.2. The PostgreSQL Type System
      • 37.3. User-defined Functions
      • 37.4. Query Language (SQL) Functions
      • 37.5. Function Overloading
      • 37.6. Function Volatility Categories
      • 37.7. Procedural Language Functions
      • 37.8. Internal Functions
      • 37.9. C-Language Functions
      • 37.10. User-defined Aggregates
      • 37.11. User-defined Types
      • 37.12. User-defined Operators
      • 37.13. Operator Optimization Information
      • 37.14. Interfacing Extensions To Indexes
      • 37.15. Packaging Related Objects into an Extension
      • 37.16. Extension Building Infrastructure
    • 38. Triggers
      • 38.1. Overview of Trigger Behavior
      • 38.2. Visibility of Data Changes
      • 38.3. Writing Trigger Functions in C
      • 38.4. A Complete Trigger Example
    • 39. Event Triggers
      • 39.1. Overview of Event Trigger Behavior
      • 39.2. Event Trigger Firing Matrix
      • 39.3. Writing Event Trigger Functions in C
      • 39.4. A Complete Event Trigger Example
      • 39.5. A Table Rewrite Event Trigger Example
    • 40. The Rule System
      • 40.1. The Query Tree
      • 40.2. Views and the Rule System
      • 40.3. Materialized Views
      • 40.4. Rules on INSERT, UPDATE, and DELETE
      • 40.5. Rules and Privileges
      • 40.6. Rules and Command Status
      • 40.7. Rules Versus Triggers
    • 41. Procedural Languages
      • 41.1. Installing Procedural Languages
    • 42. PL/pgSQL - SQL Procedural Language
      • 42.1. Overview
      • 42.2. Structure of PL/pgSQL
      • 42.3. Declarations
      • 42.4. Expressions
      • 42.5. Basic Statements
      • 42.6. Control Structures
      • 42.7. Cursors
      • 42.8. Errors and Messages
      • 42.9. Trigger Procedures
      • 42.10. PL/pgSQL Under the Hood
      • 42.11. Tips for Developing in PL/pgSQL
      • 42.12. Porting from Oracle PL/SQL
    • 43. PL/Tcl - Tcl Procedural Language
      • 43.1. Overview
      • 43.2. PL/Tcl Functions and Arguments
      • 43.3. Data Values in PL/Tcl
      • 43.4. Global Data in PL/Tcl
      • 43.5. Database Access from PL/Tcl
      • 43.6. Trigger Procedures in PL/Tcl
      • 43.7. Event Trigger Procedures in PL/Tcl
      • 43.8. Error Handling in PL/Tcl
      • 43.9. Explicit Subtransactions in PL/Tcl
      • 43.10. PL/Tcl Configuration
      • 43.11. Tcl Procedure Names
    • 44. PL/Perl - Perl Procedural Language
      • 44.1. PL/Perl Functions and Arguments
      • 44.2. Data Values in PL/Perl
      • 44.3. Built-in Functions
      • 44.4. Global Values in PL/Perl
      • 44.5. Trusted and Untrusted PL/Perl
      • 44.6. PL/Perl Triggers
      • 44.7. PL/Perl Event Triggers
      • 44.8. PL/Perl Under the Hood
    • 45. PL/Python - Python Procedural Language
      • 45.1. Python 2 vs. Python 3
      • 45.2. PL/Python Functions
      • 45.3. Data Values
      • 45.4. Sharing Data
      • 45.5. Anonymous Code Blocks
      • 45.6. Trigger Functions
      • 45.7. Database Access
      • 45.8. Explicit Subtransactions
      • 45.9. Utility Functions
      • 45.10. Environment Variables
    • 46. Server Programming Interface
      • 46.1. Interface Functions
      • 46.2. Interface Support Functions
      • 46.3. Memory Management
      • 46.4. Visibility of Data Changes
      • 46.5. Examples
    • 47. Background Worker Processes
    • 48. Logical Decoding
      • 48.1. Logical Decoding Examples
      • 48.2. Logical Decoding Concepts
      • 48.3. Streaming Replication Protocol Interface
      • 48.4. Logical Decoding SQL Interface
      • 48.5. System Catalogs Related to Logical Decoding
      • 48.6. Logical Decoding Output Plugins
      • 48.7. Logical Decoding Output Writers
      • 48.8. Synchronous Replication Support for Logical Decoding
    • 49. Replication Progress Tracking
  • VI. Reference
    • I. SQL Commands
      • ABORT — abort the current transaction
      • ALTER AGGREGATE — change the definition of an aggregate function
      • ALTER COLLATION — change the definition of a collation
      • ALTER CONVERSION — change the definition of a conversion
      • ALTER DATABASE — change a database
      • ALTER DEFAULT PRIVILEGES — define default access privileges
      • ALTER DOMAIN — change the definition of a domain
      • ALTER EVENT TRIGGER — change the definition of an event trigger
      • ALTER EXTENSION — change the definition of an extension
      • ALTER FOREIGN DATA WRAPPER — change the definition of a foreign-data wrapper
      • ALTER FOREIGN TABLE — change the definition of a foreign table
      • ALTER FUNCTION — change the definition of a function
      • ALTER GROUP — change role name or membership
      • ALTER INDEX — change the definition of an index
      • ALTER LANGUAGE — change the definition of a procedural language
      • ALTER LARGE OBJECT — change the definition of a large object
      • ALTER MATERIALIZED VIEW — change the definition of a materialized view
      • ALTER OPERATOR — change the definition of an operator
      • ALTER OPERATOR CLASS — change the definition of an operator class
      • ALTER OPERATOR FAMILY — change the definition of an operator family
      • ALTER POLICY — change the definition of a row level security policy
      • ALTER PUBLICATION — change the definition of a publication
      • ALTER ROLE — change a database role
      • ALTER RULE — change the definition of a rule
      • ALTER SCHEMA — change the definition of a schema
      • ALTER SEQUENCE — change the definition of a sequence generator
      • ALTER SERVER — change the definition of a foreign server
      • ALTER STATISTICS — change the definition of an extended statistics object
      • ALTER SUBSCRIPTION — change the definition of a subscription
      • ALTER SYSTEM — change a server configuration parameter
      • ALTER TABLE — change the definition of a table
      • ALTER TABLESPACE — change the definition of a tablespace
      • ALTER TEXT SEARCH CONFIGURATION — change the definition of a text search configuration
      • ALTER TEXT SEARCH DICTIONARY — change the definition of a text search dictionary
      • ALTER TEXT SEARCH PARSER — change the definition of a text search parser
      • ALTER TEXT SEARCH TEMPLATE — change the definition of a text search template
      • ALTER TRIGGER — change the definition of a trigger
      • ALTER TYPE — change the definition of a type
      • ALTER USER — change a database role
      • ALTER USER MAPPING — change the definition of a user mapping
      • ALTER VIEW — change the definition of a view
      • ANALYZE — collect statistics about a database
      • BEGIN — start a transaction block
      • CHECKPOINT — force a write-ahead log checkpoint
      • CLOSE — close a cursor
      • CLUSTER — cluster a table according to an index
      • COMMENT — define or change the comment of an object
      • COMMIT — commit the current transaction
      • COMMIT PREPARED — commit a transaction that was earlier prepared for two-phase commit
      • COPY — copy data between a file and a table
      • CREATE ACCESS METHOD — define a new access method
      • CREATE AGGREGATE — define a new aggregate function
      • CREATE CAST — define a new cast
      • CREATE COLLATION — define a new collation
      • CREATE CONVERSION — define a new encoding conversion
      • CREATE DATABASE — create a new database
      • CREATE DOMAIN — define a new domain
      • CREATE EVENT TRIGGER — define a new event trigger
      • CREATE EXTENSION — install an extension
      • CREATE FOREIGN DATA WRAPPER — define a new foreign-data wrapper
      • CREATE FOREIGN TABLE — define a new foreign table
      • CREATE FUNCTION — define a new function
      • CREATE GROUP — define a new database role
      • CREATE INDEX — define a new index
      • CREATE LANGUAGE — define a new procedural language
      • CREATE MATERIALIZED VIEW — define a new materialized view
      • CREATE OPERATOR — define a new operator
      • CREATE OPERATOR CLASS — define a new operator class
      • CREATE OPERATOR FAMILY — define a new operator family
      • CREATE POLICY — define a new row level security policy for a table
      • CREATE PUBLICATION — define a new publication
      • CREATE ROLE — define a new database role
      • CREATE RULE — define a new rewrite rule
      • CREATE SCHEMA — define a new schema
      • CREATE SEQUENCE — define a new sequence generator
      • CREATE SERVER — define a new foreign server
      • CREATE STATISTICS — define extended statistics
      • CREATE SUBSCRIPTION — define a new subscription
      • CREATE TABLE — define a new table
      • CREATE TABLE AS — define a new table from the results of a query
      • CREATE TABLESPACE — define a new tablespace
      • CREATE TEXT SEARCH CONFIGURATION — define a new text search configuration
      • CREATE TEXT SEARCH DICTIONARY — define a new text search dictionary
      • CREATE TEXT SEARCH PARSER — define a new text search parser
      • CREATE TEXT SEARCH TEMPLATE — define a new text search template
      • CREATE TRANSFORM — define a new transform
      • CREATE TRIGGER — define a new trigger
      • CREATE TYPE — define a new data type
      • CREATE USER — define a new database role
      • CREATE USER MAPPING — define a new mapping of a user to a foreign server
      • CREATE VIEW — define a new view
      • DEALLOCATE — deallocate a prepared statement
      • DECLARE — define a cursor
      • DELETE — delete rows of a table
      • DISCARD — discard session state
      • DO — execute an anonymous code block
      • DROP ACCESS METHOD — remove an access method
      • DROP AGGREGATE — remove an aggregate function
      • DROP CAST — remove a cast
      • DROP COLLATION — remove a collation
      • DROP CONVERSION — remove a conversion
      • DROP DATABASE — remove a database
      • DROP DOMAIN — remove a domain
      • DROP EVENT TRIGGER — remove an event trigger
      • DROP EXTENSION — remove an extension
      • DROP FOREIGN DATA WRAPPER — remove a foreign-data wrapper
      • DROP FOREIGN TABLE — remove a foreign table
      • DROP FUNCTION — remove a function
      • DROP GROUP — remove a database role
      • DROP INDEX — remove an index
      • DROP LANGUAGE — remove a procedural language
      • DROP MATERIALIZED VIEW — remove a materialized view
      • DROP OPERATOR — remove an operator
      • DROP OPERATOR CLASS — remove an operator class
      • DROP OPERATOR FAMILY — remove an operator family
      • DROP OWNED — remove database objects owned by a database role
      • DROP POLICY — remove a row level security policy from a table
      • DROP PUBLICATION — remove a publication
      • DROP ROLE — remove a database role
      • DROP RULE — remove a rewrite rule
      • DROP SCHEMA — remove a schema
      • DROP SEQUENCE — remove a sequence
      • DROP SERVER — remove a foreign server descriptor
      • DROP STATISTICS — remove extended statistics
      • DROP SUBSCRIPTION — remove a subscription
      • DROP TABLE — remove a table
      • DROP TABLESPACE — remove a tablespace
      • DROP TEXT SEARCH CONFIGURATION — remove a text search configuration
      • DROP TEXT SEARCH DICTIONARY — remove a text search dictionary
      • DROP TEXT SEARCH PARSER — remove a text search parser
      • DROP TEXT SEARCH TEMPLATE — remove a text search template
      • DROP TRANSFORM — remove a transform
      • DROP TRIGGER — remove a trigger
      • DROP TYPE — remove a data type
      • DROP USER — remove a database role
      • DROP USER MAPPING — remove a user mapping for a foreign server
      • DROP VIEW — remove a view
      • END — commit the current transaction
      • EXECUTE — execute a prepared statement
      • EXPLAIN — show the execution plan of a statement
      • FETCH — retrieve rows from a query using a cursor
      • GRANT — define access privileges
      • IMPORT FOREIGN SCHEMA — import table definitions from a foreign server
      • INSERT — create new rows in a table
      • LISTEN — listen for a notification
      • LOAD — load a shared library file
      • LOCK — lock a table
      • MOVE — position a cursor
      • NOTIFY — generate a notification
      • PREPARE — prepare a statement for execution
      • PREPARE TRANSACTION — prepare the current transaction for two-phase commit
      • REASSIGN OWNED — change the ownership of database objects owned by a database role
      • REFRESH MATERIALIZED VIEW — replace the contents of a materialized view
      • REINDEX — rebuild indexes
      • RELEASE SAVEPOINT — destroy a previously defined savepoint
      • RESET — restore the value of a run-time parameter to the default value
      • REVOKE — remove access privileges
      • ROLLBACK — abort the current transaction
      • ROLLBACK PREPARED — cancel a transaction that was earlier prepared for two-phase commit
      • ROLLBACK TO SAVEPOINT — roll back to a savepoint
      • SAVEPOINT — define a new savepoint within the current transaction
      • SECURITY LABEL — define or change a security label applied to an object
      • SELECT — retrieve rows from a table or view
      • SELECT INTO — define a new table from the results of a query
      • SET — change a run-time parameter
      • SET CONSTRAINTS — set constraint check timing for the current transaction
      • SET ROLE — set the current user identifier of the current session
      • SET SESSION AUTHORIZATION — set the session user identifier and the current user identifier of the current session
      • SET TRANSACTION — set the characteristics of the current transaction
      • SHOW — show the value of a run-time parameter
      • START TRANSACTION — start a transaction block
      • TRUNCATE — empty a table or set of tables
      • UNLISTEN — stop listening for a notification
      • UPDATE — update rows of a table
      • VACUUM — garbage-collect and optionally analyze a database
      • VALUES — compute a set of rows
    • II. PostgreSQL Client Applications
      • clusterdb — cluster a PostgreSQL database
      • createdb — create a new PostgreSQL database
      • createuser — define a new PostgreSQL user account
      • dropdb — remove a PostgreSQL database
      • dropuser — remove a PostgreSQL user account
      • ecpg — embedded SQL C preprocessor
      • pg_basebackup — take a base backup of a PostgreSQL cluster
      • pgbench — run a benchmark test on PostgreSQL
      • pg_config — retrieve information about the installed version of PostgreSQL
      • pg_dump — extract a PostgreSQL database into a script file or other archive file
      • pg_dumpall — extract a PostgreSQL database cluster into a script file
      • pg_isready — check the connection status of a PostgreSQL server
      • pg_receivewal — stream write-ahead logs from a PostgreSQL server
      • pg_recvlogical — control PostgreSQL logical decoding streams
      • pg_restore — restore a PostgreSQL database from an archive file created by pg_dump
      • psql — PostgreSQL interactive terminal
      • reindexdb — reindex a PostgreSQL database
      • vacuumdb — garbage-collect and analyze a PostgreSQL database
    • III. PostgreSQL Server Applications
      • initdb — create a new PostgreSQL database cluster
      • pg_archivecleanup — clean up PostgreSQL WAL archive files
      • pg_controldata — display control information of a PostgreSQL database cluster
      • pg_ctl — initialize, start, stop, or control a PostgreSQL server
      • pg_resetwal — reset the write-ahead log and other control information of a PostgreSQL database cluster
      • pg_rewind — synchronize a PostgreSQL data directory with another data directory that was forked from it
      • pg_test_fsync — determine fastest wal_sync_method for PostgreSQL
      • pg_test_timing — measure timing overhead
      • pg_upgrade — upgrade a PostgreSQL server instance
      • pg_waldump — display a human-readable rendering of the write-ahead log of a PostgreSQL database cluster
      • postgres — PostgreSQL database server
      • postmaster — PostgreSQL database server
  • VII. Internals
    • 50. Overview of PostgreSQL Internals
      • 50.1. The Path of a Query
      • 50.2. How Connections are Established
      • 50.3. The Parser Stage
      • 50.4. The PostgreSQL Rule System
      • 50.5. Planner/Optimizer
      • 50.6. Executor
    • 51. System Catalogs
      • 51.1. Overview
      • 51.2. pg_aggregate
      • 51.3. pg_am
      • 51.4. pg_amop
      • 51.5. pg_amproc
      • 51.6. pg_attrdef
      • 51.7. pg_attribute
      • 51.8. pg_authid
      • 51.9. pg_auth_members
      • 51.10. pg_cast
      • 51.11. pg_class
      • 51.12. pg_collation
      • 51.13. pg_constraint
      • 51.14. pg_conversion
      • 51.15. pg_database
      • 51.16. pg_db_role_setting
      • 51.17. pg_default_acl
      • 51.18. pg_depend
      • 51.19. pg_description
      • 51.20. pg_enum
      • 51.21. pg_event_trigger
      • 51.22. pg_extension
      • 51.23. pg_foreign_data_wrapper
      • 51.24. pg_foreign_server
      • 51.25. pg_foreign_table
      • 51.26. pg_index
      • 51.27. pg_inherits
      • 51.28. pg_init_privs
      • 51.29. pg_language
      • 51.30. pg_largeobject
      • 51.31. pg_largeobject_metadata
      • 51.32. pg_namespace
      • 51.33. pg_opclass
      • 51.34. pg_operator
      • 51.35. pg_opfamily
      • 51.36. pg_partitioned_table
      • 51.37. pg_pltemplate
      • 51.38. pg_policy
      • 51.39. pg_proc
      • 51.40. pg_publication
      • 51.41. pg_publication_rel
      • 51.42. pg_range
      • 51.43. pg_replication_origin
      • 51.44. pg_rewrite
      • 51.45. pg_seclabel
      • 51.46. pg_sequence
      • 51.47. pg_shdepend
      • 51.48. pg_shdescription
      • 51.49. pg_shseclabel
      • 51.50. pg_statistic
      • 51.51. pg_statistic_ext
      • 51.52. pg_subscription
      • 51.53. pg_subscription_rel
      • 51.54. pg_tablespace
      • 51.55. pg_transform
      • 51.56. pg_trigger
      • 51.57. pg_ts_config
      • 51.58. pg_ts_config_map
      • 51.59. pg_ts_dict
      • 51.60. pg_ts_parser
      • 51.61. pg_ts_template
      • 51.62. pg_type
      • 51.63. pg_user_mapping
      • 51.64. System Views
      • 51.65. pg_available_extensions
      • 51.66. pg_available_extension_versions
      • 51.67. pg_config
      • 51.68. pg_cursors
      • 51.69. pg_file_settings
      • 51.70. pg_group
      • 51.71. pg_hba_file_rules
      • 51.72. pg_indexes
      • 51.73. pg_locks
      • 51.74. pg_matviews
      • 51.75. pg_policies
      • 51.76. pg_prepared_statements
      • 51.77. pg_prepared_xacts
      • 51.78. pg_publication_tables
      • 51.79. pg_replication_origin_status
      • 51.80. pg_replication_slots
      • 51.81. pg_roles
      • 51.82. pg_rules
      • 51.83. pg_seclabels
      • 51.84. pg_sequences
      • 51.85. pg_settings
      • 51.86. pg_shadow
      • 51.87. pg_stats
      • 51.88. pg_tables
      • 51.89. pg_timezone_abbrevs
      • 51.90. pg_timezone_names
      • 51.91. pg_user
      • 51.92. pg_user_mappings
      • 51.93. pg_views
    • 52. Frontend/Backend Protocol
      • 52.1. Overview
      • 52.2. Message Flow
      • 52.3. SASL Authentication
      • 52.4. Streaming Replication Protocol
      • 52.5. Logical Streaming Replication Protocol
      • 52.6. Message Data Types
      • 52.7. Message Formats
      • 52.8. Error and Notice Message Fields
      • 52.9. Logical Replication Message Formats
      • 52.10. Summary of Changes since Protocol 2.0
    • 53. PostgreSQL Coding Conventions
      • 53.1. Formatting
      • 53.2. Reporting Errors Within the Server
      • 53.3. Error Message Style Guide
      • 53.4. Miscellaneous Coding Conventions
    • 54. Native Language Support
      • 54.1. For the Translator
      • 54.2. For the Programmer
    • 55. Writing A Procedural Language Handler
    • 56. Writing A Foreign Data Wrapper
      • 56.1. Foreign Data Wrapper Functions
      • 56.2. Foreign Data Wrapper Callback Routines
      • 56.3. Foreign Data Wrapper Helper Functions
      • 56.4. Foreign Data Wrapper Query Planning
      • 56.5. Row Locking in Foreign Data Wrappers
    • 57. Writing A Table Sampling Method
      • 57.1. Sampling Method Support Functions
    • 58. Writing A Custom Scan Provider
      • 58.1. Creating Custom Scan Paths
      • 58.2. Creating Custom Scan Plans
      • 58.3. Executing Custom Scans
    • 59. Genetic Query Optimizer
      • 59.1. Query Handling as a Complex Optimization Problem
      • 59.2. Genetic Algorithms
      • 59.3. Genetic Query Optimization (GEQO) in PostgreSQL
      • 59.4. Further Reading
    • 60. Index Access Method Interface Definition
      • 60.1. Basic API Structure for Indexes
      • 60.2. Index Access Method Functions
      • 60.3. Index Scanning
      • 60.4. Index Locking Considerations
      • 60.5. Index Uniqueness Checks
      • 60.6. Index Cost Estimation Functions
    • 61. Generic WAL Records
    • 62. GiST Indexes
      • 62.1. Introduction
      • 62.2. Built-in Operator Classes
      • 62.3. Extensibility
      • 62.4. Implementation
      • 62.5. Examples
    • 63. SP-GiST Indexes
      • 63.1. Introduction
      • 63.2. Built-in Operator Classes
      • 63.3. Extensibility
      • 63.4. Implementation
      • 63.5. Examples
    • 64. GIN Indexes
      • 64.1. Introduction
      • 64.2. Built-in Operator Classes
      • 64.3. Extensibility
      • 64.4. Implementation
      • 64.5. GIN Tips and Tricks
      • 64.6. Limitations
      • 64.7. Examples
    • 65. BRIN Indexes
      • 65.1. Introduction
      • 65.2. Built-in Operator Classes
      • 65.3. Extensibility
    • 66. Database Physical Storage
      • 66.1. Database File Layout
      • 66.2. TOAST
      • 66.3. Free Space Map
      • 66.4. Visibility Map
      • 66.5. The Initialization Fork
      • 66.6. Database Page Layout
    • 67. BKI Backend Interface
      • 67.1. BKI File Format
      • 67.2. BKI Commands
      • 67.3. Structure of the Bootstrap BKI File
      • 67.4. Example
    • 68. How the Planner Uses Statistics
      • 68.1. Row Estimation Examples
      • 68.2. Multivariate Statistics Examples
      • 68.3. Planner Statistics and Security
  • VIII. Appendixes
    • A. PostgreSQL Error Codes
    • B. Date/Time Support
      • B.1. Date/Time Input Interpretation
      • B.2. Handling of Invalid or Ambiguous Timestamps
      • B.3. Date/Time Key Words
      • B.4. Date/Time Configuration Files
      • B.5. History of Units
    • C. SQL Key Words
    • D. SQL Conformance
      • D.1. Supported Features
      • D.2. Unsupported Features
      • D.3. XML Limits and Conformance to SQL/XML
    • E. Release Notes
      • E.1. Release 10.13
      • E.2. Release 10.12
      • E.3. Release 10.11
      • E.4. Release 10.10
      • E.5. Release 10.9
      • E.6. Release 10.8
      • E.7. Release 10.7
      • E.8. Release 10.6
      • E.9. Release 10.5
      • E.10. Release 10.4
      • E.11. Release 10.3
      • E.12. Release 10.2
      • E.13. Release 10.1
      • E.14. Release 10
      • E.15. Prior Releases
    • F. Additional Supplied Modules
      • F.1. adminpack
      • F.2. amcheck
      • F.3. auth_delay
      • F.4. auto_explain
      • F.5. bloom
      • F.6. btree_gin
      • F.7. btree_gist
      • F.8. chkpass
      • F.9. citext
      • F.10. cube
      • F.11. dblink
      • F.12. dict_int
      • F.13. dict_xsyn
      • F.14. earthdistance
      • F.15. file_fdw
      • F.16. fuzzystrmatch
      • F.17. hstore
      • F.18. intagg
      • F.19. intarray
      • F.20. isn
      • F.21. lo
      • F.22. ltree
      • F.23. pageinspect
      • F.24. passwordcheck
      • F.25. pg_buffercache
      • F.26. pgcrypto
      • F.27. pg_freespacemap
      • F.28. pg_prewarm
      • F.29. pgrowlocks
      • F.30. pg_stat_statements
      • F.31. pgstattuple
      • F.32. pg_trgm
      • F.33. pg_visibility
      • F.34. postgres_fdw
      • F.35. seg
      • F.36. sepgsql
      • F.37. spi
      • F.38. sslinfo
      • F.39. tablefunc
      • F.40. tcn
      • F.41. test_decoding
      • F.42. tsm_system_rows
      • F.43. tsm_system_time
      • F.44. unaccent
      • F.45. uuid-ossp
      • F.46. xml2
    • G. Additional Supplied Programs
      • G.1. Client Applications
      • G.2. Server Applications
    • H. External Projects
      • H.1. Client Interfaces
      • H.2. Administration Tools
      • H.3. Procedural Languages
      • H.4. Extensions
    • I. The Source Code Repository
      • I.1. Getting The Source via Git
    • J. Documentation
      • J.1. DocBook
      • J.2. Tool Sets
      • J.3. Building The Documentation
      • J.4. Documentation Authoring
      • J.5. Style Guide
    • K. Acronyms
  • Bibliography
  • Index
Home API Docs Tools
Home
API Docs
Tools

Chapter 14. Performance Tips

Table of Contents

14.1. Using EXPLAIN
14.1.1. EXPLAIN Basics
14.1.2. EXPLAIN ANALYZE
14.1.3. Caveats
14.2. Statistics Used by the Planner
14.2.1. Single-Column Statistics
14.2.2. Extended Statistics
14.3. Controlling the Planner with Explicit JOIN Clauses
14.4. Populating a Database
14.4.1. Disable Autocommit
14.4.2. Use COPY
14.4.3. Remove Indexes
14.4.4. Remove Foreign Key Constraints
14.4.5. Increase maintenance_work_mem
14.4.6. Increase max_wal_size
14.4.7. Disable WAL Archival and Streaming Replication
14.4.8. Run ANALYZE Afterwards
14.4.9. Some Notes About pg_dump
14.5. Non-Durable Settings

Query performance can be affected by many things. Some of these can be controlled by the user, while others are fundamental to the underlying design of the system. This chapter provides some hints about understanding and tuning PostgreSQL performance.

Name PostgreSQL Documentation
Version
Language
Badge
Last Updated 2021-07-07T13:01:31
扫码关注公众号

Docs4dev®

如果你在使用过程中遇到任何问题,可以在 这里 提issue。

蜀ICP备14021783号-6

Copyright © Docs4dev all right reserved, powered by Docs4dev