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

On 09/26/2014 01:06 AM, Simon Riggs wrote: On 23 September 2014 00:56, Josh Berkus j...@agliodbs.com wrote: We've hashed that out a bit, but frankly I think it's much more profitable to pursue fixing the actual problem than providing a workaround like risk, such as: a) fixing n_distinct

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

correlation stats based approaches and suggested a risk-weighted cost approach. By risk-weighted you mean just adjusting cost estimates based on what the worst case cost looks like, correct? That seemed to be your proposal from an earlier post. If so, we're in violent agreement here. -- Josh Berkus

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

ought to change algos. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Partitions and work_mem?

because the level of parallelism in postgres is extremely limited, so we can't actually sort 8 partitions at the same time. BTW, 8.4 is EOL. Maybe time to upgrade? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance

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

On 10/10/2014 04:16 AM, Greg Stark wrote: On Thu, Oct 2, 2014 at 8:56 PM, Josh Berkus j...@agliodbs.com wrote: Yes, it's only intractable if you're wedded to the idea of a tiny, fixed-size sample. If we're allowed to sample, say, 1% of the table, we can get a MUCH more accurate n_distinct

Re: [PERFORM] Partitions and work_mem?

backpatches our fixes as they come out. They did in the past, anyway. I just had the impression from your original post that this was a new system; if so, it would make sense to build it on a version of Postgres which wasn't already EOL. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com

[PERFORM] 9.4 performance improvements test

(excluding connections establishing) I suspect this is due to the improvements in writing less to WAL. If so, good work, guys! -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription

Re: [PERFORM] pgtune + configurations with 9.3

of Postgres). Yeah, pgTune is pretty badly out of date. It's been on my TODO list, as I'm sure it has been on Greg's. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription

Re: [PERFORM] Performance bug in prepared statement binding in 9.2?

On 12/31/2013 09:55 AM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: Tom, There's an abbreviated version of this argument in the comments in my proposed patch at http://www.postgresql.org/message-id/11927.1384199...@sss.pgh.pa.us What I'm hoping will happen next

Re: [PERFORM] 9.3 performance issues, lots of bind and parse log entries

Tory, Do you know if your workload involves a lot of lock-blocking, particularly blocking on locks related to FKs? I'm tracing down a problem which sounds similar to yours. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql

Re: [PERFORM] Performance bug in prepared statement binding in 9.2?

On 11/10/2014 10:59 AM, Jeff Janes wrote: On Mon, Nov 10, 2014 at 10:48 AM, Josh Berkus j...@agliodbs.com wrote: On 12/31/2013 09:55 AM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: Tom, There's an abbreviated version of this argument in the comments in my proposed patch at http

Re: [PERFORM] Performance bug in prepared statement binding in 9.2?

On 11/10/2014 11:11 AM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: On 11/10/2014 10:59 AM, Jeff Janes wrote: On Mon, Nov 10, 2014 at 10:48 AM, Josh Berkus j...@agliodbs.com wrote: Did this patch every make it in? Or did it hang waiting for verification? It made it in: commit

[PERFORM] Lock pileup causes server to stall

information when I do: for example, is it ALL queries which are slow or just some of them? However, I thought this list would have some other ideas where to look. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance

Re: [PERFORM] Performance bug in prepared statement binding in 9.2?

think that's explained just by bad plans. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Lock pileup causes server to stall

On 11/10/2014 01:40 PM, Alvaro Herrera wrote: Josh Berkus wrote: All, pg version: 9.3.5 RHEL 6.5 128GB/32 cores Configured with shared_buffers=16GB Java/Tomcat/JDBC application Server has an issue that whenever we get lock waits (transaction lock waits, usually on an FK dependancy

Re: [PERFORM] Lock pileup causes server to stall

constraint. Oh, come on. We had hardly any problems with that patch! ;-) -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [PERFORM] issue in postgresql 9.1.3 in using arrow key in Solaris platform

. How did you build PostgreSQL? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

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

to recreate the original bad plan circumstances. I'll keep you posted on how the patch works for that setup. It would be great to come up with a generic/public test for a bad abort-early situation. Ideas? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing

Re: [PERFORM] Re: [SQL] querying with index on jsonb slower than standard column. Why?

require a lot of replumbing to fix. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

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

On 02/02/2015 05:48 PM, Jim Nasby wrote: On 2/1/15 3:08 PM, Josh Berkus wrote: I'm not clear on what you're suggesting here. I'm discussing how the stats for a JSONB field would be stored and accessed; I don't understand what that has to do with indexing. The JSON problem is similar

[PERFORM] Strange choice of general index over partial index

to 50X slower, because that index frequently gets pushed out of memory. What am I missing? Or is this potentially a planner bug for costing? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes

Re: [PERFORM] Strange choice of general index over partial index

that it's the other issue with Tom mentioned, which is that 9.2 really doesn't take physical index size into account. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] Strange choice of general index over partial index

that I'm seeing the effect Tom has just mentioned. It's not using a bitmapscan in either case; it's a straight indexscan. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

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

On 01/28/2015 03:34 PM, Peter Geoghegan wrote: On Wed, Jan 28, 2015 at 3:03 PM, Josh Berkus j...@agliodbs.com wrote: We already have most_common_elem (MCE) for arrays and tsearch. What if we put JSONB's most common top-level keys (or array elements, depending) in the MCE array? Then we could

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

On 01/28/2015 11:48 AM, Tomas Vondra wrote: On 27.1.2015 08:06, Josh Berkus wrote: Folks, ... On a normal column, I'd raise n_distinct to reflect the higher selecivity of the search terms. However, since @ uses contsel, n_distinct is ignored. Anyone know a clever workaround I don't

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

On 01/28/2015 03:50 PM, Peter Geoghegan wrote: On Wed, Jan 28, 2015 at 3:42 PM, Josh Berkus j...@agliodbs.com wrote: jsonb_col @ '[ key1 ]' or jsonb_col ? 'key1' if in MCE, assign % from MCE otherwise assign 1% of non-MCE % jsonb_col @ '{ key1: value1 }' if in MCE

Re: [PERFORM] Unexpected (bad) performance when querying indexed JSONB column

ON, BUFFERS ON ) so that we can see what the query is actually doing, rather than just what the plan was. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

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

On 01/30/2015 05:34 PM, Jim Nasby wrote: On 1/30/15 2:26 PM, Josh Berkus wrote: This would probably work because there aren't a lot of data structures where people would have the same key:value pair in different locations in the JSON, and care about it stats-wise. Alternatetly, if the same

Re: [PERFORM] Poor performance when deleting from entity-attribute-value type master-table

an EAV database and normalizing it, and so far application throughput is up 500%) -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref

Re: [PERFORM] Bad cost estimate with FALSE filter condition

So ... should I assume my diagnosis is correct? Haven't heard any other suggestions. On 02/27/2015 05:28 PM, Josh Berkus wrote: All: This got posted to pgsql-bugs, but got no attention there[1], so I'm sending it to this list. Test case: createdb bench pgbench -i -s bench \c bench

Re: [PERFORM] MusicBrainz postgres performance issues

3.0 to 3.8 really needs to upgrade soon. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Bad cost estimate with FALSE filter condition

On 03/16/2015 11:26 AM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: So ... should I assume my diagnosis is correct? Haven't heard any other suggestions. I don't see any reason to think this is worth worrying about, or worth spending planner cycles on to produce a cosmetically

[PERFORM] Some performance testing?

All, I currently have access to a matched pair of 20-core, 128GB RAM servers with SSD-PCI storage, for about 2 weeks before they go into production. Are there any performance tests people would like to see me run on these? Otherwise, I'll just do some pgbench and DVDStore. -- Josh Berkus

[PERFORM] Bad cost estimate with FALSE filter condition

cost for the whole query. Or is there something else at work here? [1] http://www.postgresql.org/message-id/20150225194953.2546.86...@wrigleys.postgresql.org -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org

Re: [PERFORM] Reverse Key Index

scan partially in case of equality comparisons. Seems like a good use for SP-GiST. Go for it! -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] Some performance testing?

, 32 clients) 129 303 transactions per second (read only) 16 895 transactions (read-write) Thanks for that data! I'm glad to see that 3.18 has improved so much. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance

Re: [PERFORM] Some performance testing?

kernels. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Some performance testing?

On 04/07/2015 09:46 AM, Mel Llaguno wrote: FYI - all my tests were conducted using Ubuntu 12.04 x64 LTS (which I believe are all 3.xx series kernels). If it's 3.2 or 3.5, then your tests aren't useful, I'm afraid. Both of those kernels have known, severe, memory management issues. -- Josh

Re: [PERFORM] Some performance testing?

is literally 2X to 5X different between kernels. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Does anyone have python code which digests pgbench -r output?

public benchmark. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

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

such as jsonb/jsquery *) move out of hstore and into more standard relational strucure You forgot: *) Fund a PostgreSQL developer to add selectivity estimation and stats to hstore. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql

Re: [PERFORM] ERROR: missing chunk number 0 for toast value 1821556134 in pg_toast_17881

that this error affects just one compressed value or row, so you're not losing other data, unless it's a symptom of an ongoing problem. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

Re: [PERFORM] 9.5alpha1 vs 9.4

. Thank you for testing! Can you re-run your tests with the fixed schema? How does it look? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org

Re: [PERFORM] Sudden connection and load average spikes with postgresql 9.3

overwriting the same rows on Z? * is that autovacuum a regular autovacuum, or is it to prevent wraparound? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] Sudden connection and load average spikes with postgresql 9.3

you have a driver, kernel, Linux memory management, or IO stack issue. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [PERFORM] PostgreSQL disk fragmentation causes performance problems on Windows

bunch of performance testing. If you have the budget for this, then please let's talk about it because right now nobody is working on it. Note that this could be a dead end; it's possible that preallocating large extents could cause worse problems than the current fragmentation issues. -- Josh

Re: [PERFORM] PostgreSQL disk fragmentation causes performance problems on Windows

On 05/21/2015 01:39 PM, Andres Freund wrote: On 2015-05-21 11:54:40 -0700, Josh Berkus wrote: This has been talked about as a feature, but would require major work on PostgreSQL to make it possible. You'd be looking at several months of effort by a really good hacker, and then a whole bunch

Re: [PERFORM] Sudden connection and load average spikes with postgresql 9.3

throughput look like before/during/after the stalls? The last was the cause the last time I dealt with a situation like yours; it turned out the issue was bad RAID card firmware where the card would lock up whenever the write-through buffer got too much pressure. -- Josh Berkus PostgreSQL Experts Inc

[PERFORM] Strange query stalls on replica in 9.3.9

to execute on the master. So even if the update is blocking the seq scans on the replica (and I can't see why it would), it should only block them for 3 seconds. Anyone seen anything like this? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing

Re: [PERFORM] Strange query stalls on replica in 9.3.9

is irrelevant and it's being affected by planner issues? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Query optimizer plans with very small selectivity estimates

ackers archives for previous threads. Also see Tomas's correlated stats patch submitted for 9.6. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

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

On 10/06/2015 02:33 AM, FattahRozzaq wrote: > @Merlin Moncure, I got the calculation using pg_tune. And I modified > the shared_buffers=24GB and the effective_cache_size=64GB I really need to get Greg to take down pg_tune. It's way out of date. Probably, I should replace it. -- Josh

Re: [PERFORM] Queries Per Second (QPS)

missing 5 years of performance improvements ... -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

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

hat's nice to hear. > Will this 1GO restriction is supposed to increase in a near future ?​ Not planned, no. Thing is, that's the limit for a field in general, not just JSON; changing it would be a fairly large patch. It's desireable, but AFAIK nobody is working on it. -- -- Josh Berkus Red H

Re: [PERFORM] DELETE takes too much memory

e rows? Incidentally, any time I get into deleting large numbers of rows, I generally find it faster to rebuild the table instead ... -- -- Josh Berkus Red Hat OSAS (any opinions are my own) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription

Re: [PERFORM] Auto generate number in Postgres-9.1.

at's not how it works, normally. I'd suggest adding an ON TRUNCATE trigger to the table. -- Josh Berkus Containers & Databases Oh My! -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

<    5   6   7   8   9   10