Bug: Very poor query optimization by Postgres
Hello dear Postgres-Community,
First of all I’d like to thank you for providing and developing this amazingly fast database software for free!
Now to the bug:
I’ve installed the Postgres 12 Server for Windows 10 using the installer and created the tables using Java and JDBC.
The bug is that when I execute the query “select count(*) from table” in pgAdmin in the browser or through the PostgreSQL Viewer app from the Playstore, it takes a little while which is already strange and explain indeed tells me,
that Postgres is performing a sequential scan with 2 workers instead of just returning the number or rows!
This is very poor query optimization but very easy to fix on the other hand! As well as beneficial, since this kind
of query is used very often (also as a subquery).
Below is a screenshot of pgAdmin4’s output.
Regards Tobi
Attachments:
On Tuesday, July 7, 2020, Tobias Völk <tobias.voelk@t-online.de> wrote:
that Postgres is performing a sequential scan with 2 workers instead of
just returning the number or rows!
https://wiki.postgresql.org/wiki/Index-only_scans
David J.
Hi
út 7. 7. 2020 v 22:10 odesílatel Tobias Völk <tobias.voelk@t-online.de>
napsal:
Hello dear Postgres-Community,
First of all I’d like to thank you for providing and developing this
amazingly fast database software for free!Now to the bug:
I’ve installed the Postgres 12 Server for Windows 10 using the installer
and created the tables using Java and JDBC.The bug is that when I execute the query “select count(*) from table” in
pgAdmin in the browser or through the PostgreSQL Viewer app from the
Playstore, it takes a little while which is already strange and explain
indeed tells me,that Postgres is performing a sequential scan with 2 workers instead of
just returning the number or rows!
It is not a bug - and this is expected behavior.
a) Postgres doesn't store numbers of rows of tables - so any time the table
has to be scanned
b) Any user can have different results at the same moment - due different
row visibility rules for different transaction isolation levels - it is a
reason why postgres doesn't store numbers of rows inside the table.
Regards
Pavel
Show quoted text
This is very poor query optimization but very easy to fix on the other
hand! As well as beneficial, since this kindof query is used very often (also as a subquery).
Below is a screenshot of pgAdmin4’s output.
Regards Tobi