Moderators (requests and notices)

You are reading a single comment by @Velocio and its replies. Click here to read the full conversation.

•

cyclotron3k in reply to @Velocio
Maybe just whack a distinct on on the big union? (and update the order by clause appropriately)
```
SELECT DISTINCT ON (created, item_type_id, item_id), COUNT(*) OVER() AS total, ...
```
I haven't grokked the whole query so I could be way off.

•

Velocio in reply to @cyclotron3k

Oooh... I forgot all about SELECT DISTINCT ON despite having used it once in the past.

I think that would work.

•

itsbruce in reply to @Velocio

Oooh... I forgot all about SELECT DISTINCT ON despite having used it once in the past.

Did you forget or hit performance issues and drop the idea? DISTINCT can put a significant load on query execution. People often end up trying to use a combination of careful database design and GROUP BY to get better speed while still eliminating or minimising duplicates. In my experience, DISTINCT is most often useful with simpler, smaller datasets.

Proof is in the testing, ofc.
•

cyclotron3k in reply to @Velocio

What version of postgres is lfgss on? Pre 9.6 wasn't multithreaded, and pre 12, CTEs (and sub queries?) were basically optimisation fences.

If a DISTINCT ON causes performance issues, upgrading postgres could be a solution

About

Avatar for Velocio @Velocio started