Database Reference
In-Depth Information
The factors the query planner has to take in consideration for an eficient plan
generation (step 3 described in preceding worklow) are as follows:
• Indexes
• Access methods (sequential scan, index scan, bitmap scan, index-only scan)
• Join types (nested loop joins, hash joins, sort-merge joins)
• Sort/aggregate/pipelining
Identical results can be achieved by utilizing different accumulations of the
preceding discussion, and it is evident that for a certain SQL query, there can be
numerous equipollent query plans and differences associated with runtime costs,
and among them, some equipollent plans can be huge.
Window functions
In Chapter 5 , Window Functions , we discussed the detailed utilization of window
functions. So, we won't be reiterating them here. As verbally expressed, "They are
utilized for calculation between multiple rows, which are somehow cognate to the
current query row." It can be related to the type of calculation achieved using the
aggregate functions. However, in contrast to aggregate functions, a window function
does not group rows into a single output row; rather, it retains their separate
identities. If you are in a situation where you either have to tune your complex
queries or create a custom function that has to calculate cumulative values or row
numbers, you should try considering window functions as the irst alternative that
will not only induce maintainability but eficiency as well.
Let's take an example of the warehouse_tbl table again in the following manner:
warehouse_db=# SELECT warehouse_id, year_created, warehouse_name
FROM warehouse_tbl;
warehouse_id | year_created | warehouse_name
--------------+--------------+----------------
1 | 2013 | Mark Corp
2 | 2013 | Bill & Co
3 | 2013 | West point
4 | 2014 | AB Corp
5 | 2015 | Delta Time
(5 rows)
 
Search WWH ::




Custom Search