Re: BUG #13852: SQL Select Slow Issues

On Wed, Jan 6, 2016 at 9:42 PM, <eugeneymail(at)ymail(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 13852
> Logged by: Eugene
> Email address: eugeneymail(at)ymail(dot)com
> PostgreSQL version: 9.4.5
> Operating system: Linux
> Description:
>
> I have been a long time Oracle user. Currently I am thinking to switch to
> PostgreSQL. So I did a lot searches/researches on this product.
>
> The general and major complains I can summarize is the "SELECT" statement
> returns results too slow, as compared to the commercialized products such
> as
> Oracle and MS SQL Server.
>

Hello,

The problem with this statement is that there is no "*the* SELECT
statement". Any such complaints or comparison benchmarks need to provide
extensive evidence in order to be taken seriously (see below for detailed
comments on the links you've collected).

So I would like to submit some suggestions:
>
> 1) During the installation process, use a GUI window to let the user
> choose:
>
> (a) For OLTP use
>
> (b) For General Purpose
>
> (c) Customized Installation
>

For that purpose you might want to try something like pgtune, a
configuration parameters generator: http://pgtune.leopard.in.ua/

For type (a) installation, automatically use the preset and optimised
> paramters targeted at the OLTP system;
>
> For type (b) installation, automatically use the preset and optimised
> paramters targeted at the general system;
>
> For type (c) installation, automatically use the preset paramters just like
> those in releases, it will up to the user to tune those parameters lateron,
> by themselves;
>
> Otherwise, it will create challenges to users in their installation of
> PostgresSQL. This is because we do not know which parameters to set or
> adjust, there could be hundres if not thousands componations of them. If I
> want to set up an OLAP system on PostgresSQL, I will not know which
> parameters to choose and set to achieve reasonably better performance. In
> the case of Oracle, the thing is different. Quite frankly, it is a no
> brainer that if I want the database to be used for an OLTP system, I just
> choose OLTP, the parameters underneath have already been preset and
> opertimised by Oracle.
>

IMO it should be expected that tuning such a complex system as a DBMS is
not an easy task, however the official documentation is very extensive on
the topic, most notably:
http://www.postgresql.org/docs/current/static/runtime-config-resource.html

But there is no single "one size fits all" parameter that you could turn to
the maximum to get maximum performance out of your hardware. And proper
selection and tuning of hardware is a separate topic, on which entire books
were written I believe. A good overview of resources is available in
PostgreSQL wiki, for example:
https://wiki.postgresql.org/wiki/Performance_Optimization

2) The speed issue.
> I know the features are important and in every release, new features are
> introduced. Those are good. But in my opinion, the far most important
> thing is to increase the speed. More often than not people view a better
> DBMS product is not because it have this or that fancy feature but the
> speed. Yes the speed that wins the day. The bells and whistles look nice
> and shining. But without the tree, they are just some shining pieces of
> glass or metal. As the users, we want to tree!
>

Be assured that PostgreSQL community takes performance issues seriously.
In every single release a number of improvements are dedicated for better
performance, be it new features or upgrades to already existing ones. For
an overview, please check the release announcements and "What's new" wiki
pages for the recent major versions (not to mention the newest major
version 9.5 which is due to be released today):

https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.5
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.4
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.2

The following I list some links of the sites which complains the sluggish of
> the PostgreSQL for future reference:
>
> Starting with the quote from
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
> “In any given week, some 50% of the questions on #postgresql IRC and 75% on
> pgsql-performance are requests for help with a slow query.”
>

Which is something to be expected also, IMO, with any serious deployment of
any DBMS engine: everything is fine before you hit a query which is slow
for your current set of data. The reasons to that could vary from a
missing index, to out of date statistics, to insufficient work_mem, and so
on. A good overview of options is provided on that wiki page you cite.

Count Distinct Compared on Top 4 SQL Databases
>
> https://www.periscopedata.com/blog/count-distinct-in-mysql-postgres-sql-server-and-oracle.html

Given the figure for PostgreSQL runtime of Query 1 in that blog post I
would strongly suspect an on-disk sort was taking place due to too low
work_mem setting, but unfortunately the post doesn't go into details of the
database installation other than version numbers. Nor do the authors
demonstrate any effort to find out the reason for such performance
degradation, unfortunately, summarizing it with something as broad as
"PostgreSQL is slow with count distinct".

Select * is very slow
> http://postgresql.nabble.com/Select-is-very-slow-td3254568.html

In this particular case it's hard to tell what was the reason for query
slowdown, a number of valid hypothesis were suggested in the replies,
unfortunately thread author didn't reply. Judging from low shared_buffers
setting I would expect the database was not tuned properly, thus there is
no reason to expect it to perform.

postgresql simple select is slow
> http://stackoverflow.com/questions/9019797/postgresql-simple-select-is-slow

This question complains about the query taking 17s to process, but the
EXPLAIN ANALYZE suggests otherwise: only 18ms. Again a number of valid
theories are in the question's comments, most notably the time might be
consumed by the data transfer to the client.

Slow select - PostgreSQL
> http://stackoverflow.com/questions/18508866/slow-select-postgresql

Given that the accepted answer for this question suggests adding an index,
I assume that was the reason to this query slowness.

PosgreSQL: very slow select on a single table with no joins
>
> http://dba.stackexchange.com/questions/73677/posgresql-very-slow-select-on-a-single-table-with-no-joins

Again, the slowness is on the client-server communication due to high
volume of data being transferred, as confirmed by the author of the
question in the accepted answer's comment.

postgresql simple select is slow
> http://stackoverflow.com/questions/9019797/postgresql-simple-select-is-slow

(this is a duplicate of one of the links above)

PostgreSQL queries slower than before?
>
>
> http://serverfault.com/questions/644980/postgresql-queries-slower-than-before

There is too little information present in this question which is
PostgreSQL-specific. A successful diagnosis of the problem would involve
setting log_min_duration_statement to something around 500ms and collecting
slow queries.

Very slow column count on large Postgres tabls
> http://www.heidisql.com/forum.php?t=17959

The slowness of COUNT(*) in PostgreSQL seems to be the only valid complaint
in the materials you've presented above. It is a very well known issue
which is due to the MVCC model employed by PostgreSQL. Not very much might
be done about this, however as the following wiki page suggests, some
improvement might be gained from Index-only scans introduced in version 9.2:

https://wiki.postgresql.org/wiki/Slow_Counting
https://wiki.postgresql.org/wiki/Index-only_scans#Is_.22count.28.2A.29.22_much_faster_now.3F

Cheers!
--
*Oleksandr "Alex" Shulgin*
*Database Engineer*

Mobile: +49 160 84-90-639
Email: oleksandr(dot)shulgin(at)zalando(dot)de