Re: Seeking reason behind performance gain in 12 with HashAggregate

On Mon, Jan 13, 2020 at 12:44:14PM -0800, Shira Bezalel wrote: Hi Michael, I appreciate your question. I ran a vacuum analyze on the 9.6 table and it yielded no difference. Same number of buffers were read, same query plan. VACUUM ANALYZE won't shrink the table - the number of buffers will

Re: Seeking reason behind performance gain in 12 with HashAggregate

Hi Michael, I appreciate your question. I ran a vacuum analyze on the 9.6 table and it yielded no difference. Same number of buffers were read, same query plan. Thanks, Shira On Mon, Jan 13, 2020 at 10:07 AM Michael Lewis wrote: > I am not at all familiar with PostGIS so perhaps this is a

Re: Seeking reason behind performance gain in 12 with HashAggregate

I am not at all familiar with PostGIS so perhaps this is a silly question, is bloat an issue on the older instance? Correlation isn't causation, but half the buffers scanned and half the runtime in the v12 plan has me curious why that might be. >

Re: Seeking reason behind performance gain in 12 with HashAggregate

Hi Justin, I'm seeing no difference in the query plan with JIT disabled in 12.1. Thanks, Shira On Mon, Jan 13, 2020 at 8:42 AM Justin Pryzby wrote: > On Mon, Jan 13, 2020 at 08:29:05AM -0800, Shira Bezalel wrote: > > Here is the EXPLAIN (ANALYZE ON, BUFFERS ON) output from both systems: > > >

Re: Seeking reason behind performance gain in 12 with HashAggregate

On Mon, Jan 13, 2020 at 08:29:05AM -0800, Shira Bezalel wrote: > Here is the EXPLAIN (ANALYZE ON, BUFFERS ON) output from both systems: > > 9.6 plan > 12.1 plan > Is there something that was changed/improved in either 10, 11

Seeking reason behind performance gain in 12 with HashAggregate

Hi All, I'm testing an upgrade from Postgres 9.6.16 to 12.1 and seeing a significant performance gain in one specific query. This is really great, but I'm just looking to understand why. Reading through the release notes across all the new versions (10, 11, 12) hasn't yielded an obvious cause

Re: performance degredation after upgrade from 9.6 to 12

po 16. 12. 2019 v 14:02 odesílatel Mariel Cherkassky < mariel.cherkas...@gmail.com> napsal: > I see, thank u ! > Maybe I didnt see big difference because most of my tables arent so big. > My db`s size is 17GB and the largest table contains about 20M+ records. > Postgres 12 has enabled JIT by

Re: performance degredation after upgrade from 9.6 to 12

I see, thank u ! Maybe I didnt see big difference because most of my tables arent so big. My db`s size is 17GB and the largest table contains about 20M+ records. Thanks again !

RE: performance degredation after upgrade from 9.6 to 12

coordinator process in Oracle terms) from another one. Thus there could be more serious hardware requirements even just to keep performance the same. I believe that the real benefit of the parallel will be shown when you have pair of large and wide tables (30M or more each) with hash join (typical

Re: performance degredation after upgrade from 9.6 to 12

Hey Jeff,Andrew, I continued testing the 12version vs the 96 version and it seems that there is almost non diff and in some cases pg96 is faster than 12. I compared the content of pg_stat_statements after each test that I have done and it seems that the db time is almost the same and sometimes 96

Re: Logical replication performance

On Fri, Nov 29, 2019 at 11:06 AM Florian Philippon < florian.philip...@doctolib.com> wrote: > > We tried another solution: we loaded a minimal schema (without indexes and > constraints) on the subscriber and created the subscription. The initial > copy phase was way faster (a few hours). Then we

Re: Considerable performance downgrade of v11 and 12 on Windows

t 22:47 +0300, Eugene Podshivalov wrote: > > It sounds strange but the "type" is indeed impacting the overall > > performance somehow. > > I've just tried to execute the following sequence of commands on a > > fresh new database with PostreSQL v10 and both the copy

Re: Considerable performance downgrade of v11 and 12 on Windows

On Sat, 2019-11-30 at 22:47 +0300, Eugene Podshivalov wrote: > It sounds strange but the "type" is indeed impacting the overall > performance somehow. > I've just tried to execute the following sequence of commands on a > fresh new database with PostreSQL v10 and both the

Re: Considerable performance downgrade of v11 and 12 on Windows

It sounds strange but the "type" is indeed impacting the overall performance somehow. I've just tried to execute the following sequence of commands on a fresh new database with PostreSQL v10 and both the copy and primary key commands performed as slow as in v11 and 12. SET synchron

Re: Logical replication performance

Em sex., 29 de nov. de 2019 às 17:06, Florian Philippon < florian.philip...@doctolib.com> escreveu: > Hello community! > > Hi Florian > We are currently testing PostgreSQL 11's built-in logical replication. We > are trying to initialize a subscriber (from scratch) from a publisher with > a

Logical replication performance

Hello community! We are currently testing PostgreSQL 11's built-in logical replication. We are trying to initialize a subscriber (from scratch) from a publisher with a large database (+6TB) with around 220 tables. We tweaked the configuration parameters below, both on publisher and subscriber,

Re: Considerable performance downgrade of v11 and 12 on Windows

PostgreSQL on Windows for Planet OSM database and have > > noticed considirable decrease in performance when upgrading from v10 > > to 11 or 12. Here are the details of the experiment I conducted trying > > to figure out what is causing the issue. > > > > Installed Postgr

Re: Considerable performance downgrade of v11 and 12 on Windows

On Fri, 2019-11-29 at 13:04 +0300, Eugene Podshivalov wrote: > I'm using PostgreSQL on Windows for Planet OSM database and have > noticed considirable decrease in performance when upgrading from v10 > to 11 or 12. Here are the details of the experiment I conducted trying > to fig

Re: Considerable performance downgrade of v11 and 12 on Windows

I don't think so. Why adding primary key shows the same downgraded performance as well then? пт, 29 нояб. 2019 г. в 13:37, Thomas Kellerer : > > Eugene Podshivalov schrieb am 29.11.2019 um 11:04: > > Imported ways data from a file and added a primary key. > > > > SET s

Re: Considerable performance downgrade of v11 and 12 on Windows

are not doing the same thing - the piping through the TYPE command is most probably eating all the performance

Considerable performance downgrade of v11 and 12 on Windows

Hi, I'm using PostgreSQL on Windows for Planet OSM database and have noticed considirable decrease in performance when upgrading from v10 to 11 or 12. Here are the details of the experiment I conducted trying to figure out what is causing the issue. Installed PostgreSQL 10 from scratch. Created

Re: performance degredation after upgrade from 9.6 to 12

same example, only 100 times more rows, and still see the regression at about 16%. This is a major infrastructure change patch which has been extensively built on since then, the chances of reverting it are very small. It is making an omelette, and your example is one of the eggs that got

Re: performance degredation after upgrade from 9.6 to 12

to 10 is was significant slower till I disabled parallel query. We have a very small database (40 tables and all together max 4GB) and we have no long running queries (the largest queries run max 2 to 3 seconds). That's when parallel query gives a performance degrade in my opinion. Best regards

Re: performance degredation after upgrade from 9.6 to 12

. I doubt your test case benchmarking was very reliable to start with, you only show a single execution and didn't indicate you had more unshown ones. If I do more credible benchmarking, I do get a performance regression but it closer is to 16% than to 3 fold. And it doesn

Re: performance degredation after upgrade from 9.6 to 12

Hey Andrew, It seems that changing this parameter worked for me. Setting it to zero means that there wont be any parallel workers for one query right ? Is it something familiar this problem with the gatherers ?

Re: performance degredation after upgrade from 9.6 to 12

of 11 and 12 ver have been discussed. Look thru the archive, but I didn't remember the problem status - resolved or not. Andrew. 24 ноября 2019 г. 15:53:19 GMT+03:00, Mariel Cherkassky пишет: >Hey all, >I'm testing performance of two identical machines one in 9.6 and the >second >

Re: performance degredation after upgrade from 9.6 to 12

Hello, did you run ananlyze on your db? Le dim. 24 nov. 2019 à 13:53, Mariel Cherkassky a écrit : > Hey all, > I'm testing performance of two identical machines one in 9.6 and the > second one is in 12. The second machine is a clone of the first one + db > upgrade to 12 beta 3 (Y

performance degredation after upgrade from 9.6 to 12

Hey all, I'm testing performance of two identical machines one in 9.6 and the second one is in 12. The second machine is a clone of the first one + db upgrade to 12 beta 3 (Yes I'm aware 12.1 was released). machine stats : 32gb ram 8 cpu regular hd (not ssd) my postgresql.confg settings

Wrong estimations and NL Anti join poor performance

Hello colleagues - The problem description: We're moving from 9.6 to 11.5. There is a SQL code that never ends in 11.5 but works fine in 9.6. The main cause is the optimizer considers of using NL Anti join instead of Merge in 9.6. And the root cause - wrong estimation while self-joining.

Re: Notifications within triggers seem to compromise performance

Le 28/10/2019 à 17:25, Tom Lane a écrit : =?UTF-8?Q?Gr=c3=a9goire_de_Turckheim?= writes: Le 28/10/2019 à 15:22, Tom Lane a écrit : We made some performance improvements for NOTIFY just a couple months ago, cf commits b10f40bf0, bb5ae8f6c, bca6e6435, 51004c717. It would be interesting to know

Re: Notifications within triggers seem to compromise performance

=?UTF-8?Q?Gr=c3=a9goire_de_Turckheim?= writes: > Le 28/10/2019 à 15:22, Tom Lane a écrit : >> We made some performance improvements for NOTIFY just a couple months >> ago, cf commits b10f40bf0, bb5ae8f6c, bca6e6435, 51004c717. It would >> be interesting to know how much thos

Re: Notifications within triggers seem to compromise performance

Le 28/10/2019 à 15:22, Tom Lane a écrit : =?UTF-8?Q?Gr=c3=a9goire_de_Turckheim?= writes: I guess we stumbled upon a performance issue with notifications sent within triggers (using PostgreSQL version 11.5) and I'd like your opinion about this. We made some performance improvements for NOTIFY

Re: Notifications within triggers seem to compromise performance

=?UTF-8?Q?Gr=c3=a9goire_de_Turckheim?= writes: > I guess we stumbled upon a performance issue with notifications sent within > triggers (using PostgreSQL version 11.5) > and I'd like your opinion about this. We made some performance improvements for NOTIFY just a couple months ago, c

Notifications within triggers seem to compromise performance

Hi there! I guess we stumbled upon a performance issue with notifications sent within triggers (using PostgreSQL version 11.5) and I'd like your opinion about this. We want our app to maintain a data cache, so each instance of the app listens to some channels (one per table). There are update

Re: Would SSD improve Index Only Scan performance by a lot?

, coming out to 4 fold slow down on average. If that is the case, maybe you can get the performance back up by tweaking some settings, rather than changing hardware. > Right now the database is running on a traditional HDD. SSDs have a much > faster seek time than traditional HDDs. > > W

Re: Would SSD improve Index Only Scan performance by a lot?

bloated/corrupted indexes, or some query inefficiency, then > faster disks really won't do anything. > > Depending on the data type and size of the data you may be able to help your > query performance by choosing an index type other than the out-of-the-box > btree as well (such as a hash or brin index) or maybe even a different sort > order on the index, or a partial index. > >

Re: Would SSD improve Index Only Scan performance by a lot?

some query inefficiency, then faster disks really won't do anything. Depending on the data type and size of the data you may be able to help your query performance by choosing an index type other than the out-of-the-box btree as well (such as a hash or brin index) or maybe even a different sort order on the index, or a partial index.

Sv: Would SSD improve Index Only Scan performance by a lot?

På onsdag 09. oktober 2019 kl. 01:37:06, skrev Arya F mailto:arya6...@gmail.com>>: As my table has gotten bigger, it takes longer to get a single row back when querying a row by its btree index. Right now the database is running on a traditional HDD. SSDs have a much faster seek time than

Would SSD improve Index Only Scan performance by a lot?

As my table has gotten bigger, it takes longer to get a single row back when querying a row by its btree index. Right now the database is running on a traditional HDD. SSDs have a much faster seek time than traditional HDDs. Would switching to an SSD improve "Index Only Scan" time greatly? by at

Re: Upsert performance considerations (~1 mil/hour)

On Wed, Sep 4, 2019 at 1:30 PM Fredrik Blomqvist < fredrik.blomqvist...@gmail.com> wrote: > Hi, > > I have tried doing some research for quite a while on the performance > implications of the built-in upsert (INSERT ... ON CONFLICT UPDATE...) when > a lot of upsert

Re: Upsert performance considerations (~1 mil/hour)

Thanks for the response Jeff! On Wed, Sep 4, 2019 at 3:58 PM Jeff Janes wrote: > On Wed, Sep 4, 2019 at 1:30 PM Fredrik Blomqvist < > fredrik.blomqvist...@gmail.com> wrote: > >> Hi, >> >> I have tried doing some research for quite a while on the perform

Re: UPGRADE TO PG11 CAUSED DEGREDATION IN PERFORMANCE

Hi ne 18. 8. 2019 v 8:57 odesílatel Mariel Cherkassky < mariel.cherkas...@gmail.com> napsal: > Hey, > I upgraded my pg9.6 cluster to pg11.2. > As it seems after the upgrade the duration of the same flow in my > application raised from 13 minutes to 19 minutes. > > The test I did : > 1.reset

UPGRADE TO PG11 CAUSED DEGREDATION IN PERFORMANCE

Hey, I upgraded my pg9.6 cluster to pg11.2. As it seems after the upgrade the duration of the same flow in my application raised from 13 minutes to 19 minutes. The test I did : 1.reset pg_stat_statements 2.run the applicative flow 3.collect everything from pg_stat_statements I did this test on

Re: Re: performance bottlenecks on lock transactionid

王若楠 wrote: > I want to find a way to reduce the lock waiting and improve the > performance. You either have to make the transactions shorter, or you let the different clients modify different rows, so that they don't lock each other. That concurrent writers on the same data lock each

re:Re: performance bottlenecks on lock transactionid

hello,LaurenzAlbe Yes,pg_locksisonlyanitemthatdoesnotgetalockintheview.Thetestdatais300warehousesconnections,andtheCPUisonlyabout60%.Ithinkthelockbecomesaperformancebottleneckatthistime.Iwanttofindawaytoreducethelockwaitingandimprovetheperformance. -- 原始邮件 --

Re: performance bottlenecks on lock transactionid

王若楠 wrote: > We used benchmarksql 4.1.0 to test the performance of PG12 beta TPCC. > We found performance bottlenecks on lock transactionid. You included an attachment with results from the "pg_locks" view where "granted" is FALSE for all entries. I'll assume that these

ODP: Planner performance in partitions

partitions with old data we no longer need. Pozdrawiam, Piotr Włodarczyk Od: Michael Lewis Wysłano: wtorek, 13 sierpnia 2019 00:37 Do: David Rowley DW: Piotr Włodarczyk; MichaelDBA; Piotr Włodarczyk; pgsql-performance@lists.postgresql.org Temat: Re: Planner performance in partitions

Re: Planner performance in partitions

Was there a reason to exceed 100-500 partitions in real life that pushed you to do this test? Is there some issue you see when using 100 partitions that is solved or reduced in severity by increasing to 1200 or 6000 partitions?

Re: Planner performance in partitions

On Tue, 13 Aug 2019 at 08:03, Piotr Włodarczyk wrote: > PostgreSQL11.5: > > select on main partition (public.book): planner: 60ms, execution: 5ms > select on partitioned table (public.book_1-1000): planner: 2.7 ms, execution: > 2,4 ms > > PostgreSQL 12B3: > > select on main partition

Re: Planner performance in partitions

Thanks for clarifying your position and sharing the results you have seen. That is impressive indeed. It seems likely that waiting for v12 is needed since feature are not back patched. Perhaps one of the contributors will confirm, but that is my expectation.

ODP: Planner performance in partitions

@Michael Lewis: I know documentation. I'm just considerations about possible performance tricks in current production version. I've tested this on V12 on another computer and I can say that I'm impressed. I've checked on 1200 partitions and times are: PostgreSQL11.5: • select on main partition

Re: Planner performance in partitions

Queries against tables with a lot of partitions (> 1000) start to incur an increasing planning time duration even with the current version, V11.  V12 purportedly has fixed this problem, allowing thousands of partitioned tables without a heavy planning cost.  Can't seem to find the threads on

Re: Planner performance in partitions

"It is also important to consider the overhead of partitioning during query planning and execution. The query planner is generally able to handle partition hierarchies with *up to a few hundred partitions fairly well*, provided that typical queries allow the query planner to prune all but a small

Planner performance in partitions

Hello, We have partitioned tables in two levels. Both stages are partitioned in ranges method. We see that planner and executor time was 10 time slower when we asked main table rather than partitioned. My question is did planner and executor are working optimal? I have doubts about it. Let's

Re: Bitmap heap scan performance

I decrease the number of legacy_organisation_id’s that are being > used then the query performance gets much better, but presumably > that’s because there’s a smaller dataset. > What are the actual counts that your queries are returning? For your first query at least, are you sure your issue is not

Re: Bitmap heap scan performance

Aha! That's a great hint, we had that set down to an obscenely low value due to our max_connections setting being quite high. I've tweaked it back up to 4MB for now and it's definitely had a marked improvement! Many Thanks, Rob On 09/08/2019, Jeff Janes wrote: > On Fri, Aug 9, 2019 at 4:42 AM

Re: Bitmap heap scan performance

On Fri, Aug 9, 2019 at 4:42 AM Rob Emery wrote: > > It > seems to me like the Bitmap Heap Scan on proposal is the issue because > the recheck is throwing away enormous amounts of data. Have you tried increasing work_mem? The probable reason for the recheck is that your bitmap overflows the

Bitmap heap scan performance

integrity so I'll need to wait until that's in (I think it's coming in PG12?) If I decrease the number of legacy_organisation_id’s that are being used then the query performance gets much better, but presumably that’s because there’s a smaller dataset. Any thoughts or ideas? Thanks Rob -- <ht

Re: improving windows functions performance

Am 05.08.19 um 22:47 schrieb Mariel Cherkassky: Hey, I have a very big query that consist from 3-4 subqueries that use windows functions. There is a chance that I'll need to rewrite the query but first I'm trying to search for other ways to improve it and I'll be happy to hear if one of u

improving windows functions performance

Hey, I have a very big query that consist from 3-4 subqueries that use windows functions. There is a chance that I'll need to rewrite the query but first I'm trying to search for other ways to improve it and I'll be happy to hear if one of u have an idea. Basically my table has the following

Re: PSQL performance - TPS

b/master/toolset/databases/postgres/postgresql.conf * java frameworks: https://github.com/TechEmpower/FrameworkBenchmarks/tree/master/frameworks/Java > We have tested with a simple Java code firing insert As I see - There are lot of java framework - and sometimes 10x difference in

Re: PSQL performance - TPS

On Thu, 1 Aug 2019, 23:58 Rick Otten, wrote: > > > On Thu, Aug 1, 2019 at 2:15 PM Andres Freund wrote: > >> Hi, >> >> On 2019-08-01 23:36:33 +0530, Purav Chovatia wrote: >> > > If you've set synchronous_commit = off, and you still get only 1200 >> > > transactions/sec, something else is off.

Re: PSQL performance - TPS

On Thu, Aug 1, 2019 at 2:15 PM Andres Freund wrote: > Hi, > > On 2019-08-01 23:36:33 +0530, Purav Chovatia wrote: > > > If you've set synchronous_commit = off, and you still get only 1200 > > > transactions/sec, something else is off. Are you sure you set that? > > I am not very surprised with

Re: PSQL performance - TPS

Hi, On 2019-08-01 23:36:33 +0530, Purav Chovatia wrote: > > If you've set synchronous_commit = off, and you still get only 1200 > > transactions/sec, something else is off. Are you sure you set that? > I am not very surprised with these results. However, what’s the disk type? > That can matter

Re: PSQL performance - TPS

Hi, On 2019-08-01 08:40:53 +0530, Shital A wrote: > Need help in: > 1. On this env(8core cpu, 16GB) what is the TPS that we can expect? We have > tested with a simple Java code firing insert and commit in a loop on a > simple table with one column. We get 1200 rows per sec. If we increase >

Re: PSQL performance - TPS

On 01/08/2019 15:10, Shital A wrote: Hello, We are working on development of an application with postgresql 9.6 as backend. Application as a whole is expected to give an throughput of 100k transactions per sec. The transactions are received by DB from component firing DMLs in ad-hoc fashion

PSQL performance - TPS

Hello, We are working on development of an application with postgresql 9.6 as backend. Application as a whole is expected to give an throughput of 100k transactions per sec. The transactions are received by DB from component firing DMLs in ad-hoc fashion i.e. the commits are fired after random

Re: Perplexing, regular decline in performance

Hi, On 2019-07-18 16:01:46 -0400, Hugh Ranalli wrote: > I've been going by a couple of articles I found about interpreting > pg_buffercache ( > https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers), > and so far shared buffers look okay. Our database is 486 GB, with shared

Re: Perplexing, regular decline in performance

On Tue, 25 Jun 2019 at 12:23, Justin Pryzby wrote: > It's possible that the "administrative" queries are using up lots of your > shared_buffers, which are (also/more) needed by the customer-facing > queries. I > would install pg_buffercache to investigate. Or, just pause the admin > queries >

Re: Perplexing, regular decline in performance

Hi On 2019-07-17 13:55:51 -0400, Alvaro Herrera wrote: > Be careful with pg_buffercache though, as it can cause a hiccup in > operation. I think that's been fixed a few years back: commit 6e654546fb61f62cc982d0c8f62241b3b30e7ef8 Author: Heikki Linnakangas Date: 2016-09-29 13:16:30 +0300

Re: Perplexing, regular decline in performance

On 2019-Jun-26, Justin Pryzby wrote: > > Also, Should pg_buffercache perhaps be run at the beginning and end of the > > week, to see if there is a significant difference? > > Yes; buffercache can be pretty volatile, so I'd save it numerous times each at > beginning and end of week. Be careful

Re: Perplexing, regular decline in performance

On Wed, 26 Jun 2019 at 15:18, Tom Lane wrote: > Alvaro Herrera writes: > > On 2019-Jun-26, Hugh Ranalli wrote: > >> From my research in preparing for the upgrade, I understood transparent > >> huge pages were a good thing, and should be enabled. Is this not > correct? > > > It is not. > > Yeah

Re: Perplexing, regular decline in performance

On Wed, Jun 26, 2019 at 03:00:43PM -0400, Hugh Ranalli wrote: > Pausing the admin queries isn't an option in our environment, especially as > the issue reveals itself over the course of days, not minutes or hours. Perhaps you can pause it for a short while at EOW and see if there's a dramatic

Re: Perplexing, regular decline in performance

Alvaro Herrera writes: > On 2019-Jun-26, Hugh Ranalli wrote: >> From my research in preparing for the upgrade, I understood transparent >> huge pages were a good thing, and should be enabled. Is this not correct? > It is not. Yeah ... they would be a good thing perhaps if the quality of the

Re: Perplexing, regular decline in performance

ematic table is essentially empty) and > at the end (when it is much larger)? Not necessarily. Though, if a plan change was the culprit you would probably see a sudden change in performance characteristics rather than gradual. Worth making sure, anyway. -- Álvaro Herrerahttps://www.

Re: Perplexing, regular decline in performance

On Wed, Jun 26, 2019 at 12:02 PM Hugh Ranalli wrote: > I'm sorry, but I'm not sure what you mean by the "distribution of values > within the columns." Can you clarify or provide an link to an example? I would mostly just like to see the schema of the table in question, including indexes, and a

Re: Perplexing, regular decline in performance

On Wed, 26 Jun 2019 at 14:52, Peter Geoghegan wrote: > Can you show us the definition of the table, including its indexes? > Can you describe the data and distribution of values within the > columns, particularly where they're indexed? > I'm sorry, but I'm not sure what you mean by the

Re: Perplexing, regular decline in performance

On Tue, 25 Jun 2019 at 12:23, Justin Pryzby wrote: > What kernel? Version? OS? > Ubuntu 18.04; current kernel is 4.15.0-51-generic4 If Linux, I wonder if transparent hugepages or KSM are enabled ? It seems > possible that truncating the table is clearing enough RAM to mitigate the > issue,

Re: Perplexing, regular decline in performance

d noticed > that as the number of "raw_page" columns in a particular table grew, > performance would decline. They wrote a script that once a week locks the > table, deletes the processed large columns (they are not needed after > processing), copies the remaining data to a backup table,

Re: Perplexing, regular decline in performance

assumptions by the planner can cause degradation of performance. > Autovacuum is enabled. As well, we had problems with autovacum running reliably in 8.2, so we are still running a nightly script that runs VACUUM ANALYZE on the complete database. As for VACUUM ANALYZE FULL, the database underwent a full

Re: Perplexing, regular decline in performance

On Tue, Jun 25, 2019 at 11:49:03AM -0400, Hugh Ranalli wrote: > I'm hoping people can help me figure out where to look to solve an odd > PostgreSQL performance problem. What kernel? Version? OS? If Linux, I wonder if transparent hugepages or KSM are enabled ? It seems possible that trun

Re: Perplexing, regular decline in performance

I didn't say do it all the time, I said if he hasn't done it already he should try that as a way of ensuring the database server's understanding of the data as it stands is correct. Otherwise there isn't enough information to suggest other solutions as there is no description of the operating

Re: Perplexing, regular decline in performance

On Tue, Jun 25, 2019 at 10:55:22PM +0700, Benjamin Scherrey wrote: > Have you done a VACUUM ANALYZE FULL on your database? This needs to be done > periodically to inform the server of the statistics of how the data and > relations are distributed across the database. I think this is wrong.

Re: Perplexing, regular decline in performance

Have you done a VACUUM ANALYZE FULL on your database? This needs to be done periodically to inform the server of the statistics of how the data and relations are distributed across the database. Without this bad assumptions by the planner can cause degradation of performance. Also, if you

Perplexing, regular decline in performance

I'm hoping people can help me figure out where to look to solve an odd PostgreSQL performance problem. A bit of background: We have a client with a database of approximately 450 GB, that has a couple of tables storing large amounts of text, including full HTML pages from the Internet. Last fall

Re: Planning performance problem (67626.278ms)

Hi, On 2019-04-08 16:10:17 -0700, Jeremy Schneider wrote: > On 4/8/19 07:42, Justin Pryzby wrote: > > On Mon, Apr 08, 2019 at 04:33:34PM +0200, Pavel Stehule wrote: > >> po 8. 4. 2019 v 16:11 odesílatel Krzysztof Plocharz > >> napsal: > >> > >>> We have some very strange query planning problem.

Re: Planning performance problem (67626.278ms)

On Mon, Apr 08, 2019 at 04:55:36PM +0200, Krzysztof Plocharz wrote: > We did pgrepack and it did help, but is it possible for > get_actual_variable_range to take over 60 seconds? You have many tables being joined, perhaps in exhaustive search, so maybe that's being called many times. What

Re: Planning performance problem (67626.278ms)

po 8. 4. 2019 v 16:55 odesílatel Krzysztof Plocharz napsal: > > > On 2019/04/08 16:42, Justin Pryzby wrote: > > On Mon, Apr 08, 2019 at 04:33:34PM +0200, Pavel Stehule wrote: > >> po 8. 4. 2019 v 16:11 odes�latel Krzysztof Plocharz < > ploch...@9livesdata.com> napsal: > >> > >>> We have some

Re: Planning performance problem (67626.278ms)

On 2019/04/08 16:33, Pavel Stehule wrote: po 8. 4. 2019 v 16:11 odesílatel Krzysztof Plocharz mailto:ploch...@9livesdata.com>> napsal: Hi We have some very strange query planning problem. Long story short it takes 67626.278ms just to plan. Query execution takes 12ms.

Re: Planning performance problem (67626.278ms)

On 2019/04/08 16:42, Justin Pryzby wrote: On Mon, Apr 08, 2019 at 04:33:34PM +0200, Pavel Stehule wrote: po 8. 4. 2019 v 16:11 odes�latel Krzysztof Plocharz napsal: We have some very strange query planning problem. Long story short it takes 67626.278ms just to plan. Query execution takes

Re: Planning performance problem (67626.278ms)

On Mon, Apr 08, 2019 at 04:33:34PM +0200, Pavel Stehule wrote: > po 8. 4. 2019 v 16:11 odesílatel Krzysztof Plocharz > napsal: > > > We have some very strange query planning problem. Long story short it > > takes 67626.278ms just to plan. Query execution takes 12ms. > > > > Query has 7 joins

Re: Planning performance problem (67626.278ms)

po 8. 4. 2019 v 16:11 odesílatel Krzysztof Plocharz napsal: > Hi > > We have some very strange query planning problem. Long story short it > takes 67626.278ms just to plan. Query execution takes 12ms. > > Query has 7 joins and 2 subselects. > It looks like the issue is not deterministic,

Re: Planning performance problem (67626.278ms)

No, Autovacuum is running. On 2019/04/08 16:18, Igor Neyman wrote: -Original Message- From: Krzysztof Plocharz [mailto:ploch...@9livesdata.com] Sent: Monday, April 08, 2019 10:11 AM To: pgsql-performance@lists.postgresql.org Subject: Planning performance problem (67626.278ms) Hi We

RE: Planning performance problem (67626.278ms)

-Original Message- From: Krzysztof Plocharz [mailto:ploch...@9livesdata.com] Sent: Monday, April 08, 2019 10:11 AM To: pgsql-performance@lists.postgresql.org Subject: Planning performance problem (67626.278ms) Hi We have some very strange query planning problem. Long story short

Re: Postgresql Sort cost Poor performance?

1141 | HT6MHB700 NH731PA Is there any possibility of optimization? -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

Re: Postgresql Sort cost Poor performance?

> "tank" == tank zhang <6220...@qq.com> writes: tank> smtoc| character varying(50) | | | tank> Sort Key: smtoc What is the output of SHOW lc_collate; One of the most common reasons for slow sorting is that you're sorting a text/varchar field in

Re: impact of auto explain on overall performance

On Thu, Mar 14, 2019 at 3:29 AM Stephan Schmidt wrote: > Hello, > > > > i’m currently working on a high Performance Database and want to make sure > that whenever there are slow queries during regular operations i’ve got all > Information about the query in my logs. So auto

Re: impact of auto explain on overall performance

On 3/14/19 9:23 AM, Justin Pryzby wrote: On Thu, Mar 14, 2019 at 07:29:17AM +, Stephan Schmidt wrote: i’m currently working on a high Performance Database and want to make sure that whenever there are slow queries during regular operations i’ve got all Information about the query in my

Re: impact of auto explain on overall performance

On Thu, Mar 14, 2019 at 07:29:17AM +, Stephan Schmidt wrote: > i’m currently working on a high Performance Database and want to make sure > that whenever there are slow queries during regular operations i’ve got all > Information about the query in my logs. So auto_explain com

impact of auto explain on overall performance

Hello, i’m currently working on a high Performance Database and want to make sure that whenever there are slow queries during regular operations i’ve got all Information about the query in my logs. So auto_explain come to mind, but the documentation explicitly states that it Comes at a cost

<    2   3   4   5   6   7   8   9   10   >