From: | Andreas Kretschmer <andreas(at)a-kretschmer(dot)de> |
---|---|
To: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: improving windows functions performance |
Date: | 2019-08-05 21:15:36 |
Message-ID: | fa9e0ad8-cb18-0960-a7cb-ea1c01b03a6e@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Rosenstein | 2019-08-08 12:02:52 | Postgres not using correct indices for views. |
Previous Message | Mariel Cherkassky | 2019-08-05 20:47:44 | improving windows functions performance |