On Monday, July 6, 2020, Michael Lewis wrote:
> Did you say you have an index on c1?
> [...]
> I don't know the data, but I assume there may be many rows with the same
> c1 value, so then you would likely benefit from getting that distinct set
> first like below as your FROM table.
>
>
Michael, David thanks for your quick replies.
*@Michael*
I initially dismissed writing this query using joins or subselects because
the real query has about 80 columns and I was afraid that having 80
joins/subselect would cause issues with postgresql including planner that
would fallback to
Hi Michael,
I simplified the real query before posting it here and I now realize that I
oversimplified things.
Unfortunately the real query cannot be re-written with a group by.
Some of the window functions are more complex with order by clause using
complex expressions involving multiple
On Monday, July 6, 2020, Sebastien Arod wrote:
> I would have expected postgresql to "share" a preliminary sort on c1 that
> would then be useful to reduce the work on all window functions but it
> doesn't.
>
The plan shown does share - the output of one sort goes into another.
Subsequent sorts
On Monday, July 6, 2020, Michael Lewis wrote:
> Did you say you have an index on c1?
> [...]
> I don't know the data, but I assume there may be many rows with the same
> c1 value, so then you would likely benefit from getting that distinct set
> first like below as your FROM table.
>
Re-reading
Distinct is a great way to get quick results when writing quick &
dirty queries, but I rarely have them perform better than a re-write that
avoids the need. It collects a ton of results, orders them, and throws away
duplicates in the process. I don't love the idea of that extra work. Did
you say
Does this give the same result and do the optimization you want?
select
c1,
min(c2) AS c2,
min(c3) AS c3,
min(c4) AS c4
from
t
group by
c1;
>
Hi all,
I'm trying to optimize the following query on postgres 11.6 (running on
Aurora)
select distinct
c1,
first_value(c2) OVER (PARTITION BY c1 order by c2) AS c2,
first_value(c3) OVER (PARTITION BY c1 order by c3) AS c3,
first_value(c4) OVER (PARTITION BY c1 order by c4) AS c4
from
Hi Everyone,
I have a partitioned by period table scenario here where I need to
execute a query with a function in where clause.
I'm not sure if this is the best approach to do that, but when I use the
functions, it scans all the tables instead only the desired one. If I put
the parameter
