Postgres Pro
Downloads
Home   >   mailing lists

Re: Bug: Very poor query optimization by Postgres - Mailing list pgsql-bugs

From Pavel Stehule
Subject Re: Bug: Very poor query optimization by Postgres
Date
Msg-id CAFj8pRBhV=aotd+GU=iOZ9CwBsfRysD4c_=nm+uY2n5aP=F=zg@mail.gmail.com
Whole thread Raw
In response to Bug: Very poor query optimization by Postgres  (Tobias Völk <tobias.voelk@t-online.de>)
List pgsql-bugs
Tree view
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
 

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

 

 

Attachment

pgsql-bugs by date:

Previous
From: Amit Langote
Date:
Subject: Re: posgres 12 bug (partitioned table)
Next
From: Etsuro Fujita
Date:
Subject: Re: BUG #16500: SQL Abend. select multi_key_columns_range_partition_table