Re: [PERFORM] MYSQL Stats

On 01/10/16 01:03, Joe Proietti wrote: Hi, I am relatively new to MYSQL and not really sure I am in the right forum for this. [...] If your data is important to you, then PostgreSQL is safer! I've used both MySQL & PostgreSQL, and that latter is easier to use. Cheers, Gavin -- Sent vi

Re: [PERFORM] Millions of tables

Hi Greg, Please follow the conventions of this mailing list, to avoid confusion - see bottom of this posting for further comments On 26/09/16 17:05, Greg Spiegelberg wrote: Precisely why I shared with the group. I must understand the risks involved. I need to explore if it can be stable a

Re: [PERFORM] Multi processor server overloads occationally with system process while running postgresql-9.4

On 03/10/15 21:39, ajaykbs wrote: I am working in a public company who uses only open source applications and databases. I have a problem with our critical database which is write and read intensive. *version:* Postgresql-9.4 *Hardware:* HP DL980 (8-processor, 80 cores w/o hyper threading, 512G

Re: [PERFORM] extract(year from date) doesn't use index but maybe could?

On 20/04/15 10:29, Tom Lane wrote: Yves Dorfsman writes: What about functions that are simpler such as upper()/lower()? If you think those are simpler, you're much mistaken :-(. For instance, "lower(first_name) = 'yves'" would have to be translated to something like "first_name IN ('yves', 'y

Re: [PERFORM] MusicBrainz postgres performance issues

On 16/03/15 13:07, Tomas Vondra wrote: On 16.3.2015 00:55, mich...@sqlexec.com wrote: Why is 500 connections "insane". We got 32 CPU with 96GB and 3000 max connections, and we are doing fine, even when hitting our max concurrent connection peaks around 4500. At a previous site, we were using 200

Re: [PERFORM] Performance issues

Sorry, it didn't register when I read it! (Probably reading too fast) On Sat, Mar 14, 2015 at 10:06 PM, Gavin Flower wrote: On 14/03/15 13:12, Tomas Vondra wrote: On 14.3.2015 00:28, Vivekanand Joshi wrote: Hi Guys, So here is the full information attached as well as in the link pro

Re: [PERFORM] Performance issues

On 14/03/15 13:12, Tomas Vondra wrote: On 14.3.2015 00:28, Vivekanand Joshi wrote: Hi Guys, So here is the full information attached as well as in the link provided below: http://pgsql.privatepaste.com/41207bea45 I can provide new information as well. Thanks. We still don't have EXPLAIN ANA

Re: [PERFORM] Survey: Max TPS you've ever seen

On 10/02/15 08:30, Luis Antonio Dias de Sá Junior wrote: Hi, A survay: with pgbench using TPS-B, what is the maximum TPS you're ever seen? For me: 12000 TPS. -- Luis Antonio Dias de Sá Junior Important to specify: 1. O/S 2. version of PostgreSQL 3. PostgreSQL configuration 4. hardware conf

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

On 01/10/14 05:54, Jeff Janes wrote: On Mon, Sep 29, 2014 at 7:12 PM, Gavin Flower mailto: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 better estimate o

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

On 30/09/14 12:00, Tom Lane wrote: Simon Riggs writes: The way I'm seeing it, you can't assume the LIMIT will apply to any IndexScan that doesn't have an index condition. If it has just a filter, or nothing at all, just an ordering then it could easily scan the whole index if the stats are wron

Re: [PERFORM] Tuning Postgres for Single connection use

though! :) On Monday, April 14, 2014, Gavin Flower <mailto:gavinflo...@archidevsys.co.nz>> wrote: On 15/04/14 09:46, Nick Eubank wrote: Any rules of thumb for |work_mem|, |maintenance_work_mem|, |shared_buffer|, etc. for a database that DOESN'T anticipate concu

Re: [PERFORM] Tuning Postgres for Single connection use

On 15/04/14 09:46, Nick Eubank wrote: Any rules of thumb for |work_mem|, |maintenance_work_mem|, |shared_buffer|, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions on large tables? All the advice I can find online on tuning (this

Re: [PERFORM] Connection pooling - Number of connections

On 25/03/14 13:23, Brett Wooldridge wrote: On Tue, Mar 25, 2014 at 5:24 AM, Gavin Flower mailto:gavinflo...@archidevsys.co.nz>> wrote: Surely no code changes are required, as one can simply set the min and max pool sizes to be the same? Cheers, Gavin To be sure it

Re: [PERFORM] Connection pooling - Number of connections

On 25/03/14 02:27, Brett Wooldridge wrote: Hi, Brett Wooldridge here, one of the principals of HikariCP. I thought I'd wade into the conversation pool a little myself if you guys don't mind. Speaking to David's point... >> Reaching the maxPoolSize from the minPoolSize means creating the >> co

Re: [PERFORM] Slow query (wrong index used maybe)

On 28/01/14 08:10, bobJobS wrote: My developers have had the same issue. Postgres 9.2.3 on Linux 5.6. The latest Linux kernel is 3.13 (https://www.kernel.org), so I assume 5.6 is a distribution version. So which distribution of Linux are you using? Cheers, Gavin -- Sent via pgsql-perform

Re: [PERFORM] Pg makes nonoptimal choice between two multicolumn indexes with the same columns but in different order.

On 29/12/13 10:03, Kevin Grittner wrote: Michael Kolomeitsev wrote: it is clear for me why t1_b_a_idx is better. The question is: Is postgresql able to see that? For a number of reasons I never consider a bulk load complete until I run VACUUM FREEZE ANALYZE on the table(s) involved. When I t

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

On 21/12/13 05:11, Shaun Thomas wrote: [...] . 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. :) Note some processors have 3 (three) memory channels! And I know of some with 4 memory channels. So it is im

Re: [PERFORM] Postgresql in a Virtual Machine

On 26/11/13 09:01, 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 go. Here at work we were entertaining the idea of running our Postgres datab

Re: [PERFORM] Reasons for choosing one execution plan over another?

On 12/09/13 04:55, Giuseppe Broccolo wrote: Il 11/09/2013 13:16, Mikkel Lauritsen ha scritto: Hi all, I have a number of Postgres 9.2.4 databases with the same schema but with slightly different contents, running on small servers that are basically alike (8-16 GB ram). I think that your answ

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

On 16/05/13 03:52, Heikki Linnakangas wrote: On 15.05.2013 18:31, Shaun Thomas wrote: I've seen conversations on this since at least 2005. There were even proposed patches every once in a while, but never any consensus. Anyone care to comment? Well, as you said, there has never been any consen

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

On 16/05/13 04:23, Craig James wrote: On Wed, May 15, 2013 at 8:31 AM, Shaun Thomas mailto:stho...@optionshouse.com>> wrote: [Inefficient plans for correlated columns] has been a pain point for quite a while. While we've had several discussions in the area, it always seems to just k

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

Anne, please read the comment at the bottom of this post! On 07/05/13 09:46, Anne Rosset wrote: Hi Thomas, It is not a dedicated box (we have Jboss running too). cpu_tuple_cost | 0.01 seq_page_cost | 1 random_page_cost| 4 effective_cache_size

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

On 03/05/13 00:27, Simon Riggs wrote: On 2 May 2013 01:49, Mark Kirkwood wrote: On 02/05/13 02:06, Tom Lane wrote: Mark Kirkwood writes: I am concerned that the deafening lack of any replies to my original message is a result of folk glancing at your original quick reply and thinking... inco

Re: [PERFORM] Are bitmap index scans slow to start?

On 23/02/13 08:05, Nikolas Everett wrote: I can't really help, but I can make it more clear why postgres is choosing a _bitmap_ index scan rather than a regular index scan. With a regular index scan it pumps the index for the locations of the rows that it points to and loads those rows as it f

Re: [PERFORM] Hints (was Poor performance using CTE)

On 28/11/12 15:17, Craig Ringer wrote: On 27/11/2012 3:42 PM, Scott Marlowe wrote: Here here! PostgreSQL is well known for its extensibility and this is the perfect place for hints. I agree with the sentiment and your concerns. However, this doesn't solve the CTE problem. Some people are re

Re: [PERFORM] Poor performance using CTE

On 22/11/12 04:56, Heikki Linnakangas wrote: On 21.11.2012 17:42, Gavin Flower wrote: On 22/11/12 04:32, Andres Freund wrote: On 2012-11-21 10:21:16 -0500, Andrew Dunstan wrote: I wasn't talking about removing it. My point was that if the optimization fence around CTEs is removed a l

Re: [PERFORM] Hints (was Poor performance using CTE)

On 22/11/12 06:28, Craig James wrote: On Wed, Nov 21, 2012 at 9:25 AM, Joe Conway > wrote: On 11/21/2012 08:05 AM, Heikki Linnakangas wrote: > Rather than telling the planner what to do or not to do, I'd much rather > have hints that give the planner

Re: [PERFORM] Poor performance using CTE

On 22/11/12 08:42, Andrew Dunstan wrote: On 11/21/2012 02:30 PM, Gavin Flower wrote: WITH FENCE foo AS (SELECT ...) default? WITHOUT FENCE foo AS (SELECT ...) :-) Nah! I prefer this, but it is too specific to 'WITH', and very unSQL standardish! Alternatively one of the fol

Re: [PERFORM] Poor performance using CTE

On 22/11/12 13:08, Craig Ringer wrote: On 11/22/2012 03:30 AM, Gavin Flower wrote: On 22/11/12 04:56, Heikki Linnakangas wrote: On 21.11.2012 17:42, Gavin Flower wrote: On 22/11/12 04:32, Andres Freund wrote: On 2012-11-21 10:21:16 -0500, Andrew Dunstan wrote: I wasn't talking

Re: [PERFORM] Poor performance using CTE

On 22/11/12 04:32, Andres Freund wrote: On 2012-11-21 10:21:16 -0500, Andrew Dunstan wrote: On 11/21/2012 09:59 AM, Tom Lane wrote: Andrew Dunstan writes: If we're going to do it can we please come up with something more intuitive and much, much more documented than "OFFSET 0"? And if/when we

Re: SOLVED - RE: [PERFORM] Poor performance using CTE

On 15/11/12 15:03, Peter Geoghegan wrote: On 15 November 2012 01:46, Andrew Dunstan wrote: It cuts both ways. I have used CTEs a LOT precisely because this behaviour lets me get better plans. Without that I'll be back to using the "offset 0" hack. Is the "OFFSET 0" hack really so bad? We've be

Re: [PERFORM] Planner sometimes doesn't use a relevant index with IN (subquery) condition

On 12/11/12 22:06, Rafał Rzepecki wrote: This indeed works around the issue. Thanks! On Mon, Nov 12, 2012 at 9:53 AM, ashutosh durugkar wrote: Hey Rafal, SELECT * FROM (SELECT run_id, utilization FROM stats) AS s WHERE run_id IN (SELECT run_id FROM runs WHERE server_id = 515); could you t

Re: [PERFORM] Two identical systems, radically different performance

On 09/10/12 12:40, Craig James wrote: Nobody has commented on the hyperthreading question yet ... does it really matter? The old (fast) server has hyperthreading disabled, and the new (slower) server has hyperthreads enabled. If hyperthreading is definitely NOT an issue, it will save me a trip

Re: [PERFORM] Very long deletion time on a 200 GB database

On 29/02/12 06:06, David Kerr wrote: On 02/27/2012 12:08 AM, Reuven M. Lerner wrote: Hi, everyone. I wanted to thank you again for your help on the huge delete problem that I was experiencing. After a lot of trial and error, we finally came to the conclusion that deleting this much data in the

Re: [PERFORM] Subquery in a JOIN not getting restricted?

On 10/11/11 09:39, Jay Levitt wrote: Kevin Grittner wrote: Jay Levitt wrote: I don't get why the GROUP BY in this subquery forces it to scan the entire users table (seq scan here, index scan on a larger table) when there's only one row in users that can match: Are you sure there's a plan s

Re: [PERFORM] Guide to PG's capabilities for inlining, predicate hoisting, flattening, etc?

On 03/11/11 09:22, Igor Neyman wrote: -Original Message- From: Robert Haas [mailto:robertmh...@gmail.com] Sent: Wednesday, November 02, 2011 11:13 AM To: Tom Lane Cc: Jay Levitt; pgsql-performance@postgresql.org Subject: Re: Guide to PG's capabilities for inlining, predicate hoisting, f

Re: [PERFORM] array_except -- Find elements that are not common to both arrays

On 01/10/11 01:23, Vitalii Tymchyshyn wrote: Since you are using except and not except all, you are not looking at arrays with duplicates. For this case next function what the fastest for me: create or replace function array_except2(anyarray,anyarray) returns anyarray as $$ select ARRAY( ( sele

Re: [PERFORM] RAID Controller (HP P400) beat by SW-RAID?

On 12/09/11 15:10, mark wrote: From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Anthony Presley Sent: Sunday, September 11, 2011 4:45 PM To: pgsql-performance@postgresql.org Subject: [PERFORM] RAID Controller (HP P400) beat by SW-RAID?

Re: [PERFORM] Tsearch2 - bad performance with concatenated ts-vectors

On 02/08/11 18:22, Jan Wielgus wrote: select participant.participant_id from participant participant join person person on person.person_participant_id = participant.participant_id left join registration registration on registration.registration_registered_participant_id = participant.participan

Re: [PERFORM] Trigger or Function

On 01/08/11 19:18, Robert Klemme wrote: On Sat, Jul 30, 2011 at 3:01 AM, Gavin Flower wrote: On 24/07/11 03:58, alan wrote: My first approach would be to remove WeekAvg and MonthAvg from the table and create a view which calculates appropriate values. Thanks Robert, I had to upgrade to

Re: [PERFORM] insert

On 30/07/11 08:14, Kevin Grittner wrote: alan wrote: Can I write a BEFORE ROW trigger for the products table to runs on INSERT or UPDATE to 1. insert a new category& return the new category_id OR 2. return the existing category_id for the (to be inserted row) What would you be using

Re: [PERFORM] Trigger or Function

On 24/07/11 03:58, alan wrote: My first approach would be to remove WeekAvg and MonthAvg from the table and create a view which calculates appropriate values. Thanks Robert, I had to upgrade to 9.0.4 to use the extended windowing features. Here is how I set it up. If anyone sees an issue, please

Re: [PERFORM] Bad query plan

On 25/07/11 02:06, Дмитрий Васильев wrote: I have a problem with poor query plan. My PostgreSQL is "PostgreSQL 8.4.8, compiled by Visual C++ build 1400, 32-bit" installed by EnterpriseDB installer on Windows 7 32 bit. Steps to reproduce: Start with fresh installation and execute the following: