Window functions are often used in grouping and sorting operations to facilitate users to realize various grouping requirements. Because the window function usually needs to scan the data of the whole table and sort and aggregate, it consumes a lot of CPU resources, and the execution efficiency of the window function is low. The following describes an optimization case of window function.
1. Prepare examples
There is such a functional requirement. There is an information module in the system, which is used to publish some business-related activity dynamics. Each information has a type (such as information of science and technology, entertainment, military... Etc.) and browsing field. Some popular information lists need to be displayed on the official website (the larger the number of views, the more popular it is), and at most 3 relevant information records of each category are displayed. In other words, "group according to information classification, and take the first 3 information lists of each group". Table structure and initial data are as follows:
Create table info( id numeric not null primary key , title varchar(100) , Viewnum numeric , info_type_id numeric , Code text ); create index info_infotypeid on info (info_type_id); Create table info_type( Id numeric not null primary key, Name varchar(100) ); --Insert 100 news categories Insert into info_type select id, 'TYPE' || lpad(id::text, 5, '0' ) from generate_series(1, 100) id; --Insert 1000000 news Insert into info select id, 'TTL' || lpad(id::text, 20, '0' ) title, ceil(random()*1000000) Viewnum, ceil(random()*100) info_type_id , md5(id) code from generate_series(1, 1000000) id; vacuum analyse info_type,info;
2. Method 1: use window function
explain (analyse ,buffers )
with i as ( select i.*, row_number() over (partition by i.info_type_id order by i.viewnum desc) sn from info i)
select * from info_type t left join i on i.sn <= 3 and i.info_type_id = t.id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Hash Right Join (cost=211867.09..245279.17 rows=333333 width=97) (actual time=4223.126..6169.377 rows=300 loops=1)
Hash Cond: (i.info_type_id = t.id)
Buffers: shared hit=11582 read=1753, temp read=17860 written=17901
-> Subquery Scan on i (cost=211863.84..244363.84 rows=333333 width=82) (actual time=4223.080..6168.742 rows=300 loops=1)
Filter: (i.sn <= 3)
Rows Removed by Filter: 999700
Buffers: shared hit=11582 read=1752, temp read=17860 written=17901
-> WindowAgg (cost=211863.84..231863.84 rows=1000000 width=82) (actual time=4223.079..6080.518 rows=1000000 loops=1)
Buffers: shared hit=11582 read=1752, temp read=17860 written=17901
-> Sort (cost=211863.84..214363.84 rows=1000000 width=74) (actual time=4223.065..5224.438 rows=1000000 loops=1)
Sort Key: i_1.info_type_id, i_1.viewnum DESC
Sort Method: external merge Disk: 84128kB
Buffers: shared hit=11582 read=1752, temp read=17860 written=17901
-> Seq Scan on info i_1 (cost=0.00..23334.00 rows=1000000 width=74) (actual time=0.006..249.981 rows=1000000 loops=1)
Buffers: shared hit=11582 read=1752
-> Hash (cost=2.00..2.00 rows=100 width=15) (actual time=0.037..0.037 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
Buffers: shared read=1
-> Seq Scan on info_type t (cost=0.00..2.00 rows=100 width=15) (actual time=0.015..0.021 rows=100 loops=1)
Buffers: shared read=1
Planning Time: 0.328 ms
Execution Time: 6182.496 ms
(22 rows)
As you can see, the largest resource consumption here is in the sort operation. So, can we avoid the sort operation? Indexing avoids sort operations
3. Method 2: take only the record of the third place
Method 1: because a large number of data blocks are read, it takes too much time, and the function requires only one record in each group to be returned, hoping to avoid reading redundant data blocks. New SQL features, each type uses sub queries through the info table info_ type_ The index of ID column can avoid reading redundant data. As a calculated column, the sub query of select list can only return one value, so use row (i.*)::info to integrate first, and then use (inf). * to decompose. At the same time, use offset2 limit 1 to obtain a row of records in the third place.
explain (analyse ,buffers )
select id, name, (inf).*
from (select t.*,
(select row (i.*)::info
from info i
where i.info_type_id = t.id
order by i.viewnum desc
offset 2
limit 1) inf
from info_type t
) t;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on info_type t (cost=0.00..6708942.94 rows=100 width=361) (actual time=127.552..10513.868 rows=100 loops=1)
Buffers: shared hit=3544406 read=3255
SubPlan 1
-> Limit (cost=13417.88..13417.88 rows=1 width=38) (actual time=21.744..21.745 rows=1 loops=100)
Buffers: shared hit=706280 read=3252
-> Sort (cost=13417.87..13442.87 rows=10000 width=38) (actual time=21.740..21.740 rows=3 loops=100)
Sort Key: i.viewnum DESC
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=706280 read=3252
-> Bitmap Heap Scan on info i (cost=185.93..13288.63 rows=10000 width=38) (actual time=3.985..18.371 rows=10000 loops=100)
Recheck Cond: (info_type_id = t.id)
Heap Blocks: exact=706728
Buffers: shared hit=706280 read=3252
-> Bitmap Index Scan on info_infotypeid (cost=0.00..183.43 rows=10000 width=0) (actual time=2.615..2.615 rows=10000 loops=100)
Index Cond: (info_type_id = t.id)
Buffers: shared hit=1272 read=1532
SubPlan 2
-> Limit (cost=13417.88..13417.88 rows=1 width=38) (actual time=20.599..20.600 rows=1 loops=100)
Buffers: shared hit=709529 read=3
-> Sort (cost=13417.87..13442.87 rows=10000 width=38) (actual time=20.595..20.595 rows=3 loops=100)
Sort Key: i_1.viewnum DESC
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=709529 read=3
-> Bitmap Heap Scan on info i_1 (cost=185.93..13288.63 rows=10000 width=38) (actual time=3.640..17.373 rows=10000 loops=100)
Recheck Cond: (info_type_id = t.id)
Heap Blocks: exact=706728
Buffers: shared hit=709529 read=3
-> Bitmap Index Scan on info_infotypeid (cost=0.00..183.43 rows=10000 width=0) (actual time=2.291..2.291 rows=10000 loops=100)
Index Cond: (info_type_id = t.id)
Buffers: shared hit=2801 read=3
SubPlan 3
-> Limit (cost=13417.88..13417.88 rows=1 width=38) (actual time=21.284..21.285 rows=1 loops=100)
Buffers: shared hit=709532
-> Sort (cost=13417.87..13442.87 rows=10000 width=38) (actual time=21.279..21.279 rows=3 loops=100)
Sort Key: i_2.viewnum DESC
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=709532
-> Bitmap Heap Scan on info i_2 (cost=185.93..13288.63 rows=10000 width=38) (actual time=3.609..17.868 rows=10000 loops=100)
Recheck Cond: (info_type_id = t.id)
Heap Blocks: exact=706728
Buffers: shared hit=709532
-> Bitmap Index Scan on info_infotypeid (cost=0.00..183.43 rows=10000 width=0) (actual time=2.267..2.267 rows=10000 loops=100)
Index Cond: (info_type_id = t.id)
Buffers: shared hit=2804
SubPlan 4
-> Limit (cost=13417.88..13417.88 rows=1 width=38) (actual time=20.763..20.763 rows=1 loops=100)
Buffers: shared hit=709532
-> Sort (cost=13417.87..13442.87 rows=10000 width=38) (actual time=20.759..20.759 rows=3 loops=100)
Sort Key: i_3.viewnum DESC
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=709532
-> Bitmap Heap Scan on info i_3 (cost=185.93..13288.63 rows=10000 width=38) (actual time=3.769..17.505 rows=10000 loops=100)
Recheck Cond: (info_type_id = t.id)
Heap Blocks: exact=706728
Buffers: shared hit=709532
-> Bitmap Index Scan on info_infotypeid (cost=0.00..183.43 rows=10000 width=0) (actual time=2.390..2.390 rows=10000 loops=100)
Index Cond: (info_type_id = t.id)
Buffers: shared hit=2804
SubPlan 5
-> Limit (cost=13417.88..13417.88 rows=1 width=38) (actual time=20.713..20.713 rows=1 loops=100)
Buffers: shared hit=709532
-> Sort (cost=13417.87..13442.87 rows=10000 width=38) (actual time=20.709..20.709 rows=3 loops=100)
Sort Key: i_4.viewnum DESC
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=709532
-> Bitmap Heap Scan on info i_4 (cost=185.93..13288.63 rows=10000 width=38) (actual time=3.689..17.432 rows=10000 loops=100)
Recheck Cond: (info_type_id = t.id)
Heap Blocks: exact=706728
Buffers: shared hit=709532
-> Bitmap Index Scan on info_infotypeid (cost=0.00..183.43 rows=10000 width=0) (actual time=2.288..2.288 rows=10000 loops=100)
Index Cond: (info_type_id = t.id)
Buffers: shared hit=2804
Planning Time: 0.729 ms
Execution Time: 10514.326 ms
(74 rows)
Method 2: for info_ For each row of type, the info table should be based on info_type_ The ID index accesses the info table 5 times (5 columns). Total time consumption: 100 (rows) * 5 (columns) * 20 (about 20ms each time), about 10000ms
Execution plan analysis: according to info_type_id index, too many rows need to be accessed, and still need to be sorted. Based on these considerations, we can create an info_type_id + viewnum composite index reduces the time consumption per access and avoids sorting.
create index info_typeview on info(info_type_id,viewnum);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on info_type t (cost=0.00..4627.72 rows=100 width=361) (actual time=0.255..13.391 rows=100 loops=1)
Buffers: shared hit=2881 read=120
SubPlan 1
-> Limit (cost=6.31..9.25 rows=1 width=38) (actual time=0.041..0.041 rows=1 loops=100)
Buffers: shared hit=480 read=120
-> Index Scan Backward using info_typeview on info i (cost=0.42..29421.91 rows=10000 width=38) (actual time=0.034..0.040 rows=3 loops=100)
Index Cond: (info_type_id = t.id)
Buffers: shared hit=480 read=120
SubPlan 2
-> Limit (cost=6.31..9.25 rows=1 width=38) (actual time=0.022..0.022 rows=1 loops=100)
Buffers: shared hit=600
-> Index Scan Backward using info_typeview on info i_1 (cost=0.42..29421.91 rows=10000 width=38) (actual time=0.018..0.021 rows=3 loops=100)
Index Cond: (info_type_id = t.id)
Buffers: shared hit=600
SubPlan 3
-> Limit (cost=6.31..9.25 rows=1 width=38) (actual time=0.021..0.021 rows=1 loops=100)
Buffers: shared hit=600
-> Index Scan Backward using info_typeview on info i_2 (cost=0.42..29421.91 rows=10000 width=38) (actual time=0.018..0.020 rows=3 loops=100)
Index Cond: (info_type_id = t.id)
Buffers: shared hit=600
SubPlan 4
-> Limit (cost=6.31..9.25 rows=1 width=38) (actual time=0.021..0.021 rows=1 loops=100)
Buffers: shared hit=600
-> Index Scan Backward using info_typeview on info i_3 (cost=0.42..29421.91 rows=10000 width=38) (actual time=0.018..0.020 rows=3 loops=100)
Index Cond: (info_type_id = t.id)
Buffers: shared hit=600
SubPlan 5
-> Limit (cost=6.31..9.25 rows=1 width=38) (actual time=0.023..0.023 rows=1 loops=100)
Buffers: shared hit=600
-> Index Scan Backward using info_typeview on info i_4 (cost=0.42..29421.91 rows=10000 width=38) (actual time=0.020..0.022 rows=3 loops=100)
Index Cond: (info_type_id = t.id)
Buffers: shared hit=600
Planning Time: 0.730 ms
Execution Time: 13.552 ms
(34 rows)
It can be seen that after creating a new index, the single access is reduced from 20ms to 0.023ms, nearly 1000 times.
Problem: the number of returned rows is limited to only one row. At the same time, the info table has 5 columns, so there are 5 subplan s, of which 4 are redundant.
Next, modify the new SQL and new SQL features. The sub query of select list is used as the calculation column and can only return one row of value. Therefore, use array() to convert it into array type, and then use unnest() to decompose it into multiple rows and use it at the same time limit 3 get the top three three lines of records.
explain (analyse ,buffers )
select id, name, (inf).*
from (select t.id, t.name, unnest(inf) inf
from (select t.*,
array(select row (i.*)::info
from info i
where i.info_type_id = t.id
order by i.viewnum desc
limit 3) inf
from info_type t
) t) t;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on t (cost=0.00..942.89 rows=1000 width=361) (actual time=0.092..2.526 rows=300 loops=1)
Buffers: shared hit=601
-> ProjectSet (cost=0.00..932.89 rows=1000 width=47) (actual time=0.089..2.406 rows=300 loops=1)
Buffers: shared hit=601
-> Seq Scan on info_type t_1 (cost=0.00..2.00 rows=100 width=15) (actual time=0.008..0.020 rows=100 loops=1)
Buffers: shared hit=1
SubPlan 1
-> Limit (cost=0.42..9.25 rows=3 width=38) (actual time=0.018..0.021 rows=3 loops=100)
Buffers: shared hit=600
-> Index Scan Backward using info_typeview on info i (cost=0.42..29421.91 rows=10000 width=38) (actual time=0.017..0.020 rows=3 loops=100)
Index Cond: (info_type_id = t_1.id)
Buffers: shared hit=600
Planning Time: 0.295 ms
Execution Time: 2.639 ms
(14 rows)
4. Conclusion
1. The key point of the whole optimization is to create info_type_id + viewnum composite index, that is, the composite index of the partition by and order by columns of the window query.
2. The application of array is also the key, which solves the problem of returning multiple rows.