Search PostgreSQL documentation

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 explain_uiExtensionsExtensionseasily jump into a visual plan UI for any SQL query pgrx nil-lic pg_explain_ui MonitoringMonitoring · Extensions explanationExtensionsExtensionsEXPLAIN plan parser that returns plan nodes as relational rows organized as a proximity tree. analyze explain explain-analyze node plan statistics table xml explanation MonitoringMonitoring · Extensions 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 exploring_aggregatesExtensionsExtensionspgrx example defining a custom integer-sum aggregate with serialized state. exploring_aggregates Functions and aggregatesFunctions and aggregates · Extensions 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 ext3ExtensionsExtensionsClientAuthentication hook example that logs authentication start and end events. ext3 SecuritySecurity · Extensions extendWait EventsReferenceextend Lock 10 Waiting to extend a relation. 11 12 13 14 15 16 17 18 19 20 9.6 Sub-second handoff during ordinary writes or planned DDL can be normal. Investigate once a user-facing wait bre…Wait Events · Lock 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 ExtensionWait EventsReferenceExtension 10 Waiting in an extension. 11 12 13 14 15 16 17 18 19 20 Normality depends on the exact extension, version, and operation documented by its author. Investigate when the custom nam…Wait Events · Extension 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 extension_control_pathConfigurationReferenceextension_control_path Client Connection Defaults 18 A path to search for extensions, specifically extension control files (name.control). The remaining extension script and secondary contro…Configuration · Client Connection Defaults extension_destdirConfigurationReferenceextension_destdir File Locations 10 11 12 13 14 15 16 17 9.5 9.6 The version matrix proves that extension_destdir is exposed by the measured Debian/PGDG binaries from PG9.5 through PG17. An …Configuration · File Locations extension_dropExtensionsExtensionsRun custom commands when an extension is dropped. extension extension development extensions extension_drop AdministrationAdministration · Extensions 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