What is query tree?

To understand however the rule system works it's necessary to understand once it's invoked and what its input and results square measure.

The rule system is found between the programme and also the planner. It takes the output of the programme, one question tree, and also the user-defined rewrite rules, that are question trees with some additional data, and creates zero or a lot of question trees as result. thus its input and output square measure forever things the programme itself may have created and so, something it sees is largely expressible as AN SQL statement.

Now what's a question tree? it's an indoor illustration of AN SQL statement wherever the one elements that it's designed from square measure hold on severally. These question trees may be shown within the server log if you set the configuration parameters debug_print_parse, debug_print_rewritten, or debug_print_plan. The rule actions are hold on as question trees, within the system catalog pg_rewrite. they're not formatted just like the log output, however they contain precisely the same data.

Reading a raw question tree needs some expertise. however since SQL representations of question trees square measure enough to grasp the rule system, this chapter won't teach a way to browse them.

When reading the SQL representations of the question trees during this chapter it's necessary to be able to establish the elements the statement is broken into once it's within the question tree structure. The elements of a question tree square measure

the command sort

This is an easy price telling that command (SELECT, INSERT, UPDATE, DELETE) created the question tree.

the vary table

The vary table may be a list of relations that square measure employed in the question. in a very choose statement these square measure the relations given once the FROM key Word.

Every vary table entry identifies a table or read and tells by that name it's known as within the alternative elements of the question. within the question tree, the vary table entries square measure documented by range instead of by name, thus here it does not matter if there square measure duplicate names because it would in AN SQL statement. this will happen once the vary tables of rules are incorporate in. The examples during this chapter won't have this case.

the result relation

This is AN index into the vary table that identifies the relation wherever the results of the question go.

SELECT queries do not have a result relation. (The special case of choose INTO is usually similar to produce TABLE followed by INSERT ... SELECT, and isn't mentioned severally here.)

For INSERT, UPDATE, and DELETE commands, the result relation is that the table (or view!) wherever the changes square measure to require result.

the target list

The target list may be a list of expressions that outline the results of the question. within the case of a get, these expressions square measure those that build the ultimate output of the question. They correspond to the expressions between the key Words choose and FROM. (* is simply AN abbreviation for all the column names of a relation. it's enlarged by the programme into the individual columns, that the rule system ne'er sees it.)

DELETE commands do not would like a standard target list as a result of they do not manufacture any result. Instead, the rule system adds a special CTID entry to the empty target list, to permit the fiduciary to search out the row to be deleted. (CTID is additional once the result relation is a normal table. If it's a read, a whole-row variable is additional instead, as represented in Section thirty eight.2.4.)

For INSERT commands, the target list describes the new rows that ought to get in the result relation. It consists of the expressions within the VALUES clause or those from the choose clause in INSERT ... SELECT. the primary step of the rewrite method adds target list entries for any columns that weren't allotted to by the first command however have defaults. Any remaining columns (with neither a given price nor a default) are stuffed in by the planner with a relentless null expression.

For UPDATE commands, the target list describes the new rows that ought to replace the previous ones. within the rule system, it contains simply the expressions from the SET column = expression a part of the command. The planner can handle missing columns by inserting expressions that replicate the values from the previous row into the new one. even as for DELETE, the rule system adds a CTID or whole-row variable so the fiduciary will establish the previous row to be updated.

Every entry within the target list contains AN expression which will be a relentless price, a variable inform to a column of 1 of the relations within the vary table, a parameter, or AN expression tree fabricated from perform calls, constants, variables, operators, etc.

the qualification

The query's qualification is AN expression very like one amongst those contained within the target list entries. The result price of this expression may be a mathematician that tells whether or not the operation (INSERT, UPDATE, DELETE, or SELECT) for the ultimate result row ought to be dead or not. It corresponds to the wherever clause of AN SQL statement.

the be a part of tree

The query's be a part of tree shows the structure of the FROM clause. For an easy question like choose ... FROM a, b, c, the be a part of tree is simply an inventory of the FROM things, as a result of we tend to square measure allowed to hitch them in any order. however once be a part of expressions, significantly outer joins, are used, we've got to hitch within the order shown by the joins. in this case, the be a part of tree shows the structure of the be a part of expressions. The restrictions related to explicit be a part of clauses (from ON or exploitation expressions) square measure hold on as qualification expressions connected to those join-tree nodes. It seems to be convenient to store the ranking wherever expression as a qualification connected to the ranking join-tree item, too. thus extremely the be a part of tree represents each the FROM and wherever clauses of a get.

the others

The other elements of the question tree just like the ORDER BY clause are not of interest here. The rule system substitutes some entries there whereas applying rules, however that does not have abundant to try to to with the basics of the rule system.