improving windows functions performance
Mariel Cherkassky <[email protected]>
| Newsgroups | gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <CA+t6e1ntypz5mLaR-5g_EKJKn0LKwzkjNHKb32ZyEDr1CYSJ9Q@mail.gmail.com> |
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.