blackberryvilla.blogg.se - Postgres query plan

#POSTGRES QUERY PLAN HOW TO#

Postgres=# INSERT INTO demotable SELECT random() * 1000, generate_series(1, 10000) postgres=# CREATE TABLE demotable (num numeric, id int) In this case, Sort node can be on top of whole data retrieval including all other processing.

The user scenario requires sorted data as output. Sorted data can be required explicitly or implicitly like below two cases: SortĪs the name suggests, this node is added as part of a plan tree whenever there is a need for sorted data. Let’s understand each one of these nodes. Some of the auxiliary nodes generated by the PostgreSQL query optimizer are as below: So here one auxiliary node “Sort” is added on top of the result of join to sort the data in the required order. Suppose a plan generated corresponding to the query as below: Sorting the data, aggregate of data, etc.Ĭonsider a simple query example such as… SELECT * FROM TBL1, TBL2 where TBL1.ID > TBL2.ID order by TBL.ID The nodes in this category are applied on top of data retrieved in order to further analyze or prepare report, etc e.g.

#POSTGRES QUERY PLAN HOW TO#

The previous two kinds of nodes were related to how to fetch data from a base table and how to join data retrieved from two tables.

Materialization Nodes: Also called as Auxiliary nodes.

Join Nodes: As explained in my previous blog “ An Overview of the JOIN Methods in PostgreSQL”, it indicates how two tables need to be joined together to get the result of two tables.

Scan Nodes: As explained in my previous blog “ An Overview of the Various Scan Methods in PostgreSQL”, it indicates the way a base table data needs to be fetched.

The plan tree nodes can be majorly divided into the following 3 categories: The plan is represented in the form of a tree output from the Query Optimizer. The efficient strategy is called “Plan” and it is measured in terms of cost which is directly proportional to “Query Execution/Response Time”. All modern database system supports a Query Optimizer module to automatically identify the most efficient strategy for executing the SQL queries.