Re: Is postgres able to share sorts required by common partition window functions?

Поиск
Список
Период
Сортировка
От Michael Lewis
Тема Re: Is postgres able to share sorts required by common partition window functions?
Дата
Msg-id CAHOFxGq_46XEYboKCv+8QX04ypw9OavkQ4oU0xv9HxcaYzHhbQ@mail.gmail.com
обсуждение исходный текст
Ответ на Is postgres able to share sorts required by common partition window functions?  (Sebastien Arod <sebastien.arod@gmail.com>)
Ответы Re: Is postgres able to share sorts required by common partition window functions?  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-general
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 you have an index on c1?

select
  c1,
  sub1.c2,
  sub2.c3
from  
  t
join lateral (select c2 from t1 where t1.c1 = t.c1 order by c2, c4 limit 1 ) as sub1 on true
join lateral (select c3 from t1 where t1.c1 = t.c1 order by coalesce(c4, '000'), c3 limit 1 ) as sub2 on true;


select
  c1,
  (select c2 from t1 where t1.c1 = t.c1 order by c2, c4 limit 1 ) AS c2,
  (select c3 from t1 where t1.c1 = t.c1 order by coalesce(c4, '000'), c3 limit 1 ) AS c3
from  
  t;

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.

(select c1 from t group by c1 ) AS t

В списке pgsql-general по дате отправления:

Предыдущее
От: David Rowley
Дата:
Сообщение: Re: Apply LIMIT when computation is logically irrelevant
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: Is postgres able to share sorts required by common partition window functions?