Re: [PERFORM] Batch update query performance

On Fri, Apr 4, 2014 at 5:00 AM, Hans Drexler < hans.drex...@humaninference.com> wrote: > > update t67cdi_nl_cmp_descr set is_grc_002='Y' > > This post contains the data of two runs of the query. the first with > explain analyze. The second run is with explain buffers. Between the > runs, an explic

[PERFORM] Performance regressions in PG 9.3 vs PG 9.0

Hi, We recently upgraded from pg 9.0.5 to 9.3.2 and we are observing much higher load on our hot standbys (we have 3). As you can see from the query plans below, we have some queries that are running 4-5 times slower now, many due to what looks like a bad plan in 9.3. Are there any known issues

Re: [PERFORM] SSI slows down over time

On 05/04/2014 10:25 PM, Ryan Johnson wrote: Hi all, Disclaimer: this question probably belongs on the hackers list, but the instructions say you have to try somewhere else first... toss-up between this list and a bug report; list seemed more appropriate as a starting point. Happy to file a bu

Re: [PERFORM] SSI slows down over time

On 06/04/2014 10:55 AM, Tom Lane wrote: Ryan Johnson writes: I get a strange behavior across repeated runs: each 100-second run is a bit slower than the one preceding it, when run with SSI (SERIALIZABLE). ... So the question: what should I look for to diagnose/triage this problem? In the past

Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

- http://wiki.postgresql.org/wiki/Performance_Optimization - run it on the most powerful machine you can find - get some more memory - get a big (512-1TB) SSD drive - avoid recalculating the same things over and over. if your views have many similar elements, then calculate those first into a par

Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

Excellent. Maybe the last sub-question : Those 3600 mat views do have *indexes*. I guess I will get better performances in *dropping indexes* first, then refresh, then *re-creating indexes*. Are there other way to improve performances (like mat views storage parameters

Re: [PERFORM] performance degradation after launching postgres cluster using pgpool-II

> Before starting the cluster, one bulk updates through java code would cost > about 1 hour to finish, but then it would take twice amount of time. pgpool-II is not very good at handling extended protocol (mostly used in Java). If you need to execute large updates, you'd better to connect to Postg

Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

Hi again Nick. Glad it helped. Generally, I would expect that doing all the A's first, then all the B's, and so on, would be fastest since you can re-use the data from cache. Concurrency when reading isn't generally a problem. Lots of things can read at the same time and it will be nice and

Re: [PERFORM] Batch update query performance

On 04/07/2014 03:06 PM, Albe Laurenz wrote: Hans Drexler wrote: Postgres needs close to 50 minutes to process the same query on the same data. Sometimes, Postgres needs more than 2 hours. The application performs an update query on every row of the table. The exact SQL of this query is: update

Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

Hello, Thanks for this clear explanation ! Then I have a sub-question : Supposed I have 3600 materialised views say 600 mat views from 6 main table. (A,B,C,D,E,F are repetead 600 times with some differences) Is it faster to : 1) parallel refresh 600 time A, then 600 time B etc, OR 2) parallel ref

Re: [PERFORM] Batch update query performance

Hans Drexler wrote: > We are porting an application to PostgreSQL. The appplication already > runs with DB2 (LUW version) and Oracle. One query in particular executes > slower on Postgres than it does on other Database platforms, notably DB2 > LUW and Oracle. (Please understand, we are not comparin

[PERFORM] performance degradation after launching postgres cluster using pgpool-II

Hi, I am a freshman to postgresql, also pgpool-II. I have some performance issues once I bring in the pgpool-II to build the pg cluster. Here I post some system info and the configurations of postgresql and pgpool, hopping you can help me to solve this problem. BTW, I am using the postgres 9.2.4

[PERFORM] Batch update query performance

Summary We are porting an application to PostgreSQL. The appplication already runs with DB2 (LUW version) and Oracle. One query in particular executes slower on Postgres than it does on other Database platforms, notably DB2 LUW and Oracle. (Please understand, we are not comparing databases here, we

Re: [PERFORM] The same query - much different runtimes

On Mon, Apr 7, 2014 at 3:55 PM, Johann Spies wrote: > > > I then ran the query and the result was produced in about the same time as > (2945 ms). > > What can cause such a huge discrepancy? > May be when you reran the query, most of the data blocks were cached either in the shared buffers or the

Re: [PERFORM] SSI slows down over time

On 06/04/2014 4:30 AM, Heikki Linnakangas wrote: On 04/06/2014 05:25 AM, Ryan Johnson wrote: I've tried linux perf, but all it says is that lots of time is going to LWLock (but callgraph tracing doesn't work in my not-bleeding-edge kernel). Make sure you compile with the "-fno-omit-frame-point

Re: [PERFORM] PGSQL 9.3 - Materialized View - multithreading

On 04 Apr 2014, at 18:29, Nicolas Paris wrote: > Hello, > > My question is about multiprocess and materialized View. > http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html > I (will) have something like 3600 materialised views, and I would like to > know the way to refresh

[PERFORM] The same query - much different runtimes

While waiting for a query to finish (activated through a web interface), I ran the same query using psql through a ssh-connection with much different runtimes. I have configured the server to log queries taking more than five seconds and in the log the query for which I waited was logged as: 2014