Обсуждение: improving windows functions performance
Hey,
I have a very big query that consist from 3-4 subqueries that use windows functions. There is a chance that I'll need to rewrite the query but first I'm trying to search for other ways to improve it and I'll be happy to hear if one of u have an idea.
Basically my table has the following structure : (objid,first_num,last_num) and each record is a range from the first number to the last one for that specific obj. I'm trying to unite ranges that overlaps. For example :
for the following table :
objid first_num last_num
1 5 7
1 8 10
2 4 6
2 9 10
I would like to get :
objid first_num last_num
1 5 10
2 4 6
2 9 10
I have a query that does it but takes about 4s for 1.5M records. I created an index on (objid,first_num,last_num) in order to use only index scan instead of seq scan on this table. I wanted to here if u guys have any other ideas.
Thanks.
Am 05.08.19 um 22:47 schrieb Mariel Cherkassky: > Hey, > I have a very big query that consist from 3-4 subqueries that use > windows functions. There is a chance that I'll need to rewrite the > query but first I'm trying to search for other ways to improve it and > I'll be happy to hear if one of u have an idea. > > Basically my table has the following structure : > (objid,first_num,last_num) and each record is a range from the first > number to the last one for that specific obj. I'm trying to unite > ranges that overlaps. For example : > for the following table : > objid first_num last_num > 1 5 7 > 1 8 10 > 2 4 6 > 2 9 10 > > I would like to get : > objid first_num last_num > 1 5 10 > 2 4 6 > 2 9 10 > > I have a query that does it but takes about 4s for 1.5M records. I > created an index on (objid,first_num,last_num) in order to use only > index scan instead of seq scan on this table. I wanted to here if u > guys have any other ideas. > you should provide more information, for instance: * used version * table-structure * real query * execution plan (using explain analyse) Andreas -- 2ndQuadrant - The PostgreSQL Support Company. www.2ndQuadrant.com