Re: [PERFORM] scale up (postgresql vs mssql)

On 6/20/2012 1:01 AM, Eyal Wilde wrote: Hi, all. this is an obligation from the past: http://archives.postgresql.org/pgsql-performance/2012-05/msg00017.php the same test, that did ~230 results, is now doing ~700 results. that is, BTW even better than mssql. the ultimate solution for that probl

Re: [PERFORM] High load average in 64-core server , no I/O wait and CPU is idle

On 05/24/2012 12:26 AM, Rajesh Kumar. Mallah wrote: - "Claudio Freire" wrote: | From: "Claudio Freire" | To: "Rajesh Kumar. Mallah" | Cc: pgsql-performance@postgresql.org | Sent: Thursday, May 24, 2012 9:23:43 AM | Subject: Re: [PERFORM] High load average in 64-core server , no I/O wait an

Re: [PERFORM] Tuning Postgres 9.1 on Windows

On 5/1/2012 8:06 AM, Merlin Moncure wrote: On Tue, May 1, 2012 at 7:51 AM, Walker, James Les wrote: Exactly, if turning off fsync gives me 100 commits/sec then I know where my bottleneck is and I can attack it. Keep in mind though that I already turned off synchronous commit -- *really* dange

Re: [PERFORM] Tuning Postgres 9.1 on Windows

On 4/30/2012 8:49 AM, Walker, James Les wrote: I’m trying to benchmark Postgres vs. several other databases on my workstation. My workstation is running 64 bit Windows 7. It has 12 gb of RAM and a W3550 @ 3 Ghz. I installed Postgres 9.1 using the windows installer. The data directory is on a 6Gb/

Re: [PERFORM] scale up (postgresql vs mssql)

On 4/18/2012 2:32 AM, Eyal Wilde wrote: hi all, i ran vmstat during the test : [yb@centos08 ~]$ vmstat 1 15 procs ---memory-- ---swap-- -io --system-- -cpu- r b swpd free buff cache si sobibo in cs us sy id wa st 2 0 0 6118620 1605

Re: [PERFORM] scale up (postgresql vs mssql)

On 4/15/2012 7:43 AM, Eyal Wilde wrote: hi, thanks a lot to all of you for your help. (i'm sorry i did not know how to reply to a certain message) i found that the best number of active connections is indeed 8-10. with 8-10 active connections postgresql did ~170 "account-id"s. this is still on

Re: [PERFORM] DBD-Pg prepared statement versus plain execution

On 3/21/2012 6:21 AM, Rafael Martinez wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello We are having some performance problems with an application that uses prepared statement heavily. We have found out that it creates-executes-destroys a prepared statement *per* statement it sends t

Re: [PERFORM] Shared memory for large PostGIS operations

On 03/16/2012 05:30 PM, Kevin Grittner wrote: Brian Hamlin wrote: On Mar 16, 2012, at 7:17 AM, Kevin Grittner wrote: Andy Colson wrote: I tried shared_buffers at both 2400M and 18000M, and it took 4.5 hours both times. ... (weak attempts at humor omitted) Ah, I didn't pick

Re: [PERFORM] Shared memory for large PostGIS operations

his is the best list ever! Thanks all! (especially that poetic Dave Fetter, and that somewhat mean, but helpful, Andy Colson) Shout outs to my friends Garlynn, Nick and Rush (best band ever!). Party, my house, next week! == (Virtually) Brian Hamlin GeoCal OSGeo California Chapter 415-717

Re: [PERFORM] Advice sought : new database server

On 03/04/2012 03:58 AM, Rory Campbell-Lange wrote: I'd be grateful for advice on specifying the new server providing about 230GB of usable storage, 150GB of which is on an LV providing reconfigurable space for the databases which are served off an XFS formatted volume. Do you mean LVM? I've

Re: [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

On 02/26/2012 01:11 PM, Stefan Keller wrote: 2012/2/26 Andy Colson wrote: On 02/25/2012 06:16 PM, Stefan Keller wrote: 1. How can I warm up or re-populate shared buffers of Postgres? 2. Are there any hints on how to tell Postgres to read in all table contents into memory? Yours, Stefan How

Re: [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

On 02/25/2012 06:16 PM, Stefan Keller wrote: 1. How can I warm up or re-populate shared buffers of Postgres? 2. Are there any hints on how to tell Postgres to read in all table contents into memory? Yours, Stefan How about after you load the data, vacuum freeze it, then do something like: S

Re: [PERFORM] set autovacuum=off

On 2/23/2012 2:40 PM, Alessandro Gagliardi wrote: checkpoint_segments can help insert speed, what do you have that set to? 40. Checking http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server it looks like setting that as high as 256 would not necessarily be unreasonable. What do you

Re: [PERFORM] set autovacuum=off

On 2/23/2012 12:38 PM, Alessandro Gagliardi wrote: Does analyze increase the efficiency of inserts or just selects? (I assumed the latter.) Obviously, I will need to analyze sometimes, but That depends on if you have triggers that are doing selects. But in general you are correct, analyze wo

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

On 2/23/2012 12:05 PM, Shaun Thomas wrote: On 02/23/2012 11:56 AM, Greg Spiegelberg wrote: I know there are perils in using ctid but with the LOCK it should be safe. This transaction took perhaps 30 minutes and removed 100k rows and once the table was VACUUM'd afterward it freed up close to 20

Re: [PERFORM] set autovacuum=off

On 2/23/2012 6:34 AM, Thom Brown wrote: On 22 February 2012 23:50, Alessandro Gagliardi wrote: I have a database where I virtually never delete and almost never do updates. (The updates might change in the future but for now it's okay to assume they never happen.) As such, it seems like it migh

Re: [PERFORM] Inserts or Updates

Oh, I knew I'd seen index usage stats someplace. give this a run: select * from pg_stat_user_indexes where relname = 'SuperBigTable'; http://www.postgresql.org/docs/current/static/monitoring-stats.html -Andy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make

Re: [PERFORM] Inserts or Updates

-Original Message- From: Andy Colson [mailto:a...@squeakycode.net] Sent: Tuesday, February 07, 2012 4:47 PM To: Ofer Israeli Cc: pgsql-performance@postgresql.org; Olga Vingurt; Netta Kabala Subject: Re: [PERFORM] Inserts or Updates On 2/7/2012 4:18 AM, Ofer Israeli wrote: Hi all, We

Re: [PERFORM] Inserts or Updates

On 2/7/2012 4:18 AM, Ofer Israeli wrote: Hi all, We are currently “stuck” with a performance bottleneck in our server using PG and we are thinking of two potential solutions which I would be happy to hear your opinion about. Our system has a couple of tables that hold client generated informati

Re: [PERFORM] How to improve insert speed with index on text column

On 1/30/2012 3:27 AM, Saurabh wrote: Hi all, I am using Postgresql database for our project and doing some performance testing. We need to insert millions of record with indexed columns. We have 5 columns in table. I created index on integer only then performance is good but when I created index

Re: [PERFORM] Postgress is taking lot of CPU on our embedded hardware.

On 1/27/2012 10:47 AM, Heikki Linnakangas wrote: On 27.01.2012 15:34, Jayashankar K B wrote: Hi, We are having an embedded system with a freescale m68k architecture based micro-controller, 256MB RAM running a customized version of Slackware 12 linux. It's a relatively modest Hardware. Fascina

Re: [PERFORM] Cursor fetch performance issue

On Tue, 2012-01-24 at 21:47 +0100, Pavel Stehule wrote: Hello 2012/1/24 Tony Capobianco: We are migrating our Oracle warehouse to Postgres 9. This function responds well: pg=# select public.getMemberAdminPrevious_sp2(247815829, 1,'test.em...@hotmail.com', 'email', 'test'); getmemberadminpr

Re: [PERFORM] Can lots of small writes badly hamper reads from other tables?

On 1/24/2012 2:16 PM, Dave Crooke wrote: Hi folks This could be a sheer volume issue, but I though I would ask the wisdom of this forum as to next investigative steps. We use PostgreSQL 8.4.4 which is bundled with our application as a VMware virtual appliance. The bulk of the app's databa

Re: [PERFORM] Performance question 83 GB Table 150 million rows, distinct select

On 11/16/2011 04:53 PM, Tory M Blue wrote: Linux F12 64bit Postgres 8.4.4 16 proc / 32GB 8 disk 15KRPM SAS/Raid 5 (I know!) shared_buffers = 6000MB #temp_buffers = 8MB max_prepared_transactions = 0 work_mem = 250MB

Re: [PERFORM] Optimize the database performance

On 10/17/2011 04:48 AM, Micka wrote: Hi, I've a postgres 9.1 database used for map generating ( tiles ). The system has 24Go RAM and 5 processors. I'm using geoserver to generate the tiles. My data used 8486 MB => psql -d gis -c "SELECT pg_size_pretty(pg_database_size('gis'))" I've carefully

Re: [PERFORM] Allow sorts to use more available memory

On 9/12/2011 1:22 PM, Robert Schnabel wrote: On 9/12/2011 12:57 PM, Shaun Thomas wrote: On 09/12/2011 12:47 PM, Andy Colson wrote: work_mem is not the total a query can use. I believe each step can use that much, and each backend can use it for multiple bits. So if you had two backends, each

Re: [PERFORM] Allow sorts to use more available memory

On 9/12/2011 1:22 PM, Robert Schnabel wrote: On 9/12/2011 12:57 PM, Shaun Thomas wrote: On 09/12/2011 12:47 PM, Andy Colson wrote: work_mem is not the total a query can use. I believe each step can use that much, and each backend can use it for multiple bits. So if you had two backends, each

Re: [PERFORM] Allow sorts to use more available memory

On 9/12/2011 12:57 PM, Shaun Thomas wrote: On 09/12/2011 12:47 PM, Andy Colson wrote: work_mem is not the total a query can use. I believe each step can use that much, and each backend can use it for multiple bits. So if you had two backends, each doing 2 sorts, you'd use 2*2 = 4 * 2GB

Re: [PERFORM] Allow sorts to use more available memory

On 9/12/2011 12:33 PM, Robert Schnabel wrote: The recent "data warehouse" thread made me think about how I use work_mem for some of my big queries. So I tried SET work_mem = '4GB' for a session and got ERROR: 4194304 is outside the valid range for parameter "work_mem" (64 .. 2097151) A bit of s

Re: [PERFORM] Postgres for a "data warehouse", 5-10 TB

On 9/12/2011 12:15 PM, Robert Klemme wrote: On 11.09.2011 19:02, Marti Raudsepp wrote: On Sun, Sep 11, 2011 at 17:23, Andy Colson wrote: On 09/11/2011 08:59 AM, Igor Chudov wrote: By the way, does that INSERT UPDATE functionality or something like this exist in Postgres? You have two options

Re: [PERFORM] Postgres for a "data warehouse", 5-10 TB

On 09/11/2011 09:21 AM, Igor Chudov wrote: On Sun, Sep 11, 2011 at 9:16 AM, Claudio Freire mailto:klaussfre...@gmail.com>> wrote: On Sun, Sep 11, 2011 at 3:59 PM, Igor Chudov mailto:ichu...@gmail.com>> wrote: > Well, right now, my server has twelve 7,200 RPM 2TB hard drives in a RAID

Re: [PERFORM] Postgres for a "data warehouse", 5-10 TB

On 09/11/2011 08:59 AM, Igor Chudov wrote: I do not plan to do a lot of random writing. My current design is that my perl scripts write to a temporary table every week, and then I do INSERT..ON DUPLICATE KEY UPDATE. By the way, does that INSERT UPDATE functionality or something like this exi

Re: [PERFORM] Postgres for a "data warehouse", 5-10 TB

On 09/11/2011 07:35 AM, Igor Chudov wrote: I have been a MySQL user for years, including owning a few multi-gigabyte databases for my websites, and using it to host algebra.com (about 12 GB database). I have had my ups and downs with MySQL. The ups were ease of use and decen

Re: [PERFORM] should i expected performance degradation over time

On 09/10/2011 11:55 AM, Anibal David Acosta wrote: Sometimes I read that postgres performance is degraded over the time and something people talk about backup and restore database solve the problem. It is really true? I have postgres 9.0 on a windows machine with The autovacuum is ON I have s

Re: [PERFORM] Sudden drop in DBb performance

On 09/05/2011 01:45 PM, Scott Marlowe wrote: On Mon, Sep 5, 2011 at 8:08 AM, Gerhard Wohlgenannt wrote: Below please find the results of vmstat 2 over some periode of time .. with normal database / system load. 2 1 1344204 240924 104156 31462484 3500 1906 234 3687 4512 12 3 77 9

Re: [PERFORM] Rather large LA

On 09/05/2011 08:57 AM, Richard Shaw wrote: Hi Andy, It's not a new issue no, It's a legacy system that is in no way ideal but is also not in a position to be overhauled. Indexes are correct, tables are up to 25 million rows. On startup, it hits CPU more than IO, I'll provide some additiona

Re: [PERFORM] Sudden drop in DBb performance

On 09/05/2011 09:39 AM, Tomas Vondra wrote: On 5 Září 2011, 16:08, Gerhard Wohlgenannt wrote: Below please find the results of vmstat 2 over some periode of time .. with normal database / system load. What does a "normal load" mean? Does that mean a time when the queries are slow? Are you sur

Re: [PERFORM] Sudden drop in DBb performance

On 09/05/2011 02:48 AM, Tomas Vondra wrote: On 3 Září 2011, 9:26, Gerhard Wohlgenannt wrote: Dear list, we are encountering serious performance problems with our database. Queries which took around 100ms or less last week now take several seconds. Results of Bonnie++ Version 1.96 ---

Re: [PERFORM] Rather large LA

On 09/05/2011 05:28 AM, Richard Shaw wrote: Hi, I have a database server that's part of a web stack and is experiencing prolonged load average spikes of up to 400+ when the db is restarted and first accessed by the other parts of the stack and has generally poor performance on even simple se

Re: [PERFORM] Slow performance

On 8/31/2011 1:51 PM, Alan Hodgson wrote: On August 31, 2011 11:26:57 AM Andy Colson wrote: When you ran it, did it really feel like 30 seconds? Or did it come right back real quick? Because your report says: > 35.833 ms Thats ms, or milliseconds, or 0.035 seconds. I think the "

Re: [PERFORM] Slow performance

When you ran it, did it really feel like 30 seconds? Or did it come right back real quick? Because your report says: > 35.833 ms Thats ms, or milliseconds, or 0.035 seconds. -Andy On 8/31/2011 8:04 AM, Kai Otto wrote: Hi all, I am running a simple query: SELECT * FROM public.“Frame” Ti

Re: [PERFORM] IN or EXISTS

On 8/30/2011 8:33 PM, Craig Ringer wrote: On 31/08/2011 4:30 AM, Andy Colson wrote: Hi all, I have read things someplace saying not exists was better than not in... or something like that. Not sure if that was for in/exists and not in/not exists, and for a lot of records or not. `EXISTS'

[PERFORM] IN or EXISTS

Hi all, I have read things someplace saying not exists was better than not in... or something like that. Not sure if that was for in/exists and not in/not exists, and for a lot of records or not. Here is my setup: My website has a general table, let say 60k rows. Its mostly read-only. Ev

Re: [PERFORM] Variable versus constrant size tuples

On 8/19/2011 4:03 AM, Krzysztof Chodak wrote: Is there any performance benefit of using constant size tuples? If you are referring to varchar(80) vs text, then no, there is no benefit. -Andy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your

Re: [PERFORM] Raid 5 vs Raid 10 Benchmarks Using bonnie++

On 8/17/2011 1:55 PM, Ogden wrote: On Aug 17, 2011, at 1:48 PM, Andy Colson wrote: On 8/17/2011 1:35 PM, k...@rice.edu wrote: On Wed, Aug 17, 2011 at 01:32:41PM -0500, Ogden wrote: On Aug 17, 2011, at 1:31 PM, k...@rice.edu wrote: On Wed, Aug 17, 2011 at 01:26:56PM -0500, Ogden wrote: I

Re: [PERFORM] Raid 5 vs Raid 10 Benchmarks Using bonnie++

On 8/17/2011 1:35 PM, k...@rice.edu wrote: On Wed, Aug 17, 2011 at 01:32:41PM -0500, Ogden wrote: On Aug 17, 2011, at 1:31 PM, k...@rice.edu wrote: On Wed, Aug 17, 2011 at 01:26:56PM -0500, Ogden wrote: I am using bonnie++ to benchmark our current Postgres system (on RAID 5) with the new on

Re: [PERFORM] DBT-5 & Postgres 9.0.3

On 8/17/2011 10:29 AM, bobbyw wrote: Hi, I know this is an old thread, but I wanted to chime in since I am having problems with this as well. I too am trying to run dbt5 against Postgres. Specifically I am trying to run it against Postgres 9.1beta3. After jumping through many hoops I ultimatel

Re: [PERFORM] Tuning Tips for a new Server

On 8/16/2011 8:35 PM, Ogden wrote: Hope all is well. I have received tremendous help from this list prior and therefore wanted some more advice. I bought some new servers and instead of RAID 5 (which I think greatly hindered our writing performance), I configured 6 SCSI 15K drives with RAID 10

Re: [PERFORM] Parameters for PostgreSQL

On 8/3/2011 11:03 PM, Craig Ringer wrote: great gobs of battery backed write cache DRAM. Now I know what I'm asking Santa for Christmas this year! -Andy -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.

Re: [PERFORM] Postgres performance on Linux and Windows

On 8/3/2011 11:37 AM, Dusan Misic wrote: I had done some testing for my application (WIP) and I had executed same SQL script and queries on real physical 64-bit Windows 7 and on virtualized 64-bit CentOS 6. Both database servers are tuned with real having 8 GB RAM and 4 cores, virtualized having

Re: [PERFORM] Performance

On 4/29/2011 1:55 PM, Greg Smith wrote: James Mansion wrote: Does the server know which IO it thinks is sequential, and which it thinks is random? Could it not time the IOs (perhaps optionally) and at least keep some sort of statistics of the actual observed times? It makes some assumptions ba

Re: [PERFORM] Performance on AIX

On 03/19/2011 04:00 AM, phb07 wrote: Hi all, At Bull company, we want to answer a call for tender from a large company. And we are asked for information about PostgreSQL performance under AIX on Power 7 servers. By chance, has someone some data about this ? Has someone performed a benchmark u

Re: [PERFORM] Fastest pq_restore?

On 3/18/2011 9:38 AM, Kevin Grittner wrote: Andy Colson wrote: On 03/17/2011 09:25 AM, Michael Andreasen wrote: I've been looking around for information on doing a pg_restore as fast as possible. bgwriter_lru_maxpages = 0 I hadn't thought much about that last one -- d

Re: [PERFORM] Fastest pq_restore?

On 03/17/2011 09:25 AM, Michael Andreasen wrote: Hi, I've been looking around for information on doing a pg_restore as fast as possible. It is for a backup machine so I am not interested in anything like crash recovery or anything else that would impact speed of load. I just want to go from n

Re: [PERFORM] Performance issues

startdate <= '1946/1/1'; 2011/3/8 Andy Colson mailto:a...@squeakycode.net>> I have seen really complex geometries cause problems. If you have thousands of points, when 10 would do, try ST_Simplify and see if it doesnt speed things up. -Andy On 3/8/2011 2:4

Re: [PERFORM] Performance issues

I have seen really complex geometries cause problems. If you have thousands of points, when 10 would do, try ST_Simplify and see if it doesnt speed things up. -Andy On 3/8/2011 2:42 AM, Andreas Forø Tollefsen wrote: Hi. Thanks for the comments. My data is right, and the result is exactly wh

Re: [PERFORM] Performance trouble finding records through related records

On 3/3/2011 3:19 AM, sverhagen wrote: Andy Colson wrote: For your query, I think a join would be the best bet, can we see its explain analyze? Here is a few variations: SELECT events_events.* FROM events_events WHERE transactionid IN ( SELECT transactionid FROM

Re: [PERFORM] Performance trouble finding records through related records

On 03/02/2011 06:12 PM, sverhagen wrote: Thanks for your help already! Hope you're up for some more :-) Andy Colson wrote: First off, excellent detail. Second, your explain analyze was hard to read... but since you are not really interested in your posted query, I wont worry about lo

Re: [PERFORM] Performance trouble finding records through related records

On 03/01/2011 06:14 PM, sverhagen wrote: Hi, appreciated mailing list. Thanks already for taking your time for my performance question. Regards, Sander. ===POSTGRESQL VERSION AND ORIGIN=== PostgreSQL 8.3.9 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu3) Installed u

Re: [PERFORM] getting the most of out multi-core systems for repeated complex SELECT statements

On 02/03/2011 10:00 PM, Greg Smith wrote: Andy Colson wrote: Cpu's wont get faster, but HD's and SSD's will. To have one database connection, which runs one query, run fast, it's going to need multi-core support. My point was that situations where people need to

Re: [PERFORM] getting the most of out multi-core systems for repeated complex SELECT statements

On 02/03/2011 04:56 PM, Greg Smith wrote: Scott Marlowe wrote: On Thu, Feb 3, 2011 at 8:57 AM, wrote: Time for my pet meme to wiggle out of its hole (next to Phil's, and a day later). For PG to prosper in the future, it has to embrace the multi-core/processor/SSD machine at the query level

Re: [PERFORM] getting the most of out multi-core systems for repeated complex SELECT statements

On 2/3/2011 9:08 AM, Mark Stosberg wrote: Each night we run over a 100,000 "saved searches" against PostgreSQL 9.0.x. These are all complex SELECTs using "cube" functions to perform a geo-spatial search to help people find adoptable pets at shelters. All of our machines in development in produc

Re: [PERFORM] Get master-detail relationship metadata

On 2/3/2011 5:40 AM, Laszlo Nagy wrote: Hi All, I'm working on a client program that iterates over master-detail relationships in a loop chain. Pseudo code: for row_1 in table_1: table_2 = get_details(row_1,"table2") for row_2 in table_2: row_3 = get_details(row_2,"table3") etc. process_

Re: [PERFORM] High load,

On 1/27/2011 9:09 AM, Michael Kohl wrote: On Thu, Jan 27, 2011 at 4:06 PM, Andy Colson wrote: Have you run each of your queries through explain analyze lately? A code review including checking of queries is on our agenda. You are vacuuming/autovacuuming, correct? Sure :-) Thank you

Re: [PERFORM] High load,

On 1/27/2011 4:31 AM, Michael Kohl wrote: Hi all, we are running a fairly big Ruby on Rails application on Postgres 8.4. Our traffic grew quite a bit lately, and since then we are facing DB performance issues. System load occasionally explodes (around 170 yesterday on a 16 core system), which se

Re: [PERFORM] Queries becoming slow under heavy load

On 1/25/2011 3:37 PM, Anne Rosset wrote: Hi, We are running some performances tests. With a lot of concurrent access, queries get very slow. When there is no load, those queries run fast. We kind of see a trend about these queries: it seems like the ones that become very slow have an ORDER BY o

Re: [PERFORM] Fun little performance IMPROVEMENT...

On 1/21/2011 12:12 PM, gr...@amadensor.com wrote: I was doing a little testing to see how machine load affected the performance of different types of queries, index range scans, hash joins, full scans, a mix, etc. In order to do this, I isolated different performance hits, spinning only CPU, loa

Re: [PERFORM] Migrating to Postgresql and new hardware

On 1/19/2011 6:42 PM, Craig Ringer wrote: On 01/19/2011 05:09 PM, Lars wrote: Thanks for the reply! As others have mentioned, how are you going to be doing your "shards"? Hmm... shards might not have been a good word to describe it. I'll paste what I wrote in another reply: I used sharding a

Re: [PERFORM] Migrating to Postgresql and new hardware

oops, call them database 'a' and database 'b'. -- 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] Migrating to Postgresql and new hardware

On 1/18/2011 4:56 AM, Lars wrote: Hi, We are in the process of moving a web based application from a MySql to Postgresql database. Our main reason for moving to Postgresql is problems with MySql (MyISAM) table locking. We will buy a new set of servers to run the Postgresql databases. The curren

Re: [PERFORM] Possible to improve query plan?

-Original Message- From: Andy Colson [mailto:a...@squeakycode.net] Sent: Monday, 17 January 2011 5:22 p.m. To: Jeremy Palmer Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Possible to improve query plan? First, wow, those are long names... I had a hard time keeping track

Re: [PERFORM] Possible to improve query plan?

On 01/16/2011 09:21 PM, Jeremy Palmer wrote: Hi all, I've come to a dead end in trying to get a commonly used query to perform better. The query is against one table with 10 million rows. This table has been analysed. The table definition is: CREATE TABLE version_crs_coordinate_revision (

Re: [PERFORM] queries with lots of UNIONed relations

On 1/13/2011 4:49 PM, Robert Haas wrote: On Thu, Jan 13, 2011 at 5:47 PM, Andy Colson wrote: I don't believe there is any case where hashing each individual relation is a win compared to hashing them all together. If the optimizer were smart enough to be considering the situation as a

Re: [PERFORM] queries with lots of UNIONed relations

On 1/13/2011 4:42 PM, Robert Haas wrote: On Thu, Jan 13, 2011 at 5:41 PM, Robert Haas wrote: On Thu, Jan 13, 2011 at 5:26 PM, Tom Lane wrote: Robert Haas writes: On Thu, Jan 13, 2011 at 3:12 PM, Jon Nelson wrote: I still think that having UNION do de-duplication of each contributory relat

Re: [PERFORM] Compared MS SQL 2000 to Postgresql 9.0 on Windows

On 12/17/2010 11:37 AM, Robert Haas wrote: On Fri, Dec 17, 2010 at 12:08 PM, Tom Polak wrote: other direction to get good performance, too. You're not going to compare two major database systems across the board and find that one of them is just twice as fast, across the board. They have d

Re: [PERFORM] Compared MS SQL 2000 to Postgresql 9.0 on Windows

On 12/17/2010 11:08 AM, Tom Polak wrote: So, I am back on this topic again. I have a related question, but this might be the correct thread (and please let me know that). The boss is pressing the issue because of the cost of MSSQL. What kind of performance can I expect out of Postgres compare t

Re: [PERFORM] Help with bulk read performance

On 12/14/2010 9:41 AM, Jim Nasby wrote: On Dec 14, 2010, at 9:27 AM, Andy Colson wrote: Is this the same thing Nick is working on? How'd he get along? http://archives.postgresql.org/message-id/4cd1853f.2010...@noaa.gov So it is. The one I replied to stood out because no one had repli

Re: [PERFORM] Help with bulk read performance

On 11/1/2010 9:15 AM, Dan Schaffer wrote: Hello We have an application that needs to do bulk reads of ENTIRE Postgres tables very quickly (i.e. select * from table). We have observed that such sequential scans run two orders of magnitude slower than observed raw disk reads (5 MB/s versus 100 MB/

Re: [PERFORM] Compared MS SQL 2000 to Postgresql 9.0 on Windows

On 12/7/2010 2:10 PM, Kenneth Marshall wrote: On Tue, Dec 07, 2010 at 11:56:51AM -0800, Richard Broersma wrote: On Tue, Dec 7, 2010 at 11:43 AM, Andy Colson wrote: In PG the first statement you fire off (like an "insert into" for example) will start a transaction. ?If you dont com

Re: [PERFORM] Compared MS SQL 2000 to Postgresql 9.0 on Windows

On 12/7/2010 1:22 PM, Justin Pitts wrote: Also, as a fair warning: mssql doesn't really care about transactions, but PG really does. Make sure all your code is properly starting and commiting transactions. -Andy I do not understand that statement. Can you explain it a bit better? In mssql

Re: [PERFORM] Compared MS SQL 2000 to Postgresql 9.0 on Windows

On 12/7/2010 11:34 AM, Tom Polak wrote: We are in the process of deciding on how to proceed on a database upgrade. We currently have MS SQL 2000 running on Windows 2003 (on my test server). I was shocked at the cost for MS SQL 2008 R2 for a new server (2 CPU license). I started comparing DB’s

Re: [PERFORM] Running PostgreSQL as fast as possible no matter the consequences

On 11/15/2010 9:06 AM, Robert Haas wrote: In 9.1, I'm hopeful that we'll have unlogged tables, which will even better than turning these parameters off, and for which I just posted a patch to -hackers. Instead of generating WAL and writing WAL to the OS and then NOT trying to make sure it hits t

Re: [PERFORM] MVCC performance issue

On 11/12/2010 7:47 AM, Kyriacos Kyriacou wrote: SUGGESTION -- 1) When a raw UPDATE is performed, store all "new raw versions" either in separate temporary table space or in a reserved space at the end of each table (can be allocated dynamically) etc Your use of "raw" is confusi

Re: [PERFORM] Huge overestimation in rows expected results in bad plan

On 11/9/2010 3:26 PM, bricklen wrote: Hi, I have a query that is getting a pretty bad plan due to a massively incorrect count of expected rows. All tables in the query were vacuum analyzed right before the query was tested. Disabling nested loops gives a significantly faster result (4s vs 292s).

Re: [PERFORM] Simple (hopefully) throughput question?

On 11/3/2010 10:52 AM, Nick Matheson wrote: Hello We have an application that needs to do bulk reads of ENTIRE Postgres tables very quickly (i.e. select * from table). We have observed that such sequential scans run two orders of magnitude slower than observed raw disk reads (5 MB/s versus 100 M

Re: [PERFORM] Using more tha one index per table

On 7/21/2010 2:31 AM, Elias Ghanem wrote: Hi, I have a question concerning the uses of indexes in Postgresql. I red that in PG a query can not use more than one index per table: "a query or data manipulation command can use at most one index per table". Actually I found this a little weird and un

Re: [PERFORM] Identical query slower on 8.4 vs 8.3

FULL is usually bad. Stick to "vacuum analyze" and drop the full. Do you have indexes on: test.tid, testresult.fk_tid, questionresult.fk_trid and testresult.trid -Andy On 7/15/2010 10:12 AM, Patrick Donlin wrote: I'll read over that wiki entry, but for now here is the EXPLAIN ANALYZE outp

Re: [PERFORM] performance on new linux box

On 07/11/2010 03:02 PM, Ryan Wexler wrote: Well I got me a new raid card, MegaRAID 8708EM2, fully equipped with BBU and read and write caching are enabled. It completely solved my performance problems. Now everything is way faster than the previous server. Thanks for all the help everyone.

Re: [PERFORM] Queries with conditions using bitand operator

On 07/13/2010 06:48 AM, Elias Ghanem wrote: Hi, I have table "ARTICLE" containing a String a field "STATUS" that represents a number in binary format (for ex: 10011101). My application issues queries with where conditions that uses BITAND operator on this field (for ex: select * from article wh

Re: [PERFORM] performance on new linux box

On 07/07/2010 06:06 PM, Ryan Wexler wrote: Postgresql was previously running on a single cpu linux machine with 2 gigs of memory and a single sata drive (v8.3). Basically a desktop with linux on it. I experienced slow performance. So, I finally moved it to a real server. A dually zeon cento

Re: [PERFORM] Dead lock

On 06/14/2010 06:50 AM, Elias Ghanem wrote: Hi all, I have 2 data bases trying to perform an update query at the same time on a same table in a third data base using db link. I'm getting a dead lock exception: ERROR: deadlock detected DETAIL: Process 27305 waits for ShareLock on transaction 55575

Re: [PERFORM] query tuning help

On 06/14/2010 05:41 AM, AI Rumman wrote: Can any one please help me in tuning the query? explain select * from (select * from crmentity where deleted = 0 and createdtime between (now() - interval '6 month') and now() ) as crmentity inner join (select * from activity where activitytype = 'Emails

Re: [PERFORM] Analysis Function

On 06/10/2010 07:41 PM, David Jarvis wrote: Hi, I found a slow part of the query: SELECT * date(extract(YEAR FROM m.taken)||'-1-1') d1,* * date(extract(YEAR FROM m.taken)||'-1-31') d2* FROM climate.city c, climate.station s, climate.station_category sc, climate.measurement m WHERE

Re: [PERFORM] How to insert a bulk of data with unique-violations very fast

On 06/01/2010 10:03 AM, Torsten Zühlsdorff wrote: Hello, i have a set of unique data which about 150.000.000 rows. Regullary i get a list of data, which contains multiple times of rows than the already stored one. Often around 2.000.000.000 rows. Within this rows are many duplicates and often th

Re: [PERFORM] slow query performance

On 6/3/2010 12:47 PM, Anj Adu wrote: I cant seem to pinpoint why this query is slow . No full table scans are being done. The hash join is taking maximum time. The table dev4_act_action has only 3 rows. box is a 2 cpu quad core intel 5430 with 32G RAM... Postgres 8.4.0 1G work_mem 20G effective_

Re: [PERFORM] Slow Bulk Delete

On 05/08/2010 06:39 AM, thilo wrote: Hi all! We moved from MySQL to Postgresql for some of our projects. So far we're very impressed with the performance (especially INSERTs and UPDATEs), except for a strange problem with the following bulk delete query: DELETE FROM table1 WHERE table2_id = ?

Re: [PERFORM] How check execution plan of a function

On Fri Apr 9 2010 8:18 AM, Sabin Coanda wrote: I have just a function returning a cursor based on a single coplex query. When I check the execution plan of that query it takes about 3 seconds. Just when it is used inside the function it freezes. This is the problem, and this is the reason I cann

Re: [PERFORM] How check execution plan of a function

On Wed Apr 7 2010 7:47 AM, Sabin Coanda wrote: Hi there, I have a function which returns setof record based on a specific query. I try to check the execution plan of that query, so I write EXPLAIN ANALYZE before my select, I call the function and I see the result which shows an actual time about

Re: [PERFORM] REINDEXing database-wide daily

On 3/30/2010 4:32 AM, Gnanakumar wrote: Hi, We're using PostgreSQL 8.2. I have a question in connection to this question posted by me earlier: http://archives.postgresql.org/pgsql-performance/2010-03/msg00343.php In our application, DML operations (INSERT/UPDATE/DELETE) are heavily performed

Re: [PERFORM] Database size growing over time and leads to performance impact

On 3/30/2010 6:17 AM, Gnanakumar wrote: We're using pgpool-II version 2.0.1 for PostgreSQL connection management. pgpool configurations are: num_init_children = 450 child_life_time = 300 connection_life_time = 120 child_max_connections = 30 As you recommended, I ran "ps -ax|grep postgres" at al

  1   2   >