improving windows functions performance

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.

Responses

Browse pgsql-performance by date

  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