Database Reference
In-Depth Information
Suppose we want to get the row number of the current row within its partitioning
starting from 1 on the basis of the
year_created
column value. This can be done
using the following statement:
warehouse_db=# SELECT warehouse_id, year_created, warehouse_name,
row_number() OVER (PARTITION BY Year_created) FROM
warehouse_tbl;
warehouse_id | year_created | warehouse_name | row_number
--------------+--------------+----------------+------------
1 | 2013 | Mark Corp | 1
2 | 2013 | Bill & Co | 2
3 | 2013 | West point | 3
4 | 2014 | AB Corp | 1
5 | 2015 | Delta Time | 1
(5 rows)
Imagine that you have to maintain or build a SQL query for the preceding problem;
it will not only be dificult in creating or maintaining but will also be slower if
compared to the use of window functions.
Let's see the query plan for the preceding example as well:
QUERY PLAN
-------------------------------------------------------------
WindowAgg (cost=21.89..26.09 rows=240 width=110) (actual
time=6.813..11.179 rows=5 loops=1)
-> Sort (cost=21.89..22.49 rows=240 width=110) (actual
time=6.806..6.807 rows=5 loops=1)
Sort Key: w_ytd
Sort Method: quicksort Memory: 25kB
-> Seq Scan on warehouse (cost=0.00..12.40 rows=240
width=110) (actual time=0.035..0.039 rows=5 loops=1)
Total runtime: 11.265 ms
(6 rows)
Hints
PostgreSQL does not have
optimizer hints
, but there is another way to provide
allusions to the optimizer. We can enable or disable features according to our
requirement; if we don't want to utilize the sequential scan, then we can disable it
and the planner will use the next scan for that query. We can get the beneit of hints
by enabling and disabling the scans according to our need. This is an indirect way to
provide hints to the planner. Let's take a look at the following index scan example:
warehouse_db=# EXPLAIN SELECT * FROM record.history WHERE
history_id = 1000;
QUERY PLAN
