How come DBeaver fetch is very fast?

I am using DBeaver Version 22.3.1 Connected to Postgres 13

DBeaver 'select' query fetch is very fast (returning 200 rows - refresh on scroll)

When I run SELECT * FROM table_a;

It is executed superfast. in 200ms on average. when total rows in table_a are 125mil+

but when I run SELECT count(*) FROM table_a;

It takes about 23 sec for 125mil+ rows.

Does anyone know what DBeaver does for it to have such superfast result (granted gives only 200 rows, but still impressive)?

---

PS: Sorry I am not very good at databases, still learning. but every other question about DBeaver and performance was negative (like why is it slow) but IMHO it is very fast.

I am doubting it's not actually DBeaver but Database itself. <or is there some sort database/application integration magic involved?>

Executing following query:

SELECT * FROM table_a LIMIT 200; is also quiet fast,

One more observation is it works very well with default ordering on primary key. when sorted on another column (indexed or non indexed), the performance goes very bad.

Just want to have an opinion/confirmation from someone who is expert in database or DBeaver.

Thank you

DBeaver limits the result set to 200 rows by default. That makes the first query fast, because it only has to read the first 200 rows. But the query that counts the rows has to fetch them all, so it takes much longer.

Moreover, the row limit of 200 actually slows down counting: without the row limit, PostgreSQL would use parallel workers to count the rows and be somewhat faster. But with the row limit, PostgreSQL does not use parallel query, because PostgreSQL cannot be certain that it can stop parallel processing after 200 result rows. So you get some overhead for the parallel infrastructure, and yet the query will run single-threaded.

Remove the row limit of 200, and everything will work as you would expect it.

See this article for details.