From: | Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | improving windows functions performance |
Date: | 2019-08-05 20:47:44 |
Message-ID: | CA+t6e1ntypz5mLaR-5g_EKJKn0LKwzkjNHKb32ZyEDr1CYSJ9Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-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.
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2019-08-05 21:15:36 | Re: improving windows functions performance |
Previous Message | Thomas Kellerer | 2019-08-05 07:29:33 | Re: Strange runtime partition pruning behaviour with 11.4 |