Diagnosing Slow Query Result Display in DBeaver

Recognizable condition

You run a SELECT statement in DBeaver’s SQL editor. The query finishes quickly in the database’s native CLI (or in EXPLAIN ANALYZE), but the DBeaver UI freezes, the data grid takes minutes to fill, or you see a “Not Responding” hint while the result set is being populated.

Cause / diagnostic table

Possible cause	What it looks like
Small JDBC fetch size → many round‑trips	Query returns quickly on server, but each fetch brings only a few rows; total time grows with row count.
Automatic row‑count (COUNT(*)) for pagination	DBeaver issues an extra COUNT(*) before the main query; on large tables this can double the work.
Heavy metadata loading (object browser, autocomplete)	UI stalls when expanding schemas or invoking content assist; many catalog queries are seen in the log.
Network latency amplifying per‑fetch overhead	Same fetch size works fine on localhost but is slow over VPN or WAN.
Heap pressure when DBeaver buffers too many rows	UI becomes sluggish, GC spikes, or OutOfMemoryError appears in the log.

Ordered checks

1. Cap the result set locally. Rerun the same statement with LIMIT 1000 or enable DBeaver’s “Result set size” guard (Preferences → Data Editors → Max rows). If execution becomes fast, the bottleneck is client‑side fetch/render, not the query plan.
2. Compare server‑side vs wall‑clock time. Open the database’s activity view (e.g., pg_stat_activity, V$SESSION) or run EXPLAIN ANALYZE to get the server execution time. Note the duration shown in DBeaver’s Query Manager. A large gap (>2×) indicates client‑side transfer or rendering overhead.
3. Inspect and adjust fetch size. In the connection editor → Properties tab → Result set (or Driver → Advanced → Fetch size). Note the current value (often 100–500). Increase it to a few thousand (e.g., 5000) and re‑run the uncapped query. Improvement confirms that round‑trips were the issue.
4. Disable auto row‑count and heavy metadata. In the same connection editor, turn off “Read row count” or set Row count mode to “Unknown”. Also disable autocomplete for large catalogs (Preferences → Editor → Code Completion → Enable only for selected schemas). If the UI becomes responsive, the extra COUNT(*) or catalog queries were the cause.

Fixes mapped to findings

• Increase fetch size – reduces round‑trips. Keep an eye on memory; if you see frequent GC or heap warnings, lower the size or increase DBeaver’s heap (-Xmx in etc/dbeaver.ini).
• Cap result sets for exploratory work – use LIMIT or the built‑in row limit to avoid loading millions of rows into the grid.
• Turn off automatic row count – set “Row count mode” to “Unknown” or disable the feature; the main query runs without the preceding COUNT(*).
• Filter the database navigator – right‑click a schema → Navigate → Filter Objects to limit autocomplete and metadata queries to the objects you actually need.
• Raise DBeaver heap – edit etc/dbeaver.ini (or the platform‑specific vmoptions file) and increase -Xmx2g to -Xmx4g if you regularly work with large result sets.

Escalation criteria

• If the server‑side execution time itself is high (comparable to the wall‑clock time in DBeaver), the problem lies in the database (missing indexes, locks, inefficient plan). Hand the EXPLAIN output to the DBA.
• If DBeaver freezes even on trivial queries (e.g., SELECT 1) or you observe thread‑dump‑worthy stalls, collect Help → Collect Debug Information and file a ticket in the DBeaver issue tracker.

Verification steps

1. Pick a known large table (e.g., SELECT * FROM sales WHERE sale_date > '2020-01-01'). Run it in the database CLI and note the elapsed time.
2. Run the same statement in DBeaver, open the Query Manager, and record the “Duration”.
3. Toggle the fetch size (e.g., from 200 to 5000) and repeat step 2; observe whether the duration moves closer to the CLI time.
4. Disable “Read row count” and repeat; check whether the extra COUNT(*) disappears from the log (Window → Show View → DBeaver Log).

These steps let you confirm whether the observed slowness is due to client‑side fetch overhead, row‑count overhead, or metadata loading, and guide you to the appropriate fix without guessing.