Re: [PERFORM] Problems with hash join over nested loop

On Mon, Oct 28, 2013 at 6:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jim Nasby jna...@enova.com writes: I've been working on trying to normalize a table that's got a bunch of text fields. Normalizing the first 4 has been a non-issue. But when I try and normalize 2 additional fields a bunch of

Re: [PERFORM] Trees: integer[] outperformed by ltree

On Tue, Nov 5, 2013 at 6:25 AM, Jan Walter j...@commontongue.com wrote: Hi, I am in a need of a very robust (esp. fast in read, non-blocking in update) tree structure storage (95% trees are of depth 4, current max. is 12). We have 10k-100k trees now, millions in the future. I made many

Re: [PERFORM] Trees: integer[] outperformed by ltree

On Tue, Nov 5, 2013 at 11:30 AM, Merlin Moncure mmonc...@gmail.com wrote: On Tue, Nov 5, 2013 at 6:25 AM, Jan Walter j...@commontongue.com wrote: Hi, I am in a need of a very robust (esp. fast in read, non-blocking in update) tree structure storage (95% trees are of depth 4, current max

Re: [PERFORM] Trees: integer[] outperformed by ltree

On Tue, Nov 5, 2013 at 3:52 PM, Jan Walter j...@commontongue.com wrote: On 5.11.2013 20:51, Merlin Moncure wrote: On Tue, Nov 5, 2013 at 11:30 AM, Merlin Moncure mmonc...@gmail.com wrote: On Tue, Nov 5, 2013 at 6:25 AM, Jan Walter j...@commontongue.com wrote: Hi, I am in a need of a very

Re: [PERFORM] Trees: integer[] outperformed by ltree

On Tue, Nov 5, 2013 at 6:27 PM, Jan Walter j...@commontongue.com wrote: On 5.11.2013 23:19, Merlin Moncure wrote: On Tue, Nov 5, 2013 at 3:52 PM, Jan Walter j...@commontongue.com wrote: On 5.11.2013 20:51, Merlin Moncure wrote: On Tue, Nov 5, 2013 at 11:30 AM, Merlin Moncure mmonc...@gmail.com

Re: [PERFORM] Slow index scan on B-Tree index over timestamp field

On Sun, Nov 3, 2013 at 4:05 PM, Caio Casimiro casimiro.lis...@gmail.com wrote: System Information: OS: Slackware 14.0 Postgresql Version: 9.3 Beta2 This probably doesn't have anything to do with your problem, but it's long past time to migrate from the beta to the production 9.3. merlin --

Re: [PERFORM] postgresql recommendation memory

On Sat, Nov 2, 2013 at 1:54 PM, Евгений Селявка evg.selya...@gmail.com wrote: Please help with advice! Server HP ProLiant BL460c G1 Architecture: x86_64 CPU op-mode(s):32-bit, 64-bit Byte Order:Little Endian CPU(s):8 On-line CPU(s) list: 0-7

Re: [PERFORM] Horrific time for getting 1 record from an index?

On Tue, Nov 12, 2013 at 6:22 PM, Jim Nasby jna...@enova.com wrote: On 11/12/13 6:17 PM, Jeff Janes wrote: I verified that this is the case--the empty pages remain linked in the tree until a vacuum removes them. But walking through empty leaf pages is way faster than resolving pages full of

Re: [PERFORM] UNION versus SUB SELECT

On Thu, Nov 21, 2013 at 2:31 PM, desmodemone desmodem...@gmail.com wrote: Hi Robert, could you try with exists ? SELECT c.* FROM contacts c WHERE exists ( SELECT 1 FROM phone p WHERE p.addr =? and p.contact_id=c.id ) OR exists (SELECT 1 FROM email e WHERE e.addr = ? and

Re: [PERFORM] Postgresql in a Virtual Machine

On Mon, Nov 25, 2013 at 2:01 PM, Lee Nguyen leemob...@gmail.com wrote: Hi, Having attended a few PGCons, I've always heard the remark from a few presenters and attendees that Postgres shouldn't be run inside a VM. That bare metal is the only way to go. Here at work we were entertaining the

Re: [PERFORM] Postgresql in a Virtual Machine

On Mon, Nov 25, 2013 at 4:57 PM, David Lang da...@lang.hm wrote: On Mon, 25 Nov 2013, Merlin Moncure wrote: On Mon, Nov 25, 2013 at 2:01 PM, Lee Nguyen leemob...@gmail.com wrote: Hi, Having attended a few PGCons, I've always heard the remark from a few presenters and attendees

Re: [PERFORM] Postgresql in a Virtual Machine

On Tue, Nov 26, 2013 at 11:31 AM, Josh Berkus j...@agliodbs.com wrote: On 11/25/2013 12:01 PM, Lee Nguyen wrote: Hi, Having attended a few PGCons, I've always heard the remark from a few presenters and attendees that Postgres shouldn't be run inside a VM. That bare metal is the only way to

Re: [PERFORM] Parallel Select query performance and shared buffers

On Wed, Nov 27, 2013 at 2:28 AM, Metin Doslu me...@citusdata.com wrote: We have several independent tables on a multi-core machine serving Select queries. These tables fit into memory; and each Select queries goes over one table's pages sequentially. In this experiment, there are no indexes or

Re: [PERFORM] Parallel Select query performance and shared buffers

On Tue, Dec 10, 2013 at 2:06 PM, Claudio Freire klaussfre...@gmail.com wrote: On Tue, Dec 10, 2013 at 5:03 PM, Merlin Moncure mmonc...@gmail.com wrote: Also, can I see a typical 'top' during poor scaling count(*) activity? In particular, what's sys cpu%. I'm guessing it's non trivial

Re: [PERFORM] Debugging shared memory issues on CentOS

On Wed, Dec 11, 2013 at 9:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: Mack Talcott mack.talc...@gmail.com writes: The pattern I am seeing is that postgres processes keep growing in shared (this makes sense as they access more of the shared memory, as you've pointed out) but also process-specific

Re: [PERFORM] Optimal settings for RAID controller - optimized for writes

On Tue, Feb 18, 2014 at 2:41 PM, Tomas Vondra t...@fuzzy.cz wrote: On 18.2.2014 02:23, KONDO Mitsumasa wrote: Hi, I don't have PERC H710 raid controller, but I think he would like to know raid striping/chunk size or read/write cache ratio in writeback-cache setting is the best. I'd like to

Re: [PERFORM] Optimal settings for RAID controller - optimized for writes

On Wed, Feb 19, 2014 at 12:09 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Wed, Feb 19, 2014 at 8:13 AM, Merlin Moncure mmonc...@gmail.com wrote: On Tue, Feb 18, 2014 at 2:41 PM, Tomas Vondra t...@fuzzy.cz wrote: On 18.2.2014 02:23, KONDO Mitsumasa wrote: Hi, I don't have PERC H710

Re: [PERFORM] Optimal settings for RAID controller - optimized for writes

On Sun, Feb 16, 2014 at 11:49 AM, Niels Kristian Schjødt nielskrist...@autouncle.com wrote: Hi, I'm kind of a noob when it comes to setting up RAID controllers and tweaking them so I need some advice here. I'm just about to setup my newly rented DELL R720 12. gen server. It's running a

Re: [PERFORM] Sudden crazy high CPU usage

On Mon, Mar 31, 2014 at 5:25 AM, Niels Kristian Schjødt nielskrist...@autouncle.com wrote: I'm running postgresql 9.3 on a production server. An hour ago, out of the blue, I ran into an issue I have never encountered before: my server started to use CPU as crazy. The server is a standard

Re: [PERFORM] Fast distinct not working as expected

On Fri, Apr 18, 2014 at 3:07 AM, Franck Routier franck.rout...@axege.com wrote: I have found the problem, using this query (found here http://stackoverflow.com/questions/3312929/postgresql-idle-in-transaction-diagnosis-and-reading-pg-locks) select pg_class.relname, pg_locks.transactionid,

Re: [PERFORM] CPU spikes and transactions

On Tue, May 13, 2014 at 6:04 PM, Dave Owens d...@teamunify.com wrote: Hi, Apologies for resurrecting this old thread, but it seems like this is better than starting a new conversation. We are now running 9.1.13 and have doubled the CPU and memory. So 2x 16 Opteron 6276 (32 cores total),

Re: [PERFORM] Possible performance regression in PostgreSQL 9.2/9.3?

On Wed, Jun 4, 2014 at 8:56 AM, Linos i...@linos.es wrote: Hello, Some days ago I upgraded from 8.4 to 9.3, after the upgrade some queries started performing a lot slower, the query I am using in this example is pasted here: http://pastebin.com/71DjEC21 Considering it is a production

Re: [PERFORM] Possible performance regression in PostgreSQL 9.2/9.3?

On Wed, Jun 4, 2014 at 2:58 PM, Linos i...@linos.es wrote: On 04/06/14 21:36, Merlin Moncure wrote: On Wed, Jun 4, 2014 at 8:56 AM, Linos i...@linos.es wrote: Hello, Some days ago I upgraded from 8.4 to 9.3, after the upgrade some queries started performing a lot slower, the query I am

Re: [PERFORM] CPU load spikes when CentOS tries to reclaim 'cached' memory

On Thu, Jun 5, 2014 at 10:58 AM, Jeff Janes jeff.ja...@gmail.com wrote: This sounds like a kernel problem, probably either the zone reclaim issue, or the transparent huge pages issue. I at first thought maybe same, but I don't think THP was introduced until 2.6.38...OP is running

Re: [PERFORM] CPU load spikes when CentOS tries to reclaim 'cached' memory

On Thu, Jun 5, 2014 at 2:47 PM, Deron fecas...@gmail.com wrote: We saw very similar issues with a CentOS server with 40 cores (32 virtualized) when moving from a physical server to a virtual server (I think it had 128GB RAM). Never had the problem on a physical server. We checked the same

Re: [PERFORM] High CPU load when 'free -m' shows low 'free' memory even though large 'cached' memory still available

On Thu, Jun 5, 2014 at 8:47 AM, Tom Lane t...@sss.pgh.pa.us wrote: Vince Lasmarias vincent.lasmar...@gmail.com writes: For the past few days, we've been seeing unexpected high CPU spikes in our system. Recent reports have suggested that disabling transparent huge page management in your

Re: [PERFORM] CPU load spikes when CentOS tries to reclaim 'cached' memory

On Thu, Jun 5, 2014 at 6:57 PM, Vincent Lasmarias vlasmar...@contigo.com wrote: Thanks for the informative responses and suggestions. My responses below: * Sorry for the double post. I posted the original message using my gmail account and got a is not a member of any of the restrict_post

Re: [PERFORM] OFFSET/LIMIT - Disparate Performance w/ Go application

On Thu, Jun 12, 2014 at 9:58 AM, Tom Lane t...@sss.pgh.pa.us wrote: Matt Silverlock m...@eatsleeprepeat.net writes: Hi all. This might be tricky in so much as there’s a few moving parts (when isn’t there?), but I’ve tried to test the postgres side as much as possible. Trying to work out a

Re: [PERFORM] OFFSET/LIMIT - Disparate Performance w/ Go application

On Thu, Jun 12, 2014 at 2:48 PM, Andreas Joseph Krogh andr...@visena.com wrote: På torsdag 12. juni 2014 kl. 16:58:06, skrev Tom Lane t...@sss.pgh.pa.us: Matt Silverlock m...@eatsleeprepeat.net writes: Hi all. This might be tricky in so much as there���s a few moving parts (when isn���t

Re: [PERFORM] Guidelines on best indexing strategy for varying searches on 20+ columns

On Wed, Jun 25, 2014 at 3:48 AM, Niels Kristian Schjødt nielskrist...@autouncle.com wrote: Hi, I’m running a search engine for cars. It’s backed by a postgresql 9.3 installation. Now I’m unsure about the best approach/strategy on doing index optimization for the fronted search. The

Re: [PERFORM] Volatility - docs vs behaviour?

On Mon, Jun 30, 2014 at 9:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: Craig Ringer cr...@2ndquadrant.com writes: I was unaware that the planner made any attempt to catch users' errors in marking the strictness of functions. I thought it pretty much trusted the user not to lie about the

Re: [PERFORM] Why you should turn on Checksums with SSDs

On Wed, Jul 30, 2014 at 4:01 AM, Tomas Vondra t...@fuzzy.cz wrote: On 30 Červenec 2014, 5:12, Josh Berkus wrote: Explained here: https://www.usenix.org/system/files/conference/fast13/fast13-final80.pdf 13 out of 15 tested SSD's had various kinds of corruption on a power-out. (thanks, Neil!)

Re: [PERFORM] query against pg_locks leads to large memory alloc

On Mon, Aug 18, 2014 at 4:21 PM, Matheus de Oliveira matioli.math...@gmail.com wrote: On Mon, Aug 18, 2014 at 6:01 PM, Dave Owens d...@teamunify.com wrote: max_locks_per_transaction = 9000 max_pred_locks_per_transaction = 4 performance of any query to pg_locks is proportional to the

Re: [PERFORM] postgres 9.3 vs. 9.4

On Thu, Sep 18, 2014 at 4:58 AM, Mkrtchyan, Tigran tigran.mkrtch...@desy.de wrote: 9.3.5: 0.035940END; 9.4beta2: 0.957854END; time being spent on 'END' is definitely suggesting i/o related issues. This is making me very skeptical that postgres is the source

Re: [PERFORM] postgres 9.3 vs. 9.4

On Fri, Sep 19, 2014 at 6:58 PM, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote: On 19/09/14 19:24, Mkrtchyan, Tigran wrote: - Original Message - From: Mark Kirkwood mark.kirkw...@catalyst.net.nz To: Tigran Mkrtchyan tigran.mkrtch...@desy.de Cc: Merlin Moncure mmonc

Re: [PERFORM] Yet another abort-early plan disaster on 9.3

On Sat, Sep 20, 2014 at 1:33 PM, Josh Berkus j...@agliodbs.com wrote: For example, we could increase the estimated cost for an abort-early index scan by 10X, to reflect our weak confidence in its correctness. Has any progress been made on the performance farm? The problem with suggestions

Re: [PERFORM] postgres 9.3 vs. 9.4

On Tue, Sep 23, 2014 at 7:58 AM, Mkrtchyan, Tigran tigran.mkrtch...@desy.de wrote: Hi Merlin, you are right, in 9.4 the debug_assertions are on: # /etc/init.d/postgresql-9.4 start Starting postgresql-9.4 service: [ OK ] # psql -U postgres psql (9.4beta2) Type

Re: [PERFORM] Yet another abort-early plan disaster on 9.3

On Fri, Sep 26, 2014 at 3:06 AM, Simon Riggs si...@2ndquadrant.com wrote: The problem, as I see it, is different. We assume that if there are 100 distinct values and you use LIMIT 1 that you would only need to scan 1% of rows. We assume that the data is arranged in the table in a very

Re: [PERFORM] Yet another abort-early plan disaster on 9.3

On Tue, Sep 30, 2014 at 11:54 AM, Jeff Janes jeff.ja...@gmail.com wrote: On Mon, Sep 29, 2014 at 7:12 PM, Gavin Flower gavinflo...@archidevsys.co.nz wrote: Would it be feasible to get a competent statistician to advise what data to collect, and to analyze it? Maybe it is possible to get a

Re: [PERFORM] Query with large number of joins

On Mon, Oct 20, 2014 at 3:32 PM, Marco Di Cesare marco.dices...@pointclickcare.com wrote: We are using Postgres for the first time after being SQLServer users for a long time so forgive for being noobs. We are using a BI tool that generates a query with an unusually large number of joins.

Re: [PERFORM] Query with large number of joins

On Tue, Oct 21, 2014 at 11:50 AM, Tom Lane t...@sss.pgh.pa.us wrote: Marco Di Cesare marco.dices...@pointclickcare.com writes: COUNT(DISTINCT foxtrot_india.bravo_romeo) Ah. That explains why the planner doesn't want to use a hash aggregation step --- DISTINCT aggregates

[PERFORM] intel s3500 -- hot stuff

I recently sourced a 300gb intel s3500 ssd to do some performance testing. I didn't see a lot of results on the web so I thought I'd post some numbers. Testing machine is my workstation crapbox with 4 cores and 8GB ram (of which about 4 is usable by the ~ 50gb database). The drive cost 260$ at

Re: [PERFORM] intel s3500 -- hot stuff

On Wed, Nov 5, 2014 at 11:40 AM, Merlin Moncure mmonc...@gmail.com wrote: I recently sourced a 300gb intel s3500 ssd to do some performance testing. I didn't see a lot of results on the web so I thought I'd post some numbers. Testing machine is my workstation crapbox with 4 cores and 8GB

Re: [PERFORM] Yet another abort-early plan disaster on 9.3

On Fri, Dec 5, 2014 at 12:46 AM, Simon Riggs si...@2ndquadrant.com wrote: On 30 September 2014 at 05:53, Simon Riggs si...@2ndquadrant.com wrote: On 29 September 2014 16:00, Merlin Moncure mmonc...@gmail.com wrote: On Fri, Sep 26, 2014 at 3:06 AM, Simon Riggs si...@2ndquadrant.com wrote

Re: [PERFORM] intel s3500 -- hot stuff

On Sat, Dec 6, 2014 at 7:08 AM, Bruce Momjian br...@momjian.us wrote: On Wed, Nov 5, 2014 at 12:09:16PM -0600, Merlin Moncure wrote: effective_io_concurrency 1: 46.3 sec, ~ 170 mb/sec peak via iostat effective_io_concurrency 2: 49.3 sec, ~ 158 mb/sec peak via iostat effective_io_concurrency

Re: [PERFORM] 8xIntel S3500 SSD in RAID10 on Dell H710p

On Wed, Dec 10, 2014 at 2:30 AM, Strahinja Kustudić strahin...@nordeus.com wrote: On Wed, Dec 10, 2014 at 4:55 AM, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote: That is interesting: I've done some testing on this type of card with 16 (slightly faster Hitachi) SSD attached. Setting WT and

Re: [PERFORM] working around JSONB's lack of stats?

On Tue, Jan 27, 2015 at 1:06 AM, Josh Berkus j...@agliodbs.com wrote: Folks, Currently, JSONB fields don't have statistics, and estimate a flat 1% selectivity. This can result in poor query plans, and I'm wondering if anyone has a suggested workaround for this short of hacking a new

Re: [PERFORM] Why is PostgreSQL not using my index?

On Mon, Jan 26, 2015 at 10:32 AM, Christian Roche christian.ro...@workshare.com wrote: Bitmap Heap Scan on mixpanel_events_201409 (cost=7663.36..1102862.70 rows=410022 width=949) Recheck Cond: (event_id = ANY ('{3,4,5,6,7,8,9,10,11,373,375,376,318}'::integer[])) - Bitmap Index Scan on

Re: [PERFORM] New server optimization advice

On Fri, Jan 9, 2015 at 1:48 PM, Claudio Freire klaussfre...@gmail.com wrote: On Fri, Jan 9, 2015 at 4:26 PM, Steve Crawford scrawf...@pinpointresearch.com wrote: New hardware is quite different. 2x10-core E5-2660v3 @2.6GHz, 128GB DDR4-2133 RAM and 800GB Intel DC P3700 NVMe PCIe SSD. In

Re: [PERFORM] slow server : s_lock and _bt_checkkeys on perf top

On Wed, Mar 4, 2015 at 1:31 PM, Soni M diptat...@gmail.com wrote: Hello All, Master db size 1.5 TB All postgres 9.1.13 installed from RHEL package. It has streaming replica and slony replica to another servers. Server performance is slower than usual, before that, there's a big query got

Re: [PERFORM] Row estimates off by two orders of magnitude with hstore

On Wed, Jun 10, 2015 at 2:40 PM, Josh Berkus j...@agliodbs.com wrote: On 06/10/2015 11:32 AM, Merlin Moncure wrote: This is a fundamental issue with using 'database in a box' datatypes like hstore and jsonb. They are opaque to the statistics gathering system and so are unable to give

Re: [PERFORM] Row estimates off by two orders of magnitude with hstore

On Wed, Jun 10, 2015 at 12:32 PM, Patrick Krecker patr...@judicata.com wrote: Hi everyone -- I had an issue the other day where a relatively simple query went from taking about 1 minute to execute to taking 19 hours. It seems that the planner chooses to use a materialize sometimes [1] and not

Re: [PERFORM] Row estimates off by two orders of magnitude with hstore

On Wed, Jun 10, 2015 at 3:55 PM, Patrick Krecker patr...@judicata.com wrote: OK. Well, fortunately for us, we have a lot of possible solutions this problem, and it sounds like actually getting statistics for attributes ? 'reference' is not realistic. I just wanted to make sure it wasn't some

Re: [PERFORM] Row estimates off by two orders of magnitude with hstore

On Wed, Jun 10, 2015 at 4:37 PM, Patrick Krecker patr...@judicata.com wrote: On Wed, Jun 10, 2015 at 2:08 PM, Merlin Moncure mmonc...@gmail.com wrote: On Wed, Jun 10, 2015 at 3:55 PM, Patrick Krecker patr...@judicata.com wrote: OK. Well, fortunately for us, we have a lot of possible solutions

Re: [PERFORM] Slow query: Postgres chooses nested loop over hash join, whery by hash join is much faster, wrong number of rows estimated

On Thu, Jun 11, 2015 at 7:18 PM, Sasa Vilic sasavi...@gmail.com wrote: Hi, I have a query that takes ridiculously long to complete (over 500ms) but if I disable nested loop it does it really fast (24.5ms) Here are links for * first request (everything enabled):

Re: [PERFORM] Postgres is using 100% CPU

On Mon, Jun 1, 2015 at 12:38 AM, Ashik S L ashiksl...@gmail.com wrote: On 05/30/2015 09:46 AM, Ashik S L wrote: We are using postgres SQL version 8.4.17.. Postgres DB szie is 900 MB and we are inserting 273 rows at once .and each row is of 60 bytes.Every time we insert 16380 bytes of data.

Re: [PERFORM] New server: SSD/RAID recommendations?

On Wed, Jul 1, 2015 at 6:06 PM, Craig James cja...@emolecules.com wrote: We're buying a new server in the near future to replace an aging system. I'd appreciate advice on the best SSD devices and RAID controller cards available today. The database is about 750 GB. This is a warehouse server.

Re: [PERFORM] Hmmm... why does pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?

On Fri, Jul 3, 2015 at 9:48 AM, Graeme B. Bell graeme.b...@nibio.no wrote: Hi everyone, I've written a new open source tool for easily parallelising SQL scripts in postgres. [obligatory plug: https://github.com/gbb/par_psql ] Using it, I'm seeing a problem that I've also seen in other

Re: [PERFORM] New server: SSD/RAID recommendations?

On 07 Jul 2015, at 16:59, Heikki Linnakangas hlinn...@iki.fi wrote: So it lies about fsync()... The next question is, does it nevertheless enforce the correct ordering of persisting fsync'd data? If you write to file A and fsync it, then write to another file B and fsync it too, is it

Re: [PERFORM] New server: SSD/RAID recommendations?

On Tue, Jul 7, 2015 at 11:46 AM, Graeme B. Bell graeme.b...@nibio.no wrote: RAID controllers are completely unnecessary for SSD as they currently exist. Agreed. The best solution is not to buy cheap disks and not to buy RAID controllers now, imho. In my own situation, I had a tight

Re: [PERFORM] Query planner not using indexes with JOIN query and OR clause

On Mon, Jul 13, 2015 at 4:01 PM, Merlin Moncure mmonc...@gmail.com wrote: On Mon, Jul 13, 2015 at 3:54 PM, Ben Hoyt benh...@gmail.com wrote: Hi folks, I have a fairly simple three-table query (pasted below) with two LEFT JOINs and an OR in the WHERE clause that for some reason is doing

Re: [PERFORM] Query planner not using indexes with JOIN query and OR clause

On Mon, Jul 13, 2015 at 3:54 PM, Ben Hoyt benh...@gmail.com wrote: Hi folks, I have a fairly simple three-table query (pasted below) with two LEFT JOINs and an OR in the WHERE clause that for some reason is doing sequential scans on all three tables (two of them large -- several million

Re: [PERFORM] GroupAggregate and Integer Arrays

On Friday, October 23, 2015, David Osborne wrote: > Hi, > > Wondering if anyone could suggest how we could improve the performance of > this type of query? > The intensive part is the summing of integer arrays as far as I can see. > We're thinking there's not much we can do to

Re: [PERFORM] Recursive query performance issue

On Wed, Oct 21, 2015 at 2:45 PM, Pavel Stehule wrote: > 2015-10-21 21:32 GMT+02:00 Jamie Koceniak : >> >> adama_prod=# SHOW shared_buffers; >> >> shared_buffers >> >> >> >> 64GB > > > can you try to increase shared buffers to

Re: [PERFORM] Recursive query performance issue

On Fri, Oct 23, 2015 at 12:45 PM, Jamie Koceniak wrote: > Hi, > > We just had the performance problem again today. > Here is some of the top output. Unfortunately, we don't have perf top > installed. > > top - 16:22:16 up 29 days, 13:00, 2 users, load average: 164.63,

Re: [PERFORM] GroupAggregate and Integer Arrays

On Mon, Oct 26, 2015 at 12:45 PM, David Osborne wrote: > Physical memory is 61GB at the moment. > > work_mem is 1,249,104kB I'm not sure if this query is a candidate because of the function, but you can try progressively cranking work_mem and running explain to see what it'd

Re: [PERFORM] Recursive query performance issue

On Tue, Oct 20, 2015 at 12:34 PM, Jamie Koceniak wrote: > Version: > > --- > > PostgreSQL 9.1.14 on x86_64-unknown-linux-gnu, compiled by gcc (Debian > 4.7.2-5) 4.7.2, 64-bit > >

Re: [PERFORM] Simple delete query is taking too long (never ends)

On Thu, Nov 12, 2015 at 9:48 AM, Craig James <cja...@emolecules.com> wrote: > > On Thu, Nov 12, 2015 at 7:12 AM, Merlin Moncure <mmonc...@gmail.com> wrote: >> >> On Wed, Nov 11, 2015 at 1:33 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> > Massalin Yerzhan

Re: [PERFORM] Recursive query performance issue

On Sat, Nov 14, 2015 at 12:58 AM, Jamie Koceniak wrote: > Had the issue again today. > > Here is vmstat : > procs ---memory-- ---swap-- -io -system-- cpu > r b swpd free buff cache si sobibo in cs us sy id wa > 24

Re: [PERFORM] Simple delete query is taking too long (never ends)

On Wed, Nov 11, 2015 at 1:33 PM, Tom Lane wrote: > Massalin Yerzhan writes: >> I'm having an issue. The query never ends: >> delete from bb_gamelist_league; > > 9 times out of 10, the answer to this type of problem is that you have > some table referencing

Re: [PERFORM] SELECT slows down on sixth execution

On Fri, Oct 16, 2015 at 9:14 PM, Jonathan Rogers wrote: > On 10/16/2015 08:37 AM, Albe Laurenz wrote: >> Jonathan Rogers wrote: Look at the EXPLAIN ANALYZE output for both the custom plan (one of the first five executions) and the generic plan (the one used from

Re: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

On Wed, Oct 7, 2015 at 5:29 AM, FattahRozzaq wrote: > Response from you all are very precious. > > @Merlin, > I'm misunderstood the question. > Yes, I didn't measure it. I only monitor RAM and CPU using htop (I also use Can you be a little more specific. What values did you

Re: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

On Tue, Oct 6, 2015 at 10:10 AM, Scott Marlowe <scott.marl...@gmail.com> wrote: > On Tue, Oct 6, 2015 at 3:33 AM, FattahRozzaq <ssoor...@gmail.com> wrote: >> @Merlin Moncure, I got the calculation using pg_tune. And I modified >> the shared_buffers=24GB and th

Re: [PERFORM] Hmmm... why does pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?

On Tue, Jul 7, 2015 at 3:33 PM, Graeme B. Bell graeme.b...@nibio.no wrote: Hi Merlin, Long story short - thanks for the reply, but you're not measuring anything about the parallelism of code running in a pl/pgsql environment here. You're just measuring whether postgres can parallelise

Re: [PERFORM] Hmmm... why does CPU-intensive pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?

On Wed, Jul 8, 2015 at 12:48 PM, Craig James cja...@emolecules.com wrote: On Tue, Jul 7, 2015 at 10:31 PM, Joshua D. Drake j...@commandprompt.com wrote: On 07/07/2015 08:05 PM, Craig James wrote: No ideas, but I ran into the same thing. I have a set of C/C++ functions that put some

Re: [PERFORM] Hmmm... why does CPU-intensive pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?

On Thu, Jul 9, 2015 at 10:12 AM, Graeme B. Bell graeme.b...@nibio.no wrote: 3. I don't disagree that the benchmark code is objectively 'bad' in the sense that it is missing an important optimisation. Particularly with regards documentation, a patch improving things is much more likely to

Re: [PERFORM] New server: SSD/RAID recommendations?

On Thu, Jul 2, 2015 at 1:00 PM, Wes Vaske (wvaske) wva...@micron.com wrote: Storage Review has a pretty good process and reviewed the M500DC when it released last year. http://www.storagereview.com/micron_m500dc_enterprise_ssd_review The only database-specific info we have available are

Re: [PERFORM] Hmmm... why does CPU-intensive pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?

On Wed, Jul 8, 2015 at 5:38 PM, Craig James cja...@emolecules.com wrote: On Wed, Jul 8, 2015 at 1:27 PM, Andres Freund and...@anarazel.de wrote: On 2015-07-08 13:46:53 -0500, Merlin Moncure wrote: On Wed, Jul 8, 2015 at 12:48 PM, Craig James cja...@emolecules.com wrote: On Tue, Jul 7

Re: [PERFORM] Hmmm... why does CPU-intensive pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?

On Thu, Jul 9, 2015 at 4:44 AM, Graeme B. Bell graeme.b...@nibio.no wrote: On 09 Jul 2015, at 05:38, Tom Lane t...@sss.pgh.pa.us wrote: If you write your is_prime function purely in plpgsql, and don't bother to mark it nonvolatile, *it will not scale*. much for properly written plpgsql; but

Re: [PERFORM] Simple delete query is taking too long (never ends)

On Thu, Nov 12, 2015 at 4:26 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Merlin Moncure <mmonc...@gmail.com> writes: >> On Thu, Nov 12, 2015 at 9:48 AM, Craig James <cja...@emolecules.com> wrote: >>> What about a warning on creation? >>> >>>

Re: [PERFORM] query with pg_trgm sometimes very slow

On Thu, Sep 3, 2015 at 6:19 PM, Claudio Freire wrote: > On Wed, Sep 2, 2015 at 4:29 PM, Jeff Janes wrote: >> On Wed, Sep 2, 2015 at 7:00 AM, Volker Böhm wrote: >>> >>> >>> >>> CREATE INDEX trgm_adresse ON adressen.adresse USING

Re: [PERFORM] query with pg_trgm sometimes very slow

On Tue, Sep 8, 2015 at 4:21 PM, Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > On 09/08/2015 09:15 PM, Merlin Moncure wrote: > ... >>> >>> I just had the exact same problem, and indeed gin fares much better. >> >> >> Also, with 9.5 we will

Re: [PERFORM] shared-buffers set to 24GB but the RAM only use 4-5 GB average

On Mon, Oct 5, 2015 at 9:51 AM, FattahRozzaq wrote: > I have configured postgresql.conf with parameters as below: > > log_destination = 'stderr' > logging_collector = on > log_directory = 'pg_log' > listen_addresses = '*' > log_destination = 'stderr' > logging_collector = on >

Re: [PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

On Thu, Jun 9, 2016 at 8:43 AM, Nicolas Paris <nipari...@gmail.com> wrote: > > > 2016-06-09 15:31 GMT+02:00 Merlin Moncure <mmonc...@gmail.com>: >> >> On Wed, Jun 8, 2016 at 1:04 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: >> > Michael Paquier <mic

Re: [PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

On Wed, Jun 8, 2016 at 1:04 AM, Tom Lane wrote: > Michael Paquier writes: >> On Tue, Jun 7, 2016 at 10:03 PM, Josh Berkus wrote: >>> On 06/07/2016 08:42 AM, Nicolas Paris wrote: Will this 1GO restriction is supposed to

Re: [PERFORM] bad COPY performance with NOTIFY in a trigger

On Sat, Feb 6, 2016 at 6:03 AM, Filip Rembiałkowski wrote: > Thanks for the feedback. > > This patch is my first and obvious approach. > > @Merlin, I'm not sure if I get your idea: > - keep previous behaviour as obligatory? (which is: automatic > de-duplicating of

Re: [PERFORM] bad COPY performance with NOTIFY in a trigger

On Fri, Feb 5, 2016 at 9:33 AM, Filip Rembiałkowski wrote: > patch submitted on -hackers list. > http://www.postgresql.org/message-id/CAP_rwwn2z0gPOn8GuQ3qDVS5+HgEcG2EzEOyiJtcA=vpdeh...@mail.gmail.com > > results after the patch: > > trigger= BEGIN RETURN NULL; END

Re: [PERFORM] bad COPY performance with NOTIFY in a trigger

On Mon, Feb 8, 2016 at 8:35 AM, Merlin Moncure <mmonc...@gmail.com> wrote: > On Sat, Feb 6, 2016 at 6:03 AM, Filip Rembiałkowski > <filip.rembialkow...@gmail.com> wrote: >> Thanks for the feedback. >> >> This patch is my first and obvious approach. >> &

Re: [PERFORM] Odd behavior with indices

On Mon, Feb 29, 2016 at 12:47 PM, Tom Lane wrote: > FWIW, PG >= 9.5 will ignore a LIMIT 1 inside an EXISTS, so that you get > the same plan with or without it. But that does act as an optimization > fence in earlier releases. Does 'offset 0' still work as it did? merlin

Re: [PERFORM] Performance problems with postgres and null Values?

On Thu, Apr 21, 2016 at 4:49 AM, Sven Kerkling wrote: > Can somebody help me with these performance Problem. > > What can I try to solve this? can you explain what the problem actually is? Which query is running slow and how fast do you think it should run? merlin --

Re: [PERFORM] Performant queries on table with many boolean columns

On Sun, Apr 24, 2016 at 3:14 PM, bricklen wrote: > Query plan for the md5() index test: > > Index Scan using lots_of_columns_md5_idx on lots_of_columns > (cost=0.93..3.94 rows=1 width=208) (actual time=0.043..0.043 rows=1 loops=1) >Index Cond:

Re: [PERFORM] Poor disk (virtio) Performance Inside KVM virt-machine vs host machine

On Tue, Apr 26, 2016 at 10:27 AM, Artem Tomyuk wrote: > I didn't compare impact of virtualization on other hypervisors yet. My rule of thumb is 50% hit for 1:1 host:guest. Virtualization is not free. If that's a pain try using 100% native solutions (docker, etc) merlin

Re: [PERFORM] Tuning guidelines for server with 256GB of RAM and SSDs?

On Wed, Jul 6, 2016 at 4:48 PM, Scott Marlowe <scott.marl...@gmail.com> wrote: > On Wed, Jul 6, 2016 at 12:13 PM, Merlin Moncure <mmonc...@gmail.com> wrote: >> Disabling write back cache for write heavy database loads will will >> destroy it in short ord

Re: [PERFORM] Tuning guidelines for server with 256GB of RAM and SSDs?

On Tue, Jul 5, 2016 at 9:50 AM, Kaixi Luo wrote: > Hello, > > I've been reading Mr. Greg Smith's "Postgres 9.0 - High Performance" book > and I have some questions regarding the guidelines I found in the book, > because I suspect some of them can't be followed blindly to the

Re: [PERFORM] DELETE takes too much memory

On Mon, Jul 4, 2016 at 11:35 AM, Kouber Saparev wrote: > I tried to DELETE about 7 million rows at once, and the query went up to 15% > of the RAM (120 GB in total), which pushed some indexes out and the server > load went up to 250, so I had to kill the query. > > The involved

[PERFORM] PSA: upgrade your extensions

I was just troubleshooting a strange performance issue with pg_trgm (greatest extension over) that ran great in testing but poor in production following a 9.6 in place upgrade from 9.2. By poor I mean 7x slower. Problem was resolved by ALTER EXTENSION UPDATE followed by a REINDEX on the impacted

Re: [PERFORM] PSA: upgrade your extensions

On Thu, Feb 2, 2017 at 1:18 AM, Jeff Janes <jeff.ja...@gmail.com> wrote: > On Wed, Feb 1, 2017 at 4:38 AM, Merlin Moncure <mmonc...@gmail.com> wrote: >> >> I was just troubleshooting a strange performance issue with pg_trgm >> (greatest extension over) t

Re: [PERFORM] optimizing immutable vs. stable function calls?

On Mon, Jan 23, 2017 at 9:10 AM, Jim Nasby wrote: > On 1/18/17 6:09 PM, David G. Johnston wrote: >> >> That would not be a productive exercise for me, or most people who just >> want >> some idea of what to expect in terms of behavior when they write and use a >> Stable

Re: [PERFORM] Suggestions for a HBA controller (6 x SSDs + madam RAID10)

On Tue, Feb 21, 2017 at 7:49 AM, Pietro Pugni wrote: > Hi there, > I configured an IBM X3650 M4 for development and testing purposes. It’s > composed by: > - 2 x Intel Xeon E5-2690 @ 2.90Ghz (2 x 8 physical Cores + HT) > - 96GB RAM DDR3 1333MHz (12 x 8GB) > - 2 x 146GB

Re: [PERFORM] Suggestions for a HBA controller (6 x SSDs + madam RAID10)

On Tue, Feb 21, 2017 at 1:40 PM, Wes Vaske (wvaske) wrote: > - HW RAID can give better performance if your drives do not have > a capacitor backed cache (like the MX300) AND the controller has a battery > backed cache. **Consumer drives can often get better

<    5   6   7   8   9   10   11   >