Database Select cost too high

Question

I want to use a double INNER SELECT on my database. If I use it via HIBERNATE and entities in my java application, the debugger gets an GC error.

  SELECT * FROM campaign WHERE id_campaign  IN 
    (SELECT id_campaign FROM event WHERE id_event IN 
     (SELECT id_event FROM dataset_event WHERE id_dataset=xxx)) limit 100;

Is there a better way with no GC error? Maybe a different select? Does Hibernate fills the event-entities of the second inner select? It is not necessary i guess..

FYI:

Error in console:

26-Feb-2020 09:36:36.575 SCHWERWIEGEND [ContainerBackgroundProcessor[StandardEngine[Catalina]]] org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run Unexpected death of background thread [ContainerBackgroundProcessor[StandardEngine[Catalina]]]
    java.lang.OutOfMemoryError: GC overhead limit exceeded
Exception in thread "ContainerBackgroundProcessor[StandardEngine[Catalina]]" java.lang.OutOfMemoryError: GC overhead limit exceeded

Hibernate version is 5.3.3.final

https://stackoverflow.com/questions/1239723/how-do-you-do-a-limit-query-in-hql — JGFMK, Feb 26 '20 at 08:02
I use `query.setFirstResult(offset).setMaxResults(limit).getResultList(); `later in my code. — Dennis, Feb 26 '20 at 08:06
`debugger gets an GC error` - Could you please provide the full error message? What hibernate version, database do you use? — SternK, Feb 26 '20 at 08:27
The OutOfMemoryError has nothing to do with the (very small) cost shown by the explain output. The database expects only 5 rows from that query, so it's really strange that you get an OOME - how many rows does the query really return? `explain (analyze)` would show that. — , Feb 26 '20 at 08:45
The execution plan is better shown as [formatted text](http://stackoverflow.com/help/formatting). To do that, paste the plan as text into your question, then put `\`\`\`` on the line before the plan and on a line after the plan to make sure you preserve the indention. — , Feb 26 '20 at 08:46
The screen shot is hard to read ( formatted text would have been better). But as far as I can tell, the query returns **a single row**. Which means you most probably have several really gigantic columns (e.g. multiple JSON and text values) in that table. Otherwise the OOME can't be explained. — , Feb 26 '20 at 11:39
Hey, yeah it returns a single row. Maybe there is a problem with hibernate, cause if fills up the inner inner select in his entities ( like 288 entities to fill ). — Dennis, Feb 27 '20 at 06:20

score 0 · Answer 1 · answered Feb 26 '20 at 08:35

0

I think to use this query is better than the above query:

  SELECT * 
  FROM campaign  AS c
  OUTER APPLY(
     SELECT id_campaign FROM event AS e WHERE e.id_campaign = c.id_campaign
             ) AS p
  OUTER APPLY(
     SELECT id_event FROM dataset_event AS d WHERE id_dataset=xxx AND d.id_event = 
     p.id_event
             ) AS o

answered Feb 26 '20 at 08:35

Ehsan Hemati

13
3

ERROR: syntax error at or near "OUTER" LINE 5: OUTER APPLY( ^ SQL state: 42601 Character: 54 – Dennis Feb 26 '20 at 08:44
`outer apply` is non-standard SQL. The equivalent standard SQL operator would be (I think) `left join lateral` – Feb 26 '20 at 08:47
ERROR: syntax error at end of input LINE 10: p.id_event ) AS o – Dennis Feb 26 '20 at 11:32

score 0 · Accepted Answer · answered Feb 26 '20 at 12:58

This probably does what you want:

SELECT c.*
FROM campaign c JOIN
     event e
     ON c.id_campaign = e.id_campaign JOIN
     dataset_event de
     ON de.id_event = e.id_event
WHERE de.id_dataset = xxx
LIMIT 100;

For performance, you want indexes on:

dataset_event(id_dataset, id_event)
event(id_event, id_campaign)
campaign(id_campaign)

You may already have some or all of these.

The above might return duplicates. Obviously, you could use select distinct or distinct on, but that could be quite expensive. Instead, exists might be a better solution:

SELECT c.*
FROM campaign c 
WHERE EXISTS (SELECT 1
              FROM event e JOIN
                   dataset_event de
                   ON de.id_event = e.id_event
              WHERE c.id_campaign = e.id_campaign AND
                    de.id_dataset = xxx
             )
LIMIT 100;

For this, you want indexes on:

event(id_campaign, id_event)
dataset_event(id_event, id_dataset)

are both solutions hibernate conform? I guess the real problem is, that my inner inner select returns 288 rows and hibernate wants to match them into the entities. — Dennis, Feb 27 '20 at 06:21
@Dennis . . . I don't know. Both are compatible with Postgres. — Gordon Linoff, Feb 27 '20 at 12:11

Database Select cost too high

2 Answers2