Re: Bug: Very poor query optimization by Postgres

Hi

út 7. 7. 2020 v 22:10 odesílatel Tobias Völk <tobias(dot)voelk(at)t-online(dot)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

> 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
>
>
>
>
>