FAQ

PostgreSQL > pgsql-performance > September 2011

Search Discussions

48 discussions - 446 posts

  • 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. ...
    Igor ChudovIgor Chudov
    Sep 11, 2011 at 12:36 pm
    Sep 13, 2011 at 8:59 pm
  • The doc at http://www.postgresql.org/docs/current/interactive/indexes-types.html says: "Caution: Hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with ...
    Stefan KellerStefan Keller
    Sep 13, 2011 at 11:04 pm
    Sep 20, 2011 at 4:25 pm
  • Hello Everyone, I had posted a query in "GENERAL" category, not sure if that was the correct category to post. Please help me understand how to calculate free space in Tables and Indexes even after ...
    Venkat BalajiVenkat Balaji
    Sep 20, 2011 at 4:22 pm
    Oct 10, 2011 at 8:04 am
  • 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 ...
    Richard ShawRichard Shaw
    Sep 5, 2011 at 10:35 am
    Sep 7, 2011 at 6:54 am
  • Dear list, we are encountering serious performance problems with our database. Queries which took around 100ms or less last week now take several seconds. The database runs on Ubuntu Server 10.4.3 ...
    Gerhard WohlgenanntGerhard Wohlgenannt
    Sep 3, 2011 at 7:58 am
    Sep 12, 2011 at 11:20 pm
  • 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 ...
    Robert SchnabelRobert Schnabel
    Sep 12, 2011 at 5:33 pm
    Oct 5, 2011 at 9:54 pm
  • Hello performance wizards! (Sorry for the re-post if this appears twice - I see no evidence e-mailing to pgsql-perfomrance is working yet.) My client has migrated his 8.3 hosted DB to new machines ...
    Carlo StonebanksCarlo Stonebanks
    Sep 9, 2011 at 4:34 pm
    Sep 15, 2011 at 4:27 am
  • First of all, thank you for taking the time to review my question. After attending the PostgresOpen conference in Chicago last week, I've been pouring over explain logs for hours on end and although ...
    Michael ViscusoMichael Viscuso
    Sep 21, 2011 at 11:21 pm
    Sep 26, 2011 at 1:24 am
  • Hi all, It looks like I've been hit with this well known issue. I have a complicated query that is intended to run every few minutes, I'm using JDBC's Connection.prepareStatement() mostly for nice ...
    Royce AusburnRoyce Ausburn
    Sep 20, 2011 at 10:55 pm
    Sep 21, 2011 at 5:08 pm
  • I am using Postgresql 9.0.1. Using the query http://wiki.postgresql.org/wiki/Show_database_bloat, I got the following result for a table: -[ RECORD 1 ...
    AI RummanAI Rumman
    Sep 21, 2011 at 7:01 am
    Sep 21, 2011 at 4:30 pm
  • We've currently got PG 8.4.4 running on a whitebox hardware set up, with (2) 5410 Xeon's, and 16GB of RAM. It's also got (4) 7200RPM SATA drives, using the onboard IDE controller and ext3. A few ...
    Anthony PresleyAnthony Presley
    Sep 11, 2011 at 10:44 pm
    Sep 14, 2011 at 8:22 am
  • I recently had need of an "array_except" function but couldn't find any good/existing examples. Based off the neat "array_intersect" function at ...
    BricklenBricklen
    Sep 30, 2011 at 2:32 am
    Oct 4, 2011 at 4:10 pm
  • dear all, i have a table with (approx) 500.000.000 rows. it has several indexes, one of which is a multicolumn index for a column that has an id (integer) and a column that has a timestamp. i have ...
    MirrorXMirrorX
    Sep 14, 2011 at 12:50 pm
    Sep 19, 2011 at 2:39 am
  • Hi All, We are currently using PostgreSQL 9.0.3 and we noticed a performance anomaly from a framework (ActiveRecord) generated query to one of our tables. The query uses an in clause to check an ...
    Timothy GarnettTimothy Garnett
    Sep 23, 2011 at 10:45 pm
    Sep 27, 2011 at 1:01 pm
  • Hi! I have a table not too big but with aprox. 5 millions of rows, this table must have 300 to 400 select per second. But also must have 10~20 delete/insert/update per second. So, I need to know if ...
    Anibal David AcostaAnibal David Acosta
    Sep 8, 2011 at 12:56 pm
    Sep 9, 2011 at 12:04 pm
  • Hello Everyone, I am implementing a PostgreSQL performance monitoring system (to monitor the below) which would help us understand the database behavior - 1. Big Full Table Scans 2. Table with high ...
    Venkat BalajiVenkat Balaji
    Sep 27, 2011 at 4:44 pm
    Oct 7, 2011 at 10:21 pm
  • Hello Everyone, I am back with an issue (likely). I am trying to create a table in our production database, and is taking 5 seconds. We have executed VACUUM FULL and yet to run ANALYZE. Can i expect ...
    Venkat BalajiVenkat Balaji
    Sep 28, 2011 at 5:07 pm
    Sep 30, 2011 at 11:20 am
  • Hello Everyone, I am preparing a plan to track the tables undergoing Full Table Scans for most number of times. If i track seq_scan from the pg_stat_user_tables, will that help (considering the ...
    Venkat BalajiVenkat Balaji
    Sep 27, 2011 at 12:36 pm
    Sep 28, 2011 at 6:13 am
  • In Mammoth Replicator (PG 8.3) I have a table described as Table "public.tevent_cdr" Column | Type | Modifiers ...
    Anthony ShipmanAnthony Shipman
    Sep 26, 2011 at 6:53 am
    Sep 28, 2011 at 5:13 am
  • Let's say that I want to do INSERT SELECT of 1,000 items into a table. The table has some ints, varchars, TEXT and BLOB fields. Would the time that it takes, differ a great deal, depending on whether ...
    Igor ChudovIgor Chudov
    Sep 19, 2011 at 10:11 pm
    Sep 20, 2011 at 5:05 am
  • Hi all, I have a problem with autovacuum apparently not doing the job I need it to do. I have a table named datasession that is frequently inserted, updated and deleted from. Typically the table will ...
    Royce AusburnRoyce Ausburn
    Sep 27, 2011 at 3:55 am
    Sep 27, 2011 at 2:27 pm
  • Hi, I have a database cluster running PostgreSQL 8.2 and I have **new Linux virtualized database environment running PostgreSQL 9.0 My question is how to ensure that database schemas are always ...
    Hany ABOU-GHOURYHany ABOU-GHOURY
    Sep 11, 2011 at 10:54 am
    Sep 13, 2011 at 3:14 am
  • Hello, Does anyone know whether PostgreSQL uses DMA (Direct Memory Access) in certain cases to improve networking IO performance? I mean "simple" query is which doesn't require any CPU processing, ...
    Antonio RodrigesAntonio Rodriges
    Sep 9, 2011 at 5:55 pm
    Sep 10, 2011 at 12:07 pm
  • Hi everyone, My question is, if I have a table with 500,000 rows, and a SELECT of one row is returned in 10 milliseconds, if the table has 6,000,000 of rows and everything is OK (statistics, vacuum ...
    Anibal David AcostaAnibal David Acosta
    Sep 6, 2011 at 6:58 pm
    Sep 8, 2011 at 9:30 pm
  • All, Here's a case which it seems like we ought to be able to optimize for: datamart-# ORDER BY txn_timestamp DESC datamart-# LIMIT 200 datamart-# OFFSET 6000; QUERY PLAN --------------------------- ...
    Josh BerkusJosh Berkus
    Sep 30, 2011 at 12:39 am
    Oct 27, 2011 at 6:47 pm
  • Hello, It is interesting how PostgreSQL reads the tablefiie. Whether its indexes store/use filesystem clusters locations containing required data (so it can issue a low level cluster read) or it just ...
    Antonio RodrigesAntonio Rodriges
    Sep 23, 2011 at 8:04 pm
    Sep 27, 2011 at 4:12 pm
  • 2011/9/17 Tomas Vondra wrote: (...) I'm also interested in such proposals or ideas! Here's some list of topics: * Adding WAL-support to hash indexes in PostgreSQL (see ex-topic) * Time in PostgreSQL ...
    Stefan KellerStefan Keller
    Sep 17, 2011 at 8:01 pm
    Sep 19, 2011 at 1:25 pm
  • It would be great if you could show a benchmark technique which shows otherwise. -Kevin
    Kevin GrittnerKevin Grittner
    Sep 18, 2011 at 3:17 pm
    Sep 19, 2011 at 2:58 am
  • 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 ...
    Anibal David AcostaAnibal David Acosta
    Sep 10, 2011 at 5:03 pm
    Oct 28, 2011 at 7:03 pm
  • Hey, Is there a suggested number of child tables for table partitioning, I ran a stress test on a master table (with 800 thousand rows), trying to create 500,000 child tables for it, each child table ...
    Jian ShiJian Shi
    Sep 29, 2011 at 2:30 pm
    Sep 30, 2011 at 5:13 pm
  • Hi, I did this: CREATE VIEW unionview AS SELECT col, otherstuff FROM (heavy subquery) WHERE col BETWEEN 1 AND 3 UNION ALL SELECT col, otherstuff FROM (another heavy subquery) WHERE col BETWEEN 4 AND ...
    Gunnlaugur Þór BriemGunnlaugur Þór Briem
    Sep 14, 2011 at 6:23 pm
    Sep 22, 2011 at 10:00 am
  • Hi all.... I would like to start a dialogue and hear general feedback about the use of constraint triggers in postgres (8.4.5). Our overall issue is that using general triggers is causing slow ...
    Maria L. WilsonMaria L. Wilson
    Sep 26, 2011 at 5:17 pm
    Sep 30, 2011 at 3:12 am
  • Hi everyone, I did a software upgrade, and with it came a new feature where when selecting a customer it queries for the sum of a few columns. This takes 7 seconds for the 'Cash Sale' customer - by ...
    M. D.M. D.
    Sep 23, 2011 at 7:47 pm
    Sep 26, 2011 at 11:46 pm
  • Hi all, I am trying to update / refresh one table (history) only from prod. database to my test environment database my query as follows: pg_dump -h <hostname1 -U postgres -t history DATABASENAME | ...
    Hany ABOU-GHOURYHany ABOU-GHOURY
    Sep 21, 2011 at 3:57 am
    Sep 21, 2011 at 12:48 pm
  • I'm just beginning the process of benchmarking and tuning a new server. Something I really haven't done before. I'm using Greg's book as a guide. I started with bonnie++ (1.96) and immediately got ...
    Samuel GendlerSamuel Gendler
    Sep 13, 2011 at 7:21 pm
    Sep 14, 2011 at 6:19 pm
  • [please CC, I'm not on the list] Hi all, we have one table that basically uses Postgres as a key-value store. Table "public.termindex" Column | Type | Modifiers -------------+---------+----------- ...
    Thomas KapplerThomas Kappler
    Sep 20, 2011 at 5:43 pm
    Sep 21, 2011 at 3:02 pm
  • Hi, Sorry if this is an odd question: I assume that Postgres indexes don't store records but only pointers to the data. This means, that there is always an additional access needed (real table I/O). ...
    Stefan KellerStefan Keller
    Sep 18, 2011 at 8:19 pm
    Sep 19, 2011 at 1:13 am
  • Hola!! Tengo un serio inconveniente, estoy trabajando con postgresql 8.2 y tomcat 5.5.20 en un equipo con Centos 5.3 Y se me presenta un problema con una consulta, si la ejecuto le toma alrededores ...
    Numael Vacca DuranNumael Vacca Duran
    Sep 29, 2011 at 12:43 pm
    Sep 29, 2011 at 2:21 pm
  • Hello Everyone, I have been working on PostgreSQL for quite a while (2 yrs) now. I have got "PostgreSQL 9.0 High Performance" book today and quite excited to go through it. Please let me know any ...
    Venkat BalajiVenkat Balaji
    Sep 28, 2011 at 5:17 pm
    Sep 28, 2011 at 6:54 pm
  • Hi, I am having problem retrieving data with % in the value. I tried a lot but so far i have no luck. for example i have first name = 'abc', middle name = 'pq%', last name = '123' I want to list all ...
    BiswaBiswa
    Sep 26, 2011 at 1:36 pm
    Sep 26, 2011 at 2:45 pm
  • I am working on a fuzzy search of a large dataset. Basically, it is a list of all of the songs, albums, artists, movies, and celebrities exported from Freebase. Anyway, I was hoping to get a fuzzy ...
    Jonathan BartlettJonathan Bartlett
    Sep 22, 2011 at 4:40 pm
    Sep 22, 2011 at 11:04 pm
  • buen dia a partir de los siguientes artículos publicados en la primera edicion de PostgreSQL Magazine (http://pgmag.org/00/read) (Performance Tunning PostgreSQL y Tuning linux for PostgreSQL) me ...
    Hellmuth VargasHellmuth Vargas
    Sep 21, 2011 at 1:32 pm
    Sep 21, 2011 at 2:19 pm
  • I have a lot of wasted bytes in some tables. Somewhere I read that maybe auto-vacuum can't release space due to a low max_fsm_pages setting. I want to increase it, but I don't found the param in the ...
    Anibal David AcostaAnibal David Acosta
    Sep 20, 2011 at 4:28 am
    Sep 20, 2011 at 4:49 am
  • Hi, I know there exist Bitmap Index Scan and Bitmap Heap Scan in Postgres. What about implementing a bitmap index for explicit use (CREATE INDEX ...)? Any use cases? Bitmap indexes work best on ...
    Stefan KellerStefan Keller
    Sep 18, 2011 at 7:45 pm
    Sep 18, 2011 at 8:06 pm
  • In relation to my previous thread (about SW RAID vs. HW RAID on a P400), I was able to narrow down the filesystem speed and in general, our new system (running PG 9.1) is about 3x - 5x faster on the ...
    Anthony PresleyAnthony Presley
    Sep 13, 2011 at 11:56 am
    Sep 17, 2011 at 8:22 pm
  • It's not an issue for me (it's not really impacting performance), but since it was odd I thought I might ask. I have this supermegaquery: SELECT t.date AS status_date, lu.id AS memberid, lu.username ...
    Claudio FreireClaudio Freire
    Sep 16, 2011 at 2:50 pm
    Sep 17, 2011 at 2:41 am
  • I want to thank members on this list which helped me benchmark and conclude that RAID 10 on a XFS filesystem was the way to go over what we had prior. PostgreSQL we have been using with Perl for the ...
    OgdenOgden
    Sep 11, 2011 at 7:50 pm
    Sep 11, 2011 at 8:16 pm
  • I'm running a labour-intensive series of queries on a medium-sized dataset (~100,000 rows) with geometry objects and both gist and btree indices. The queries are embedded in plpgsql, and have ...
    C PondC Pond
    Sep 3, 2011 at 12:25 am
    Sep 5, 2011 at 9:27 am
Sep 2011
Group Navigation
period‹ prev | Sep 2011 | next ›
Group Overview
grouppgsql-performance @
categoriespostgresql
discussions48
posts446
users97
websitepostgresql.org
irc#postgresql

97 users for September 2011

Tom Lane: 27 posts Venkat Balaji: 24 posts Scott Marlowe: 23 posts Kevin Grittner: 20 posts Merlin Moncure: 16 posts Claudio Freire: 15 posts Andy Colson: 14 posts Stefan Keller: 14 posts Stephen Frost: 14 posts Shaun Thomas: 13 posts Tomas Vondra: 13 posts Craig Ringer: 12 posts Robert Klemme: 9 posts Anibal David Acosta: 8 posts Gerhard Wohlgenannt: 8 posts Igor Chudov: 8 posts Greg Smith: 7 posts Hany ABOU-GHOURY: 7 posts Jeff Janes: 7 posts Marti Raudsepp: 7 posts
show more
Archives