Re: improving windows functions performance

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