Re: improving windows functions performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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






[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux