Re: BUG #13852: SQL Select Slow Issues - Mailing list pgsql-bugs
From | Shulgin, Oleksandr |
---|---|
Subject | Re: BUG #13852: SQL Select Slow Issues |
Date | |
Msg-id | CACACo5TD3S-Vvcy8kZ9zjwpAtcv8VW16n2JvHOGE+AhJeocmxA@mail.gmail.com Whole thread Raw |
In response to | BUG #13852: SQL Select Slow Issues (eugeneymail@ymail.com) |
List | pgsql-bugs |
On Wed, Jan 6, 2016 at 9:42 PM, <eugeneymail@ymail.com> wrote: > The following bug has been logged on the website: > > Bug reference: 13852 > Logged by: Eugene > Email address: eugeneymail@ymail.com > PostgreSQL version: 9.4.5 > Operating system: Linux > Description: > > I have been a long time Oracle user. Currently I am thinking to switch t= o > 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 li= ke > those in releases, it will up to the user to tune those parameters latero= n, > 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. I= n > 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 nic= e > 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 o= f > the PostgreSQL for future reference: > > Starting with the quote from > https://wiki.postgresql.org/wiki/Slow_Query_Questions > =E2=80=9CIn any given week, some 50% of the questions on #postgresql IRC = and 75% on > pgsql-performance are requests for help with a slow query.=E2=80=9D > 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-s= erver-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-sl= ow 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-o= n-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-sl= ow (this is a duplicate of one of the links above) PostgreSQL queries slower than before? > > > http://serverfault.com/questions/644980/postgresql-queries-slower-than-be= fore 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=3D17959 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_m= uch_faster_now.3F Cheers! --=20 *Oleksandr "Alex" Shulgin* *Database Engineer* Mobile: +49 160 84-90-639 Email: oleksandr.shulgin@zalando.de
pgsql-bugs by date: