[PERFORM] Migration to pglister - Before

Greetings, We will be migrating these lists to pglister in the next few minutes. This final email on the old list system is intended to let you know that future emails will have different headers and you will need to adjust your filters. The changes which we expect to be most significant to user

Re: [PERFORM] PostgreSQL 9.6 wals management

Thank you for the clarification. בתאריך 20 בנוב׳ 2017 14:28,‏ "Michael Paquier" כתב: > On Mon, Nov 20, 2017 at 6:02 PM, Mariel Cherkassky > wrote: > > This morning , I set the wal_keep_segments to 100 and I set the > > archive_timeout to 6 minutes. Now, after setting those settings and > starti

Re: [PERFORM] PostgreSQL 9.6 wals management

On Mon, Nov 20, 2017 at 6:02 PM, Mariel Cherkassky wrote: > This morning , I set the wal_keep_segments to 100 and I set the > archive_timeout to 6 minutes. Now, after setting those settings and starting > the cluster wals switch is working fine and I didnt see that many wals were > However, doesnt

[PERFORM] PostgreSQL 9.6 wals management

Hi, I'm trying to understand my wals behavior on my postgresql environment. My wal settings are : wal_keep_segments = 200 max_wal_size = 3GB min_wal_size = 80MB archive_command = 'cp %p /PostgreSQL-wal/9.6/pg_xlog/wal_archives/%f' archive_timeout = 10 #checkpoint_flush_afte

[PERFORM] POWA doesn't show queries executed

Dear all I have successfully installed POWA (http://dalibo.github.io/powa), including all required extensions, see the following Printscreen of its operation of end email. But when executing queries in psql- comand line, this queries are not monitored by powa. I have checked that only Postgresql

Re: [PERFORM] CREATE STATISTICS and join selectivity

On 16 November 2017 at 09:19, Justin Pryzby wrote: > I just noticed that PG10 CREATE STATISTICS (dependencies) doesn't seem to work > for joins on multiple columns; is that right? Unfortunately, for now, they're not used for join selectivity estimates, only for the base rel selectivities. That's

[PERFORM] CREATE STATISTICS and join selectivity

I just noticed that PG10 CREATE STATISTICS (dependencies) doesn't seem to work for joins on multiple columns; is that right? With statistics on table for 2017 but not 20171110: ts=# CREATE STATISTICS x ON site_id,sect_id FROM eric_enodeb_cell_2017; ts=# ANALYZE VERBOSE eric_enodeb_cell_20

Re: [PERFORM] query performance issue

2017-11-15 20:58 GMT+01:00 Gunther : > > On 11/15/2017 8:12, Pavel Stehule wrote: > > There is wrong plan due wrong estimation > > for this query you should to penalize nested loop > > set enable_nestloop to off; > > before evaluation of this query > > > You are not the only one with this issue. M

Re: [PERFORM] query performance issue

On 11/15/2017 8:12, Pavel Stehule wrote: There is wrong plan due wrong estimation for this query you should to penalize nested loop set enable_nestloop to off; before evaluation of this query You are not the only one with this issue. May I suggest to look at this thread a little earlier th

[PERFORM] Re: Query planner gaining the ability to replanning after start of query execution.

May I suggest the recent discussion over the dreaded NL issue ... I had some similar ideas. http://www.postgresql-archive.org/OLAP-reporting-queries-fall-into-nested-loops-over-seq-scans-or-other-horrible-planner-choices-td5990160.html This could be massively useful and a huge leap in the industr

Re: [PERFORM] query performance issue

On Wed, Nov 15, 2017 at 03:03:39PM +0530, Samir Magar wrote: > I am having performance issues with one of the query. > The query is taking 39 min to fetch 3.5 mil records. > > I want to reduce that time to 15 mins. > could you please suggest something to its performance? > "HashAggregate (cost=4

Re: [PERFORM] query performance issue

2017-11-15 13:54 GMT+01:00 Samir Magar : > please find the EXPLAIN ANALYZE output. > > On Wed, Nov 15, 2017 at 3:13 PM, Pavel Stehule > wrote: > >> Hi >> >> please send EXPLAIN ANALYZE output. >> >> Regards >> >> Pavel >> >> 2017-11-15 10:33 GMT+01:00 Samir Magar : >> >>> Hello, >>> I am having p

Re: [PERFORM] query performance issue

please find the EXPLAIN ANALYZE output. On Wed, Nov 15, 2017 at 3:13 PM, Pavel Stehule wrote: > Hi > > please send EXPLAIN ANALYZE output. > > Regards > > Pavel > > 2017-11-15 10:33 GMT+01:00 Samir Magar : > >> Hello, >> I am having performance issues with one of the query. >> The query is takin

Re: [PERFORM] query performance issue

Hi please send EXPLAIN ANALYZE output. Regards Pavel 2017-11-15 10:33 GMT+01:00 Samir Magar : > Hello, > I am having performance issues with one of the query. > The query is taking 39 min to fetch 3.5 mil records. > > I want to reduce that time to 15 mins. > could you please suggest something

[PERFORM] query performance issue

Hello, I am having performance issues with one of the query. The query is taking 39 min to fetch 3.5 mil records. I want to reduce that time to 15 mins. could you please suggest something to its performance? server configuration: CPUs = 4 memory = 16 GM shared_buffers = 3 GB work_mem = 100MB eff

[PERFORM] Re: Query planner gaining the ability to replanning after start of query execution.

As a first step, before relaunching the query with a new plan I would be happy to be able to get information about sql queries having wrong estimates. Maybe thoses SQL queries could be collected using something similar to "auto_explain" module (tracing finished or cancelled queries). If the "corr

Re: [PERFORM] Query planner gaining the ability to replanning after start of query execution.

performance@postgresql.org Subject: Re: [PERFORM] Query planner gaining the ability to replanning after start of query execution. > Can you be more elaborate how you'd want to go about it? My initial approach would be to try to identify places in the plan where selectivity is serious

Re: [PERFORM] Query planner gaining the ability to replanning after start of query execution.

alf Of Oliver Mattos Sent: Monday, November 13, 2017 5:45 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] Query planner gaining the ability to replanning after start of query execution. I am interested in giving the query planner the ability to replan (or re-rank plans) after query e

Re: [PERFORM] Query planner gaining the ability to replanning after start of query execution.

> You can't just restart from scratch, because we may already have shipped > rows to the client For v1, replanning wouldn't be an option if rows have already been shipped, or for DML statements. > parallel plans and most importantly cursors? Parallel plans look do-able with the same approach, b

Re: [PERFORM] Query planner gaining the ability to replanning after start of query execution.

Oliver Mattos writes: >> Can you be more elaborate how you'd want to go about it? > ... If another candidate plan is now lower cost, the current plan would be > terminated[1] by setting a flag instructing each execnode to return as > if it had reached the end of the input, although still caching

Re: [PERFORM] Query planner gaining the ability to replanning after start of query execution.

doubt > that's worth any work (and even less the maintenance). > > Best regards > Arne Roland > > -Original Message- > From: pgsql-performance-ow...@postgresql.org > [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Oliver Mattos > Sent: Monday, Novemb

[PERFORM] Query planner gaining the ability to replanning after start of query execution.

I am interested in giving the query planner the ability to replan (or re-rank plans) after query execution has begun, based on the progression of the query so far. Example use case: * A LIMIT 1 query is planned using an expensive scan which the planner expects to return a large number of results

Re: [PERFORM] DB slowness after upgrade from Postgres 9.1 to 9.4

p kirti writes: > We have recently upgraded our project with a huge DB from Postgres v9.1 to > v9.4. The whole system performance has degraded alarmingly after the > upgrade. Simple operations that were taking only a few seconds in Postgres > 9.1 are now taking minutes of time. Are you certain no

[PERFORM] DB slowness after upgrade from Postgres 9.1 to 9.4

Hi all, We have recently upgraded our project with a huge DB from Postgres v9.1 to v9.4. The whole system performance has degraded alarmingly after the upgrade. Simple operations that were taking only a few seconds in Postgres 9.1 are now taking minutes of time. The problem is not specific to one

Re: [PERFORM] overestimate on empty table

Justin Pryzby writes: > On Fri, Nov 10, 2017 at 04:19:41PM -0500, Tom Lane wrote: >> One idea is to say that relpages = reltuples = 0 is only the state that >> prevails for a freshly-created table, and that VACUUM or ANALYZE should >> always set relpages to at least 1 even if the physical size is

Re: [PERFORM] overestimate on empty table

On Fri, Nov 10, 2017 at 04:19:41PM -0500, Tom Lane wrote: > Justin Pryzby writes: > > (or, the opposite of the more common problem) > > As the queued_alters table is typically empty (and autoanalyzed with > > relpages=0), I see "why": > > > ./src/backend/optimizer/util/plancat.c > > |

Re: [PERFORM] overestimate on empty table

Justin Pryzby writes: > As the queued_alters table is typically empty (and autoanalyzed with > relpages=0), I see "why": > ./src/backend/optimizer/util/plancat.c > |if (curpages < 10 && > |rel->rd_rel->relpages == 0 && > |

[PERFORM] overestimate on empty table

(or, the opposite of the more common problem) I wrote this query some time ago to handle "deferred" table-rewriting type promoting ALTERs of a inheritence children, to avoid worst-case disk usage altering the whole heirarchy, and also locking the entire heirarchy against SELECT and INSERT. ts=# e

[PERFORM] Dynamic performance issues

Hello, I was encouraged to write up a few simplified, reproducible performance cases, that occur (similarly) in our production environment. Attached you find a generic script that sets up generic tables, used for the three different cases. While I think at all of them I included the times neede

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

2017-11-08 0:45 GMT+01:00 Tom Lane : > =?UTF-8?Q?Ulf_Lohbr=C3=BCgge?= writes: > > I just ran "check_postgres.pl --action=bloat" and got the following > output: > > ... > > Looks fine, doesn't it? > > A possible explanation is that something is taking an exclusive lock > on some system catalog and

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

=?UTF-8?Q?Ulf_Lohbr=C3=BCgge?= writes: > I just ran "check_postgres.pl --action=bloat" and got the following output: > ... > Looks fine, doesn't it? A possible explanation is that something is taking an exclusive lock on some system catalog and holding it for a second or two. If so, turning on l

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

2017-11-07 22:39 GMT+01:00 Scott Marlowe : > On Tue, Nov 7, 2017 at 2:25 PM, Ulf Lohbrügge > wrote: > > 2017-11-07 20:45 GMT+01:00 Andres Freund : > >> > >> On 2017-11-07 18:48:14 +0100, Ulf Lohbrügge wrote: > >> > Hi, > >> > > >> > 2017-11-07 16:11 GMT+01:00 Andres Freund : > >> > > >> > > Hi, >

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

On Tue, Nov 7, 2017 at 2:25 PM, Ulf Lohbrügge wrote: > 2017-11-07 20:45 GMT+01:00 Andres Freund : >> >> On 2017-11-07 18:48:14 +0100, Ulf Lohbrügge wrote: >> > Hi, >> > >> > 2017-11-07 16:11 GMT+01:00 Andres Freund : >> > >> > > Hi, >> > > >> > > On 2017-11-07 11:11:36 +0100, Ulf Lohbrügge wrote:

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

2017-11-07 20:45 GMT+01:00 Andres Freund : > On 2017-11-07 18:48:14 +0100, Ulf Lohbrügge wrote: > > Hi, > > > > 2017-11-07 16:11 GMT+01:00 Andres Freund : > > > > > Hi, > > > > > > On 2017-11-07 11:11:36 +0100, Ulf Lohbrügge wrote: > > > > I'm using PostgreSQL 9.5.9 on Debian and experience slow e

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

On 2017-11-07 18:48:14 +0100, Ulf Lohbrügge wrote: > Hi, > > 2017-11-07 16:11 GMT+01:00 Andres Freund : > > > Hi, > > > > On 2017-11-07 11:11:36 +0100, Ulf Lohbrügge wrote: > > > I'm using PostgreSQL 9.5.9 on Debian and experience slow execution of > > some > > > basic SET statements. > > > > > >

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

in' is established and the following commands > are > > executed: > > > > SET ROLE 1337; > > SET search_path = tenant1337; > > > > Then the application uses that connection to perform various statements > in > > the database. > > Just to be sur

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

following commands are > executed: > > SET ROLE 1337; > SET search_path = tenant1337; > > Then the application uses that connection to perform various statements in > the database. Just to be sure: You realize bad application code could escape from that, right? >

[PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

application uses that connection to perform various statements in the database. As I'm using a connection pool, every connection that is returned to the pool executes the following commands: RESET ROLE; SET search_path = DEFAULT; My application is a web service that approximately executes so

Re: [PERFORM] Performance loss upgrading from 9.3 to 9.6

On 11/6/17, 9:21 AM, "Justin Pryzby" wrote: > see if statistics improve: > SELECT (SELECT sum(x) FROM unnest(most_common_freqs) x) frac_MCV, > tablename, attname, n_distinct, array_length(most_common_vals,1) n_mcv, > FROM pg_stats WHERE attname~'customers_customer' AND tablename='id'

Re: [PERFORM] Performance loss upgrading from 9.3 to 9.6

> It has now been decided to try upgrading to 9.4 as that is the minimum to > support Django 1.11 (which we are trying to upgrade a backend service to). > The hope is whatever feature we have not configured properly in 9.6 is not > there in 9.4. It's entirely possible whatever is causing your p

Re: [PERFORM] Performance loss upgrading from 9.3 to 9.6

Justin, Thanks for the reply. I changed the statistics on av.customer_id as suggested and the number returned by pg_stats went from 202,333 to 904,097. There are 11.2 million distinct customer_ids on the 14.8 million vehicle records. Rerunning the query showed no significant change in time (6

Re: [PERFORM] Index-Advisor Tools

On Tue, Oct 31, 2017 at 8:06 PM Julien Rouhaud wrote: > On Tue, Oct 31, 2017 at 8:25 PM, Alexandre de Arruda Paes > wrote: > > I will be very happy with a tool(or a stats table) that shows the most > > searched values from a table(since a statistic reset). > As a vendor, I normally stay silent

Re: [PERFORM] Performance loss upgrading from 9.3 to 9.6

On Mon, Nov 06, 2017 at 01:18:00PM +, Adam Torres wrote: > Good morning all, > > We have a problem with performance after upgrading from 9.3 to 9.6 where > certain queries take 9 times longer to run. On our initial attempt to > upgrade, we noticed the system as a whole was taking longer to

[PERFORM] Performance loss upgrading from 9.3 to 9.6

Good morning all, We have a problem with performance after upgrading from 9.3 to 9.6 where certain queries take 9 times longer to run. On our initial attempt to upgrade, we noticed the system as a whole was taking longer to run through normal daily processes. The query with the largest run ti

Re: [PERFORM] Unnecessary DISTINCT while primary key in SQL

On 5 November 2017 at 04:20, 刘瑞 wrote: > CREATE TABLE test_tbl ( k INT PRIMARY KEY, col text) > INSERT into test_tbl select generate_series(1,1000), 'test'; > > SQL with DISTINCT: > test=# explain analyze select distinct col, k from test_tbl order by k limit > 1000; >

[PERFORM] Unnecessary DISTINCT while primary key in SQL

Hi all: I am new in pgsql, I am even new in using mailing list. I send this email just to give a suggestion on performance. I found that if primary key or a colume which has an unique index, is in a select sql，the distinct sometimes is Unnecessary. Actually, the SQL with DISTINCT will runs more sl

Re: [PERFORM] Re: OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

Thank you Gunther for bringing this up. It's been bothering me quite a bit over time as well. Forgive the naive question, but does the query planner's cost estimator only track a single estimate of cost that gets accumulated and compared across plan variants? Or is it keeping a range or probabil

Re: [PERFORM] Re: OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

On 11/3/2017 10:55, legrand legrand wrote: To limit NL usage, wouldn't a modified set of Planner Cost Constants https://www.postgresql.org/docs/current/static/runtime-config-query.html seq_page_cost random_page_cost cpu_t

[PERFORM] Re: OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

To limit NL usage, wouldn't a modified set of Planner Cost Constants https://www.postgresql.org/docs/current/static/runtime-config-query.html seq_page_cost random_page_cost cpu_tuple_cost cpu_index_tuple_cost cpu_ope

Re: [PERFORM] Re: OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

Just throwing out some more innovative ideas. Materialized join tables, I have read somewhere. OK, difficult to keep consistent with transactions. Forget that. But, why not collect statistics on every join that is processed, even if the query is interrupted. Then as more and more plans are ru

Re: [PERFORM] Re: OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

On Fri, Nov 3, 2017 at 5:28 AM, Thomas Kellerer wrote: I do like Oracle's approach with SQL profiles, where you can force the optimizer to try harder to find a good execution plan. I _think_ it even runs the statement with multiple plans and compares the expected outcome with the actual values.

Re: [PERFORM] Re: OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

On Fri, Nov 3, 2017 at 5:28 AM, Thomas Kellerer wrote: > I do like Oracle's approach with SQL profiles, where you can force the > optimizer to try harder to find a good execution plan. I _think_ it even > runs the statement with multiple plans and compares the expected outcome > with the actual va

[PERFORM] Re: OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

Laurenz Albe schrieb am 02.11.2017 um 09:30: > Finally, even though the official line of PostgreSQL is to *not* have > query hints, and for a number of good reasons, this is far from being > an unanimous decision. The scales may tip at some point, though I > personally hope that this point is not

Re: [PERFORM] OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

Gunther wrote: > > Bad choices are almost always caused by bad estimates. > > Granted, there is no way that estimates can ever be perfect. > > ... > > Looking deeper, I would say that wrongly chosen nested loop joins > > often come from an underestimate that is close to zero. > > PostgreSQL already

[PERFORM] Re: OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

Hello, may I suggest you to look at https://github.com/ossc-db/pg_hint_plan that mimics Oracle hints syntax Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To m

Re: [PERFORM] OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

Thanks you for your thoughtful reply, Laurenz (funny that the people interested in this topic are named Laurent and Laurenz :) PostgreSQL doesn't have a way to tell if a query is an OLAP query running against a star schema or a regular OLTP query, it will treat both in the same fashion. right,

Re: [PERFORM] OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

Thanks for your support Laurent. I have an idea on one thing you said: Just adding to your voice. I recently experienced the same issue with a complex multi-table view, including pivots, and was surprised to see all the nested loops everywhere and here is the clue for me: in spite of indice

Re: [PERFORM] OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

Gunther wrote: > But there > is one thing that keeps bothering me both with Oracle and PgSQL. And > that is the preference for Nested Loops. [...] > But the issue is bulk searches, reports, and any analytic queries > scenarios. In those queries Nested Loops are almost always a bad choice, > e

Re: [PERFORM] OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

> -Original Message- > From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance- > ow...@postgresql.org] On Behalf Of Gunther > Sent: Wednesday, November 01, 2017 20:29 > To: pgsql-performance@postgresql.org > Subject: [PERFORM] OLAP/reporting queries fall

[PERFORM] OLAP/reporting queries fall into nested loops over seq scans or other horrible planner choices

Hi, this is Gunther, have been with PgSQL for decades, on an off this list. Haven't been on for a long time making my way just fine. But there is one thing that keeps bothering me both with Oracle and PgSQL. And that is the preference for Nested Loops. Over the years the archives have question

Re: [PERFORM] Cursor vs Set Operation

On Mon, Oct 30, 2017 at 5:51 PM, patibandlakoshal wrote: > From performance standpoint I thought set operation was better than Cursor. > But I found Cursor to be more effective than Set operation. Is there a way > we can force optimizer to use cursor plan. QUERY PLAN You're going to have to be

[PERFORM] Cursor vs Set Operation

>From performance standpoint I thought set operation was better than Cursor. But I found Cursor to be more effective than Set operation. Is there a way we can force optimizer to use cursor plan. QUERY PLAN

Re: [PERFORM] Index-Advisor Tools

On Tue, Oct 31, 2017 at 8:25 PM, Alexandre de Arruda Paes wrote: > I will be very happy with a tool(or a stats table) that shows the most > searched values from a table(since a statistic reset). i.e.: > > table foo (id int, year int) > > top 3 searched value for year field: 2017(500x), 2016(300x)

Re: [PERFORM] Index-Advisor Tools

I have not used it yet, but from the presentation, very promising: https://medium.com/@ankane/introducing-dexter-the-automatic-indexer-for-postgres-5f8fa8b28f27 https://github.com/ankane/dexter -- https://yves.zioup.com gpg: 4096R/32B0F416 -- Sent via pgsql-performance mailing list (pgsql

Re: [PERFORM] Index-Advisor Tools

I will be very happy with a tool(or a stats table) that shows the most searched values from a table(since a statistic reset). i.e.: table foo (id int, year int) top 3 searched value for year field: 2017(500x), 2016(300x), 2015(55x) With this info we can create partial indexes or do a table part

Re: [PERFORM] Index-Advisor Tools

Thanks for reply Antony. But from what I've read, HYPOPG only allows you to create hypothetical indexes, so the DBA can analyze if it brings benefits. What I would like is a tool that from a SQL Query indicates which indexes would be recommended to decrease the response time. Best Regards Neto 20

Re: [PERFORM] Index-Advisor Tools

Hi Neto, maybe HypoPG Can help you: https://github.com/dalibo/hypopg El 31 oct. 2017 2:13 PM, "Neto pr" escribió: > > Hello All I'm researching on Index-Advisor Tools to be applied in SQL > queries. At first I found this: - EnterpriseDB - > https://www.enterprisedb.com/docs/en/9.5/asguide/EDB_

[PERFORM] Index-Advisor Tools

Hello All I'm researching on Index-Advisor Tools to be applied in SQL queries. At first I found this: - EnterpriseDB - https://www.enterprisedb.com/docs/en/9.5/asguide/EDB_Postgres_Advanced_Server_Guide.1.56.html Someone would know of other tools for this purpose. I'd appreciate it if you can help

[PERFORM] Massive insert vs heavy contention in LWLock:buffer_content

I'm working on an application which performs a lot of inserts in 2 large tables. Previously we didn't know about lwlocks, but we're now testing in Amazon RDS Aurora - PostgreSQL (9.6.3). In previous load tests, both local servers and classic Amazon RDS, there was some scalability limit we couldn't

Re: [PERFORM] Cheaper subquery scan not considered unless offset 0

Benjamin Coutu writes: > Please consider the following three semantically equivalent, but differently > written queries: > ... > Queries A + B generate the same plan and execute as follows: > -> Finalize HashAggregate (cost=32879.78..33102.62 rows=22285 > width=12) (actual time=450.72

Re: [PERFORM] Cheaper subquery scan not considered unless offset 0

There is actually another separate issue here apart from that the planner obviously choosing the wrong plan as originally described in my last message, a plan it knows to be more expensive based on cost estimates. Take a look at the way the filter condition is treated differently when estimatin

Re: [PERFORM] Cheaper subquery scan not considered unless offset 0

dth=332) (actual time=0.003..0.003 rows=1 loops=15521) Index Cond: ("ID" = c.item) Planning time: 0.223 ms Execution time: 526.203 ms == Original == From: David Rowley To: Benjamin Coutu Date: Sun, 29 Oct 2017 12:46:42 +0100 Subject: Re: [PERFORM] Cheaper s

Re: [PERFORM] Cheaper subquery scan not considered unless offset 0

On 30 October 2017 at 00:24, Benjamin Coutu wrote: > -> Index Scan using "PK_items_ID" on items a (cost=0.42..1.05 rows=1 > width=332) (actual time=0.003..0.003 rows=1 loops=15521 total=46.563) I've never seen EXPLAIN output like that before. Is this some modified version of PostgreSQL? --

[PERFORM] Cheaper subquery scan not considered unless offset 0

Hello everyone, Please consider the following three semantically equivalent, but differently written queries: Query A: SELECT * FROM items a INNER JOIN ( SELECT item, sum(amount) stock FROM stocktransactions GROUP BY item HAVING sum(amount) >= 1 ) b ON b.item = a. "ID" Query B: SELECT * FR

[PERFORM] Row-level security performance

Hi, I see in the v10 release notes (2017-10-05) that there's been a change to "Improve performance of queries affected by row-level security restrictions". I am using RLS in a Postgres 9.5 database and am seeing some very bad performance when joining tables. Upgrading this DB to v10 shows a hug

Re: [PERFORM] WAL still kept in pg_xlog even long after heavy workload is done

To get the values right, you have to consider the "unit" column in pg_settings. On mine, it is 16M for both min and max wal size. So it would be 1024 x 1024 x 16 x (pg_settings.min_wal_size or pg_settings.max_wal_size) The result of this formula should be close to what you specified in pos

[PERFORM] WAL still kept in pg_xlog even long after heavy workload is done

Hello, We're encountering some problems with WAL growth in production with PostgreSQL 9.6.3 and 9.6.2. From what I know a WAL file can either be recycled(and would be reused) or deleted. We'd like to have better control over the amount of WAL that is kept around. There were a few occasions where w

Re: [PERFORM] performance drop after upgrade (9.6 > 10)

On Tue, Oct 24, 2017 at 04:15:59PM +0200, johannes graën wrote: > Hi Pavel, *, > > you were right with ANALYZing the DB first. However, even after doing > so, I frequently see Seq Scans where an index was used before. This > usually cooccurs with parallelization and looked different before > upgra

Re: [PERFORM] performance drop after upgrade (9.6 > 10)

johannes graën wrote: > Hi Pavel, *, > > you were right with ANALYZing the DB first. However, even after doing > so, I frequently see Seq Scans where an index was used before. This > usually cooccurs with parallelization and looked different before > upgrading to 10. I can provide an example for 1

Re: [PERFORM] performance drop after upgrade (9.6 > 10)

On 2017-10-24 17:18, Justin Pryzby wrote: > You could (re)install PG96 alongside PG10 and run a copy of the DB (even from > your homedir, or on a difference server) and pg_dump |pg_restore the relevant > tables (just be sure to specify the alternate host/port/user/etc as needed for > the restore in

Re: [PERFORM] postgresql tuning with perf

Once again you are speaking about edb port of postgresql. The edb pl sql code is not public. This is not the good place to get support: please ask your edb contract manager. If you want support hère: please rewrite your oracle proc in pl pqsql, share that code and commit strategy ... Postgres doesn

Re: [PERFORM] performance drop after upgrade (9.6 > 10)

On Tue, Oct 24, 2017 at 04:15:59PM +0200, johannes graën wrote: > upgrading to 10. I can provide an example for 10 [1], but I cannot > generate a query plan for 9.6 anymore. You could (re)install PG96 alongside PG10 and run a copy of the DB (even from your homedir, or on a difference server) and p

Re: [PERFORM] postgresql tuning with perf

We record like this: perf record -g -u enterprisedb We report like this: perf report -g -i perf.data Is this what you were looking for? Sorry, we are new to perf so we might be sharing something different as compared to what you asked. We already shared the SP code in the original post. Thanks

Re: [PERFORM] postgresql tuning with perf

Please share how you monitor your perfs. At less duration for each plpgsql proc / oracle proc. Please share your plpgsql code, and commit strategy. (for support with edb please check with your contract manager) -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.ht

Re: [PERFORM] performance drop after upgrade (9.6 > 10)

Hi Pavel, *, you were right with ANALYZing the DB first. However, even after doing so, I frequently see Seq Scans where an index was used before. This usually cooccurs with parallelization and looked different before upgrading to 10. I can provide an example for 10 [1], but I cannot generate a que

Re: [PERFORM] postgresql tuning with perf

Hi Pascal, Do you mean the sample program that acts as the application, do you want me to share that? I can do that, but I guess my post will get blocked. Yes, c1 is the PK. Pls see below: bmdb=# desc dept_new Table "public.dept_new" Column | Type | Modifiers +

Re: [PERFORM] postgresql tuning with perf

The language used for stored procedures is EDBSPL. Even if we dont use EDBSPL, and instead use PLPgPSQL, the performance is still the same. Thanks On 24 October 2017 at 03:29, Steve Atkins wrote: > > > On Oct 23, 2017, at 12:19 PM, Purav Chovatia wrote: > > > > Hello Experts, > > > > We are tr

Re: [PERFORM] postgresql tuning with perf

> On Oct 23, 2017, at 12:19 PM, Purav Chovatia wrote: > > Hello Experts, > > We are trying to tune our postgresql DB using perf. We are running a C > program that connects to postgres DB and calls very simple StoredProcs, one > each for SELECT, INSERT & UPDATE. > > The SPs are very simple.

Re: [PERFORM] postgresql tuning with perf

On 10/23/2017 09:19 PM, Purav Chovatia wrote: > Hello Experts, > > We are trying to tune our postgresql DB using perf. Can you share some of the perf reports, then? > We are running a C program that connects to postgres DB and calls > very simple StoredProcs, one each for SELECT, INSERT & UPDA

Re: [PERFORM] postgresql tuning with perf

Hi, could you providence the code used with PG ? Has table dept_new an index/pk on c1 ? Do you analyze this table after loading it ? Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html -- Sent via pgsql-performance mailing list (pgsql-performa

[PERFORM] postgresql tuning with perf

Hello Experts, We are trying to tune our postgresql DB using perf. We are running a C program that connects to postgres DB and calls very simple StoredProcs, one each for SELECT, INSERT & UPDATE. The SPs are very simple. *SELECT_SP*: CREATE OR REPLACE PROCEDURE query_dept_new(p1 IN numeric, p2 OU

Re: [PERFORM] Low priority batch insert

On Fri, Oct 20, 2017 at 1:10 AM, Jean Baro wrote: > That's my first question in this mailing list! :) Welcome! > Is it possible (node.js connecting to PG 9.6 on RDS) to set a lower priority > to a connection so that that particular process (BATCH INSERT) would have a > low impact on other runnin

[PERFORM] Low priority batch insert

Hi there, That's my first question in this mailing list! :) Is it possible (node.js connecting to PG 9.6 on RDS) to set a lower priority to a connection so that that particular process (BATCH INSERT) would have a low impact on other running processes on PG, like live queries and single inserts/up

Re: [PERFORM] memory allocation

nijam J wrote: > our server is getting too slow again and again Use "vmstat 1" and "iostat -mNx 1" to see if you are running out of memory, CPU capacity or I/O bandwith. Figure out if the slowness is due to slow queries or an overloaded system. Yours, Laurenz Albe -- Sent via pgsql-performanc

[PERFORM] memory allocation

we are using cloud server *this are memory info* free -h total used free sharedbuffers cached Mem: 15G15G 197M 194M 121M14G -/+ buffers/cache: 926M14G Swap: 15G32M15G *this are

Re: [PERFORM] Row level security policy policy versus SQL constraints. Any performance difference?

Thanks for your suggestions. I had pretty much given up on this idea. At first, I had thought there would only be 2 or 3 different constraint cases to consider. I had thought of using distinct credentials for my connection and using RLS to give different cuts on the same table. The different p

Re: [PERFORM] Row level security policy policy versus SQL constraints. Any performance difference?

Tomas Vondra writes: > On 10/17/2017 10:44 PM, Joe Carlson wrote: >> What I was wondering is what is the performance differences between a >> row level security implementation: >> ... >> and an implementation where I add on the constraints as part of each >> select statement: > The main point of

Re: [PERFORM] Row level security policy policy versus SQL constraints. Any performance difference?

Hi, On 10/17/2017 10:44 PM, Joe Carlson wrote: > Hello. > > I have not used row level security policies in the past but am > considering using them for a project in which I would like to restrict > the set returned in a query based on specific fields. This is more as a > convenience issue (for me

[PERFORM] Row level security policy policy versus SQL constraints. Any performance difference?

Hello. I have not used row level security policies in the past but am considering using them for a project in which I would like to restrict the set returned in a query based on specific fields. This is more as a convenience issue (for me) rather than a security issue. What I was wondering i

Re: [PERFORM] 99% time spent in WAL wait events

Kindly ignore this post. It was an oversight - the wait times are in millisec and hence even if we manage to reduce these waits to 0, we will gain only 1000 msec of savings during a workload of 40min. Regards On 16 Oct 2017 7:04 pm, "Purav Chovatia" wrote: Hello, We are running workload on a E

  1   2   3   4   5   6   7   8   9   10   >