Search PostgreSQL documentation

EXPLAINManual ChaptersPG18E.6.3.2.3. EXPLAIN Automatically include BUFFERS output in EXPLAIN ANALYZE (Guillaume Lelarge, David Rowley) § Add full WAL buffer count to EXPLAIN (WAL) output (Bertrand Drouvot) § In EXPLA…EXPLAIN EXPLAINManual ChaptersPG18EXPLAIN EXPLAIN — show the execution plan of a statement Synopsis EXPLAIN [ ( option [, ...] ) ] statement where option can be one of: ANALYZE [ boolean ] VERBOSE [ boolean ] COSTS [ boolean…EXPLAIN EXPLAIN (DEBUG)Manual ChaptersPG18F.29.1. EXPLAIN (DEBUG) The DEBUG option displays miscellaneous information from the plan tree that is not normally shown because it is not expected to be of general interest. For each indiv…EXPLAIN (DEBUG) EXPLAIN (RANGE_TABLE)Manual ChaptersPG18F.29.2. EXPLAIN (RANGE_TABLE) The RANGE_TABLE option displays information from the plan tree specifically concerning the query's range table. Range table entries correspond roughly to items …EXPLAIN (RANGE_TABLE) EXPLAIN ANALYZEManual ChaptersPG1814.1.2. EXPLAIN ANALYZE It is possible to check the accuracy of the planner's estimates by using EXPLAIN's ANALYZE option. With this option, EXPLAIN actually executes the query, and then dis…EXPLAIN ANALYZE EXPLAIN BasicsManual ChaptersPG1814.1.1. EXPLAIN Basics The structure of a query plan is a tree of plan nodes. Nodes at the bottom level of the tree are scan nodes: they return raw rows from a table. There are different typ…EXPLAIN Basics Explicit LockingManual ChaptersPG1813.3. Explicit Locking PostgreSQL provides various lock modes to control concurrent access to data in tables. These modes can be used for application-controlled locking in situations where M…Explicit Locking Explicit SubtransactionsManual ChaptersPG1844.7. Explicit Subtransactions Recovering from errors caused by database access as described in Section 44.6.2 can lead to an undesirable situation where some operations succeed before one o…Explicit Subtransactions Explicit Subtransactions in PL/TclManual ChaptersPG1842.9. Explicit Subtransactions in PL/Tcl Recovering from errors caused by database access as described in Section 42.8 can lead to an undesirable situation where some operations succeed befo…Explicit Subtransactions in PL/Tcl Exported SnapshotsManual ChaptersPG1847.2.5. Exported Snapshots When a new replication slot is created using the streaming replication interface (see CREATE_REPLICATION_SLOT), a snapshot is exported (see Section 9.28.5), which …Exported Snapshots Exporting a Large ObjectManual ChaptersPG1833.3.3. Exporting a Large Object To export a large object into an operating system file, call int lo_export(PGconn *conn, Oid lobjId, const char *filename); The lobjId argument specifies the…Exporting a Large Object Expression Evaluation RulesManual ChaptersPG184.2.14. Expression Evaluation Rules The order of evaluation of subexpressions is not defined. In particular, the inputs of an operator or function are not necessarily evaluated left-to-right…Expression Evaluation Rules Expression RestrictionsManual ChaptersPG1829.4.2. Expression Restrictions The WHERE clause allows only simple expressions. It cannot contain user-defined functions, operators, types, and collations, system column references or non-i…Expression Restrictions ExpressionsManual ChaptersPG1841.4. Expressions All expressions used in PL/pgSQL statements are processed using the server's main SQL executor. For example, when you write a PL/pgSQL statement like IF expression THEN ...…Expressions Extended QueryManual ChaptersPG1854.2.3. Extended Query The extended query protocol breaks down the above-described simple query protocol into multiple steps. The results of preparatory steps can be re-used multiple times f…Extended Query Extended Query OverviewManual ChaptersPG1854.1.2. Extended Query Overview In the extended-query protocol, execution of SQL commands is divided into multiple steps. The state retained between steps is represented by two types of obje…Extended Query Overview Extended StatisticsManual ChaptersPG1814.2.2. Extended Statistics It is common to see slow queries running bad execution plans because multiple columns used in the query clauses are correlated. The planner normally assumes that …Extended Statistics Extending SQLManual ChaptersPG18Chapter 36. Extending SQL In the sections that follow, we will discuss how you can extend the PostgreSQL SQL query language by adding: functions (starting in Section 36.3) aggregates (starti…Extending SQL ExtensibilityManual ChaptersPG1865.5.3. Extensibility The BRIN interface has a high level of abstraction, requiring the access method implementer only to implement the semantics of the data type being accessed. The BRIN la…Extensibility ExtensibilityManual ChaptersPG1865.4.3. Extensibility The GIN interface has a high level of abstraction, requiring the access method implementer only to implement the semantics of the data type being accessed. The GIN laye…Extensibility ExtensibilityManual ChaptersPG1865.2.3. Extensibility Traditionally, implementing a new index access method meant a lot of difficult work. It was necessary to understand the inner workings of the database, such as the lock…Extensibility ExtensibilityManual ChaptersPG1830.4. ExtensibilityExtensibility ExtensibilityManual ChaptersPG1865.3.3. Extensibility SP-GiST offers an interface with a high level of abstraction, requiring the access method developer to implement only methods specific to a given data type. The SP-GiST…Extensibility Extension Building InfrastructureManual ChaptersPG1836.18. Extension Building Infrastructure If you are thinking about distributing your PostgreSQL extension modules, setting up a portable build system for them can be fairly difficult. Theref…Extension Building Infrastructure Extension Configuration TablesManual ChaptersPG1836.17.3. Extension Configuration Tables Some extensions include configuration tables, which contain data that might be added or changed by the user after installation of the extension. Ordin…Extension Configuration Tables Extension ExampleManual ChaptersPG1836.17.7. Extension Example Here is a complete example of an SQL-only extension, a two-element composite type that can store any type of value in its slots, which are named “k” and “v”. Non-t…Extension Example Extension FilesManual ChaptersPG1836.17.1. Extension Files The CREATE EXTENSION command relies on a control file for each extension, which must be named the same as the extension with a suffix of .control, and must be placed…Extension Files Extension RelocatabilityManual ChaptersPG1836.17.2. Extension Relocatability Users often wish to load the objects contained in an extension into a different schema than the extension's author had in mind. There are three supported le…Extension Relocatability Extension UpdatesManual ChaptersPG1836.17.4. Extension Updates One advantage of the extension mechanism is that it provides convenient ways to manage updates to the SQL commands that define an extension's objects. This is done…Extension Updates ExtensionsManual ChaptersPG18H.4. Extensions PostgreSQL is designed to be easily extensible. For this reason, extensions loaded into the database can function just like features that are built in. The contrib/ directory…Extensions External ProjectsManual ChaptersPG18Appendix H. External Projects PostgreSQL is a complex software project, and managing the project is difficult. We have found that many enhancements to PostgreSQL can be more efficiently deve…External Projects External References for ICUManual ChaptersPG1823.2.3.5. External References for ICU This section (Section 23.2.3) is only a brief overview of ICU behavior and language tags. Refer to the following documents for technical details, additi…External References for ICU External ResourcesManual ChaptersPG18F.40.8. External Resources SE-PostgreSQL Introduction This wiki page provides a brief overview, security design, architecture, administration and upcoming features. SELinux User's and Admini…External Resources Extra TestsManual ChaptersPG1831.1.6. Extra Tests The core regression test suite contains a few test files that are not run by default, because they might be platform-dependent or take a very long time to run. You can ru…Extra Tests EXTRACT, date_partManual ChaptersPG189.9.1. EXTRACT, date_part EXTRACT(field FROM source) The extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamp,…EXTRACT, date_part FailoverManual ChaptersPG1826.3. Failover If the primary server fails then the standby server should begin failover procedures. If the standby server fails then no failover need take place. If the standby server can b…Failover Failures and Serialization/Deadlock RetriesManual ChaptersPG18Failures and Serialization/Deadlock Retries When executing pgbench, there are three main types of errors: Errors of the main program. They are the most serious and always result in an immedi…Failures and Serialization/Deadlock Retries FDW Options of postgres_fdwManual ChaptersPG18F.38.1. FDW Options of postgres_fdwFDW Options of postgres_fdw FDW Routines for ANALYZEManual ChaptersPG1858.2.8. FDW Routines for ANALYZE bool AnalyzeForeignTable(Relation relation, AcquireSampleRowsFunc *func, BlockNumber *totalpages); This function is called when ANALYZE is executed on a fore…FDW Routines for ANALYZE FDW Routines for Asynchronous ExecutionManual ChaptersPG1858.2.11. FDW Routines for Asynchronous Execution A ForeignScan node can, optionally, support asynchronous execution as described in src/backend/executor/README. The following functions are a…FDW Routines for Asynchronous Execution