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

-ending "hints vs. no hints" discussion would be, to think about integrating the existing "pg_hint_plan" as a standard contrib module) Thomas -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html -- Sent via pgsql-performance mailing list

Re: [PERFORM] partitioning materialized views

won't help with deletes, unfortunately. -- Shaun M Thomas - 2ndQuadrant PostgreSQL Training, Services and Support shaun.tho...@2ndquadrant.com | www.2ndQuadrant.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] partitioning materialized views

the data at once. Let's face it, 3-4 hours is still a ton of data transfer and calculation. -- Shaun M Thomas - 2ndQuadrant PostgreSQL Training, Services and Support shaun.tho...@2ndquadrant.com | www.2ndQuadrant.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgre

Re: [PERFORM] Postgres not using all RAM (Huge Page activated on a 96GB RAM system)

g use of set theory within a database, for example, you might be getting 100x less throughput than you could otherwise attain. If it's not proprietary in some way, or you can obfuscate it into a test case, we can probably help then. As it stands, there isn't enough to go on. --

Re: [PERFORM] Capacitors, etc., in hard drives and SSD for DBMS machines...

t closest to the data (so that there are fewer potential elements to consider for failure cases) -- Thomas SAMSON

Re: [PERFORM] Architectural question

stream them to the client. The application always needs to read the whole blob into memory before it can be used. This might put some memory pressure on the application server. Thomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Why is now()::date so much faster than current_date

Hello, I stumbled over this answer: http://stackoverflow.com/a/9717125/330315 and this sounded quite strange to me. So I ran this on my Windows laptop with Postgres 9.4.5, 64bit and indeed now()::date is much faster than current_date: explain analyze select current_date from generate_

Re: [PERFORM] SELECT slows down on sixth execution

postgresql.org/documentation/publicapi/org/postgresql/PGStatement.html#setPrepareThreshold%28int%29 As I don't think JDBC is using anything "exotic" I would be surprised if this can't be changed with other programming environments also. Thomas -- Sent via pgsql-performance m

Re: [PERFORM] Having some problems with concurrent COPY commands

ct they're not exceptionally useful anymore. :) As a side note, using INSERT instead scales almost exactly linearly. This would be useful, except that INSERT is already at least a magnitude slower than COPY. Hah. -- Shaun Thomas bonesmo...@gmail.com http://bonesmoses.org/ -- Sent via pgsq

Re: [PERFORM] Having some problems with concurrent COPY commands

t stuck, because this seems incredibly wrong. I'll keep digging. -- Shaun Thomas bonesmo...@gmail.com http://bonesmoses.org/ -- 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] Having some problems with concurrent COPY commands

'/tmp/loadtest1.csv'" &>/dev/null & psql -c "COPY test_copy2 FROM '/tmp/loadtest1.csv'" &>/dev/null & psql -c "COPY test_copy3 FROM '/tmp/loadtest1.csv'" &>/dev/null & psql -c "COPY test_copy4 FROM '/tm

[PERFORM] Having some problems with concurrent COPY commands

e? I also know 9.5 underwent a lot of locking improvements, so it might not be relevant. I just haven't gotten a chance to repeat my tests with 9.5 just yet. -- Shaun Thomas bonesmo...@gmail.com

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

Graeme B. Bell schrieb am 09.07.2015 um 11:44: > I don't recall seeing a clear statement telling me I should mark pl/pgsql > functions nonvolatile wherever possible or throw all performance and > scalability out the window. From: http://www.postgresql.org/docs/current/static/xfunc-volatility.html

Re: [PERFORM] Performance issues

Tomas Vondra schrieb am 17.03.2015 um 15:43: > On 17.3.2015 15:19, Thomas Kellerer wrote: >> Tomas Vondra schrieb am 17.03.2015 um 14:55: >>> (2) using window functions, e.g. like this: >>> >>> SELECT * FROM ( >>>SELECT *, &g

Re: [PERFORM] Performance issues

Tomas Vondra schrieb am 17.03.2015 um 14:55: > (2) using window functions, e.g. like this: > > SELECT * FROM ( >SELECT *, > ROW_NUMBER() OVER (PARTITION BY touchpoint_execution_id >ORDER BY FROM max_creation_dt) AS rn >FROM s_f_touc

Re: [PERFORM] MusicBrainz postgres performance issues

found. Otherwise this will simply stick around as yet another unsolved performance problem Thomas -- 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] Reverse Key Index

x to speed up a "LIKE '%abc%'" (something Oracle can't do at all) without having to worry about obfuscation layers (aka ORM). Thomas -- 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] Reverse Key Index

: why do you think you need such an index? Do you have any performance problems with the existing BTree index? If yes, which problem exactly? Thomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.

Re: [PERFORM] pgtune + configurations with 9.3

Alexey, The issue is not that 8GB is the maximum. You *can* set it higher. What I'm saying, and I'm not alone in this, is that setting it higher can actually decrease performance for various reasons. Setting it to 25% of memory on a system with 512GB of RAM for instance, would be tantamount to

Re: [PERFORM] pgtune + configurations with 9.3

Alexey, The issue is that the 1/4 memory suggestion hasn't been a recommendation in quite a while. Now that much larger amounts of RAM are readily available, tests have been finding out that more than 8GB of RAM in shared_buffers has diminishing or even worse returns. This is true for any versi

Re: [PERFORM] pgtune + configurations with 9.3

> 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. Yeah. And unfortunately the recommendations it gives have been spreading. Take a look at the online version: http://pgtune.leopard.in.ua/ I entered a pretty typical 92GB system, and it r

Re: [PERFORM] Turn off Hyperthreading! WAS: 60 core performance with 9.3

until 3.11+, but the worst of the behavior was solved before that. Bugs in kernel cache page aging algorithms are bad, m'kay? -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com

Re: [PERFORM] Turn off Hyperthreading! WAS: 60 core performance with 9.3

-9.2 systems encountered high CPU counts. I somehow doubt Intel would allow their HT architecture to regress so badly from Nehalem, which is almost 3-generations old at this point. This smells like something in the software stack, up to and including the Linux kernel. -- Shaun Thomas OptionsHouse

Re: [PERFORM] Window functions, partitioning, and sorting performance

n retrieve those kind of results in a few milliseconds. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditio

Re: [PERFORM] Turn off Hyperthreading! WAS: 60 core performance with 9.3

off turbo, or forgot to disable power saving features. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditio

Re: [PERFORM] Query Performance question

Magers, James, 14.07.2014 15:18: > Thank you. I executed the query this morning after disabling the scan types. > > I am including links to explain.depesz output for each of the three > variations that I executed. > > indexscan and bitmapscan off: http://explain.depesz.com/s/sIx > seqscan a

Re: [PERFORM] Query Performance question

Magers, James, 14.07.2014 04:20: > Thank you for your feedback. I am attaching the requested information. > While I do not think the query is necessarily inefficient, I believe a > sequence scan would be more efficient. You can try set enable_indexscan = off; set enable_bitmapscan = off;

Re: [PERFORM] how to improve perf of 131MM row table?

data and WHERE clause and see if the planner still goes for the terrible plan. If it does, that would seem like an obvious planner tweak to me. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@o

Re: [PERFORM] how to improve perf of 131MM row table?

an in that case, depending on how high random_page_cost is. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and condi

Re: [PERFORM] how to improve perf of 131MM row table?

's still doing the sequence scan, we'll have to dig deeper. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for te

Re: [PERFORM] how to improve perf of 131MM row table?

obvious immediately. Here's a link for your version: http://www.postgresql.org/docs/9.0/static/sql-explain.html You should still consider upgrading to the latest release of 9.0 too. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionsho

Re: [PERFORM] how to improve perf of 131MM row table?

any rows, the random seeks are throwing off your performance. Of course, I can't confirm that without EXPLAIN output. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com _

Re: [PERFORM] how to improve perf of 131MM row table?

ally match, which queries are performing badly, and so on. Please refer to this page to ask performance related questions: https://wiki.postgresql.org/wiki/Slow_Query_Questions Without much of this information, we'd only be speculating. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd.

Re: [PERFORM] postgres files in use not staying in linux file cache

initely much happier since the upgrade, but the plural of anecdote is not data. :) -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_discl

Re: [PERFORM] postgres files in use not staying in linux file cache

-generic-lts-saucy pseudo-package to at least get onto the 3.11 instead. The 3.2 kernel is pants-on-head retarded; we've had a lot more luck with 3.8 and above. Cheers! -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionsho

Re: [PERFORM] Planner doesn't take indexes into account

f the table. I highly recommend reading up on cardinality and selectivity before creating more indexes. This page in the documentation does a really good job: http://www.postgresql.org/docs/9.3/static/row-estimation-examples.html -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suit

Re: [PERFORM] NFS, file system cache and shared_buffers

at(tm). It can be kinda safe-ish if mounted in sync mode with caching disabled, but I'd never use it on any of our systems. We also have this in the Wiki: http://wiki.postgresql.org/wiki/Shared_Storage -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 606

Re: [PERFORM] PGSQL, checkpoints, and file system syncs

shing. This is a pretty well-known issue on Linux systems with large amounts of RAM. Most VM servers fit that profile, so I'm not surprised it's hurting you. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd | Suite 400 | Chicago IL, 6060

Re: [PERFORM] query against large table not using sensible index to find very small amount of data

ving the indexes around. I can't think of many indexes in our database where I have the low cardinality value as the first column. Databases have an easier time managing many shallow buckets of values, than a few deep ones. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd | Suit

Re: [PERFORM] Slow Count-Distinct Query

in. This will pretty much never be fast, especially with 2M rows involved. I could be wrong about this, and the back-end folks might have a different answer, but I wouldn't hold my breath. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd | Suite 400 | Chicago IL, 60604 312-

Re: [PERFORM] Why shared_buffers max is 8GB?

> On most machines the limit is higher than you'd ever want to set it. I > have a set of servers with 1TB RAM and shared buffers on them is set > to 10G and even that is probably higher than it needs to be. The old > 1/4 of memory advice comes from the days when db server memory > was in the 1 to

Re: [PERFORM] Getting query plan alternatives from query planner?

n extreme cases. Optimization fences are truly forever. Unless of course they're removed. In which case, a bunch of queries that exploited them will suddenly perform a whole lot worse, causing organizations to delay upgrading PostgreSQL. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Su

Re: [PERFORM] window function induces full table scan

Am 03.01.2014 19:04, schrieb Tom Lane: I think you need to read the code around subquery_is_pushdown_safe and qual_is_pushdown_safe some more. regards, tom lane . In general, I'd need to go throught the pg source code which will take some time. For instance, I wanted

Re: [PERFORM] window function induces full table scan

Am 03.01.2014 15:54, schrieb Tom Lane: Thomas Mayer writes: To implement the optimization, subquery_is_pushdown_safe() needs to return true if pushing down the quals to a subquery which has window functions is in fact safe ("quals that only reference subquery outputs that are listed i

Re: [PERFORM] window function induces full table scan

anyhow. (The same applies * to aggregates, which we check for in pull_var_clause below.) */ Assert(!contain_window_function(qual)); " Tom, do you think that these two changes could be sufficient? Do you have a more general aproach in mind? Best regards Thomas Am 03.01.2014 00:55, schrieb T

Re: [PERFORM] window function induces full table scan

tepos FROM checkin_node ) AS tmp_last_position WHERE user_id = 43; -- takes 6574 ms Best regards, Thomas Am 03.01.2014 00:12, schrieb Thomas Mayer: Am 02.01.2014 23:43, schrieb Tom Lane: Jeff Janes writes: On Thu, Jan 2, 2014 at 1:52 PM, Tom Lane wrote: It's possible that i

Re: [PERFORM] window function induces full table scan

check is ignoring the PARTITION-BY-sets of attributes completely. regards, tom lane . Best regards Thomas -- 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] window function induces full table scan

o ensure correctness, WHERE condition attributes which are _not_ element of the PARTITION-BY-set of attributes of _every_ window function of the statement need to be performed after performing the window function. So, the optimizer could check if it's safe or not. Regards, Thomas Am 02.0

[PERFORM] window function induces full table scan

When querying a view with a WHERE condition, postgresql normally is able to perform an index scan which reduces time for evaluation dramatically. However, if a window function is evaluated in the view, postgresql is evaluating the window function before the WHERE condition is applied. This ind

Re: [PERFORM] slow query - will CLUSTER help?

> What are your thoughts on the right way to use SSDs in a RAID to > enhance postgres I/O performance? In an earlier reply, you > indicated one of a "RAID1+0 consisting of several spindles, > NVRAM-based solution (SSD or PCIe card), or a SAN" Well, it's a tiered approach. If you can identify yo

Re: [PERFORM] Recommendations for partitioning?

he results of the insert. WITH saved AS ( INSERT INTO dst SELECT * FROM src WHERE ... RETURNING * ) DELETE FROM src WHERE ...; I'll admit yours is cleaner, though. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@o

Re: [PERFORM] slow query - will CLUSTER help?

table middle ground with 240GB. Of course, don't forget to buy modules in multiples of four, otherwise you're not taking advantage of all the CPU's memory channels. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 606

Re: [PERFORM] slow query - will CLUSTER help?

d to a separate set of disks for a workload of this volume. The amount of writes here will constantly degrade read IO and further increase fetch times. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho

Re: [PERFORM] Regarding Hardware Tuning

couple versions. There are also a ton of considerations regarding new Linux kernel settings. Greg, go tell Packt they need to pay you to write the second edition. ;) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@o

Re: [PERFORM] Unexpected pgbench result

only scans. So theoretically, you might actually see some benefit there. If it were me and I had spindles available, I would just increase the overall size of the pool. It's a lot easier than managing multiple tablespaces. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chica

Re: [PERFORM] slow query - will CLUSTER help?

users have that setup. You're much more likely to get meaningful feedback if you follow the herd. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.pea

Re: [PERFORM] Optimizing a query

nt records are? Add an index to the createdAt column: CREATE INDEX idx_log_createdat ON "Log" (createdAt DESC); Using that, it should get the ten most recent Log records almost immediately, including associated article content. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd.

Re: [PERFORM] Unexpected pgbench result

solution. Your '-s 2000' test was probably running mostly from memory, while the '-s 4000' did not. What you're seeing is the speed your records are being supplied from disk, plus whatever cache effects are there when records are read before they are flushed in favor of m

Re: [PERFORM] autovacuum and dead tuples

mind that the dead tuples are still in the table, but reusable. The free_space column and free_percent is a better description of table bloat from data turnover cleaned up by autovacuum. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676

Re: [PERFORM] How clustering for scale out works in PostgreSQL

bsreejithin wrote on 29.08.2013 18:13: PostgreSQL version was* 8.2*. 8.2 has long been deprecated. For a new system you should use 9.2 (or at least 9.1) Thomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http

Re: [PERFORM] Looks like merge join planning time is too big, 55 seconds

T pg_size_pretty(pg_table_size('pg_class')) AS size_pg_class; SELECT pg_size_pretty(pg_table_size('pg_attribute')) AS size_pg_attribute; Thanks -- Thomas Reiss Consultant Dalibo http://dalibo.com - http://dalibo.org -- 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] PostgreSQL settings for running on an SSD drive

h more controller chips can (purportedly) push upwards of 6GB/s, which is a tad faster than the 800MB/s (measured) of our ancient gen-1 cards. Too many variables. -_- -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __

Re: [PERFORM] Partitions not Working as Expected

of this will probably pan out, but I need to see the limits of how badly I can abuse the database. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/e

Re: [PERFORM] Partitions not Working as Expected

ld tables has a low cost, but it's not zero. With about a dozen of them, query times increase from 0.130ms to 0.280ms for my test case. Not a lot in the long run, but in a OLTP system, it can be fairly noticeable. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 5

Re: [PERFORM] Partitions not Working as Expected

telling devs to use current_date instead of ORM injections, then. Hopefully we can track down and tweak the affected queries on the tables we're partitioning without too much work and QA. Thanks, Tom! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 31

Re: [PERFORM] Partitions not Working as Expected

pg_proc SET provolatile = 'i' WHERE proname = 'date_in'; -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for te

Re: [PERFORM] Partitions not Working as Expected

apparently detrimental to query execution. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

[PERFORM] Partitions not Working as Expected

ong, and I can't seem to come up with a workaround other than, "Ok devs, hard code dates into all of your queries from now on." -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com

Re: [PERFORM] PostgreSQL settings for running on an SSD drive

, and we were getting some really bad query plans unless we *strongly* suggested RPC was cheap. I was afraid I'd have to go lower, but 1 seemed to do the trick. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshous

Re: [PERFORM] PostgreSQL settings for running on an SSD drive

ce * 2 years, 3 months in operation * 1.29PB written * 1.75PB read The load on our system right now is 3.7 on a 24 CPU box while serving 4100 TPS after active trading hours. The FusionIO drive is basically the only reason we can do all of that without a lot of excessive contortions. -- Shaun Thomas

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

that would run into performance problems with a (relatively inexpensive) setup like this. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_discl

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

read queue, which suggests a firmware issue. Often this is because the device was optimized for sequential reads and post lower IOPS than is theoretically possible so they can advertise higher numbers alongside consumer-grade disks. They're Greg's disks though. :) -- Shaun Thomas

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

ZIL would compare. It's still disk-based, but the transparent SSD layer acting as a gigantic passive read and write cache intrigue me. It seems like it would also make a good middle-ground concerning cost vs. performance. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 5

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

ut those situations are few and far between in a heavy transactional setting. Having used NVRAM/SSDs, I could never go back so long as the budget allows us to procure them. A data warehouse? Maybe spindles still have a place there. Heavy transactional system? Not a chance. -- Shaun Thomas

Re: [PERFORM] Thinking About Correlated Columns (again)

On 05/15/2013 03:31 PM, Thomas Kellerer wrote: What happens if you create one index for each column? (instead of one combined index) I just created the combined index to simplify the output. With two indexes, it does the usual bitmap index scan. Even then the row estimate is off by a

Re: [PERFORM] Thinking About Correlated Columns (again)

Shaun Thomas wrote on 15.05.2013 17:31: Hi! This has been a pain point for quite a while. While we've had several discussions in the area, it always seems to just kinda trail off and eventually vanish every time it comes up. A really basic example of how bad the planner is here: CREATE

Re: [PERFORM] Thinking About Correlated Columns (again)

with functional indexes. It would definitely be nice to override the stats with known correlations when possible. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http

[PERFORM] Thinking About Correlated Columns (again)

but never any consensus. Anyone care to comment? -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to th

Re: [PERFORM] Deterioration in performance when query executed in multi threads

Anne Rosset, 06.05.2013 19:00: Postgres version: 9.0.13 Work_mem is set to 64MB Shared_buffer to 240MB Segment_size is 1GB Wal_buffer is 10MB Artifact table: 251831 rows Field_value table: 77378 rows Mntr_subscription: 929071 rows Relationship: 270478 row Folder: 280356 rows Item: 716465 rows

Re: [PERFORM] In progress INSERT wrecks plans on table

mark.kirkw...@catalyst.net.nz wrote on 03.05.2013 00:19: I think the idea of telling postgres that we are doing a load is probably the wrong way to go about this. We have a framework that tries to automatically figure out the best plans...I think some more thought about how to make that understan

Re: [PERFORM] Changing ORDER BY column slows query dramatically

27;t complete enough, so it thinks there are few matches. If that doesn't work and you want a quick, but ugly fix for this, you can create the following index: CREATE INDEX event_20130406_id_desc_tstamp_utc_idx ON event_20130406 (id DESC, tstamp_utc); -- Shaun Thomas OptionsHouse |

Re: [PERFORM] Postgresql.conf file from like 7.x to 9.2

Settings that are new, and could assist in setting up streaming or backups: wal_level max_wal_senders People are getting more and more vocal about increasing cpu_tuple_cost, as the default is apparently too low in practice. Everything else? Salt to taste. -- Shaun Thomas OptionsHouse | 141

Re: [PERFORM] [OT] linux 3.10 kernel will improve ipc,sysv semaphore scalability

autogrouping and increasing sched_migration_cost. If the completely fair scheduler has less locking contention with this patch-set, those tweaks may not even be necessary. I need to see if I can find a system to test on. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604

Re: [PERFORM] Slow query when used in a view

ms to work without major adverse effects. Apparently the tiny table really will be tiny in actual use, so impact should be minimal. I just really don't like using subselects that way. :) Thanks, Tom! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676

[PERFORM] Slow query when used in a view

sure what's going on here. I sense an optimization fence, but I can't see where. Thanks in advance! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http:/

Re: [PERFORM] Two Necessary Kernel Tweaks for Linux Systems

ly ones who ran into this, too. You settled on a much higher setting than we did, but the end result was the same. I wonder how prevalent this will become as more servers are switched over to newer kernels in the next couple of years. Hopefully more people start complaining so they fix

Re: [PERFORM] Two Necessary Kernel Tweaks for Linux Systems

y prefer PostgreSQL's approach... -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email --

Re: [PERFORM] Two Necessary Kernel Tweaks for Linux Systems

squat about internal kernel mechanics. Anyone who actually *isn't* talking out of his ass is free to interject. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ S

Re: [PERFORM] Two Necessary Kernel Tweaks for Linux Systems

itself or combined with Linux terms is tremendously unhelpful. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions

Re: [PERFORM] Two Necessary Kernel Tweaks for Linux Systems

s with the Completely Fair Scheduler, as opposed to the O(1) scheduler. For all intents and purposes, this means 3.0 kernels and above. With a 2.6 kernel, you're fine. Effectively these changes fix what is basically a performance regression compared to older kernels. -- Shaun Thomas Optio

[PERFORM] Two Necessary Kernel Tweaks for Linux Systems

scheduler settings should be modified under different usage scenarios. I just figured I'd share, since we found this info so beneficial. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com ___

[PERFORM] sched_migration_cost for high-connection counts

y this, because frankly, I find it difficult to believe. If legit, high-connection systems would benefit greatly. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http:

Re: [PERFORM] Ubuntu 12.04 / 3.2 Kernel Bad for PostgreSQL Performance

x27;m still curious why only recent 3.2's cause it, but 3.4 don't. That's mighty odd. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/e

Re: [PERFORM] Ubuntu 12.04 / 3.2 Kernel Bad for PostgreSQL Performance

4 levels, but that's expected. I haven't checked 3.0, but other threads I've read suggest it had less problems. Sorry if I wasn't clear. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __

[PERFORM] Ubuntu 12.04 / 3.2 Kernel Bad for PostgreSQL Performance

3.4, but still happens. If anyone has tested against 3.6 or 3.7, I'd love to hear your input. Inconsistent load reports are one thing... strangled performance and inflated CPU usage are quite another. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444

Re: [PERFORM] shared_buffers on ubuntu precise

own tests prove beneficial. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent

Re: [PERFORM] shared_buffers on ubuntu precise

er eclectic and a little wonky, or that nailing down load calculations went awry since the NOHZ stuff started, or both. At this point, I wish we'd stayed on CentOS. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __

Re: [PERFORM] Optimize update query

read IO by reducing shared_buffers to 4GB. Without as many reads, your writes should be much less disruptive. You'll need to restart PG to adopt that change. But I encourage you to keep iostat running in a terminal window so you can watch it for a while. It's very revealing. -- Shaun

Re: [PERFORM] Optimize update query

er. You should also use sar. Just a plain: sar 1 100 Will give you a lot of info on what the CPU is doing. You want that %iowait column to be as low as possible. Keep us updated. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@opti

Re: [PERFORM] Optimize update query

7;s some other write stream happening we're not privy to. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditi

  1   2   3   4   5   6   >