PostgreSQL Distinct Sort For Huge Amount of Data

Question

Here my query is:

explain(buffers, analyze) SELECT DISTINCT e.eventid, e.objectid, e.clock, e.ns, e.name, e.severity
FROM EVENTS e, functions f, items i, hosts_groups hg
WHERE e.source='0'  AND e.object='0' AND NOT EXISTS
    (SELECT NULL  FROM functions f, items i, hosts_groups hgg
     LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN (12, 13, 14, ...)
     WHERE e.objectid=f.triggerid AND f.itemid=i.itemid  AND i.hostid=hgg.hostid
     GROUP BY i.hostid HAVING MAX(permission)<2  OR MIN(permission) IS NULL OR MIN(permission)=0)
  AND e.objectid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hg.hostid
  AND hg.groupid IN (1, 2, 3, ...)
  AND e.value=1
ORDER BY e.eventid DESC;

You can find the related execution plan here.

As you can see, it spills to the disk. Because default value of work_mem is 8 MB. Than, I set work_mem to 1 GB on my session to see difference and run the query again. The new execution plans is here. Now, it is doing quicksort but still, the execution time is 779213.763 ms.

This query is a auto - generated query by a third party tool but we can change it I assume.

Doing distinct - sort for ~602k rows is insane. That is why I want to add more filter for clock column. Yet, I want to ask is there any other options to decrease execution time of this query?

Specifications for database server:

$ lscpu
CPU(s):              8
On-line CPU(s) list: 0-7
Thread(s) per core:  1
Core(s) per socket:  4
Socket(s):           2
NUMA node(s):        1
Memory: 96 GB

The database settings for:

 max_parallel_workers_per_gather
---------------------------------
 4

 max_worker_processes
----------------------
 16

 max_parallel_workers
----------------------
 16

Thanks!

The sort itself consumes a trivial a amount of time--1.4 seconds. It is waiting for its input to arrive that takes the time. — jjanes, Aug 01 '22 at 18:45
The NOT EXISTS doesn't seem to remove any rows. Is that whole thing actually needed? — jjanes, Aug 01 '22 at 19:03
You should repeat the test with track_io_timing turned on. (I would just turn it on globally and leave it on) — jjanes, Aug 01 '22 at 19:05
I had focused on the wrong point :S. No, objectid is not unique. Actually, all of them is not needed and that is why we are thinking of adding more filter but, I am searching ways of that is there any other way to optimize this version of the query. Thanks! — Umut TEKİN, Aug 02 '22 at 07:25

jjanes · Accepted Answer · 2022-08-04T02:17:53.270

1

It looks like the core of the problem is that the planner is not using a hashed subplan (where it runs it in bulk once and memorizes the results in a hash) for the NOT EXISTS, but rather is running it parameterized for each tuple in a loop. Usually this is because the planner thinks it will take too much memory to hash the results, but in this case I think it is just because it can not figure out how to analyze GROUP BY...HAVING.

You can guide it down the (presumably) correct path here by replacing the NOT EXISTS (...) with:

 AND e.objectid NOT IN (
   SELECT triggerid FROM functions f, items i, hosts_groups hgg
   LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN (12, 13, 14 /*...*/)
   WHERE f.itemid=i.itemid  AND i.hostid=hgg.hostid
   GROUP BY triggerid, i.hostid HAVING MAX(permission)<2  OR MIN(permission) IS NULL OR MIN(permission)=0
 )

But before trying this, I might run just the inner query there by itself to see how long it takes and how many rows it returns.

If this ends up working, it might be worthwhile to investigate what it would take to make the planner smart enough to do this conversion on its own.

edited Aug 04 '22 at 02:17

answered Aug 04 '22 at 01:53

jjanes

37,812
5
27
34

Thank you, it made the difference but I think it is still needed to add more filter. The execution plan is changed to [this](https://explain.dalibo.com/plan/Bqu). It still needs more than 250 MB for work_mem. I don' t think it is a good idea to set it. – Umut TEKİN Aug 04 '22 at 08:41
@UmutTEKİN Your new plan features a LIMIT node, which your old ones did not, so they must be for different queries (different in a way other than the way we already know about). A query with a LIMIT will have different optimizations available--ones that will likely use less memory. If you *want* the query to be LIMITed, you should start over with plans for your original query but with the LIMIT added. – jjanes Aug 04 '22 at 22:55
Yeah, you are right. It was slightly different. I just executed the first one and here is the [plan](https://explain.dalibo.com/plan/cIq) of it. – Umut TEKİN Aug 05 '22 at 11:20
When we get rid of correlated subquery we eliminated nested loops (loops=3333361) instead we get sequential scan(loops=5). Another question, is there any possibilty to know this way (using `NOT IN` cluase instead of `NOT EXISTS`) is better or we have to try and see and compare execution plans? – Umut TEKİN Aug 05 '22 at 11:29
Starting from your advise I searched a little bit and found [reference1](https://dba.stackexchange.com/questions/121034/best-practice-between-using-left-join-or-not-exists) and [reference2](https://stackoverflow.com/questions/7082449/exists-vs-join-and-use-of-exists-clause). Manipulated the query a little bit and the fastest plan is [here](https://explain.dalibo.com/plan/GQr)(The query is also on the link). Still needs `work_mem` more than `250 MB` but I learnt how to deal with it. Thanks @jjanes. – Umut TEKİN Aug 05 '22 at 14:59

PostgreSQL Distinct Sort For Huge Amount of Data

1 Answers1