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.
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.