FAQ

PostgreSQL > pgsql-performance > November 2011

Search Discussions

37 discussions - 288 posts

  • 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 maintenance_work_mem = ...
    Tory M BlueTory M Blue
    Nov 16, 2011 at 10:53 pm
    Nov 17, 2011 at 4:56 pm
  • Hi list, I've been experiencing a weird performance issue lately. I have a very simple (and usually very fast) query: SELECT track_logs.id FROM track_logs WHERE track_logs.track_id = <some id AND ...
    Claudio FreireClaudio Freire
    Nov 3, 2011 at 5:51 pm
    Nov 7, 2011 at 8:32 pm
  • Hello list, A OCZ Vertex 2 PRO and Intel 710 SSD, both 100GB, in a software raid 1 setup. I was pretty convinced this was the perfect solution to run PostgreSQL on SSDs without a IO controller with ...
    Yeb HavingaYeb Havinga
    Nov 2, 2011 at 1:05 pm
    Nov 3, 2011 at 3:53 pm
  • When I run the following query: select questions.id from questions join ( select u.id as user_id from users as u left join scores as s on s.user_id = u.id ) as subquery on subquery.user_id = ...
    Jay LevittJay Levitt
    Nov 7, 2011 at 9:25 pm
    Nov 16, 2011 at 2:06 pm
  • Hi, running Postgres 9.1.1 on an EC2 m1.xlarge instance. Machine is a dedicated master with 2 streaming replication nodes. The machine has 16GB of RAM and 4 cores. We're starting to see some slow ...
    Cody CaughlanCody Caughlan
    Nov 14, 2011 at 6:17 pm
    Nov 16, 2011 at 6:17 pm
  • Hello, just for clarification. Unlogged tables are not memory tables don't? If we stop postgres server (normal stop) and start again, all information in unlogged tables still remain? So, can I expect ...
    Anibal David AcostaAnibal David Acosta
    Nov 11, 2011 at 8:14 pm
    Dec 4, 2011 at 12:28 pm
  • I have one query which does not run very often. Sometimes it may be months between runs. However, when it does get executed, it scans approximately 100 identically-structured tables (a form of ...
    Jon NelsonJon Nelson
    Nov 17, 2011 at 5:11 pm
    Nov 20, 2011 at 4:12 pm
  • As I've come up to speed on SQL and PostgreSQL with some medium-complexity queries, I've asked a few questions about what the optimizer will do in various situations. I'm not talking about the ...
    Jay LevittJay Levitt
    Nov 2, 2011 at 2:22 pm
    Nov 3, 2011 at 1:16 pm
  • Hi All, I have a table with 665605 rows (counted, vacuum-ed): CREATE TABLE unique_words ( filename text NOT NULL, filetype text NOT NULL, word text NOT NULL, count integer,) The query is: select ...
    Joost KraaijeveldJoost Kraaijeveld
    Nov 28, 2011 at 4:42 pm
    Nov 29, 2011 at 3:40 pm
  • I know there were a lot of performance issues with ext4, but i don't know the state of it now. I have a private openstreetmap server installed on a ubuntu 11.10 64bit pc with both partitions (/ and ...
    AlexandruAlexandru
    Nov 14, 2011 at 10:00 am
    Nov 16, 2011 at 6:51 am
  • Experts, Quick Summary: data can now be inserted very quickly via COPY + removing indexes, but is there a design or some tricks to still allow someone to query while the partition is still active and ...
    Benjamin JohnsonBenjamin Johnson
    Nov 30, 2011 at 3:27 pm
    Dec 22, 2011 at 8:04 am
  • Hello, I'm going to be testing some new hardware (see http://archives.postgresql.org/pgsql-performance/2011-11/msg00230.php) and while I've done some very rudimentary before/after tests with pgbench, ...
    CSSCSS
    Nov 18, 2011 at 9:56 am
    Nov 28, 2011 at 10:33 pm
  • Hi, I have the following function: =============================== CREATE OR REPLACE FUNCTION xxx(text) RETURNS SETOF vvvvv AS $BODY$ select a.x, a.y, CASE WHEN strpos($1,b.x) 0 THEN b.x ELSE NULL ...
    Sorin DuduiSorin Dudui
    Nov 11, 2011 at 3:38 pm
    Nov 11, 2011 at 4:55 pm
  • I'm confused. I have a now-trivial SQL function that, unrestricted, would scan my whole users table. When I paste the body of the function as a subquery and restrict it to one row, it only produces ...
    Jay LevittJay Levitt
    Nov 3, 2011 at 5:48 pm
    Nov 7, 2011 at 8:22 pm
  • Hi, My application is performing 1600 inserts per second and 7 updates per second. The updates occurred only in a small table which has only 6 integer columns. The inserts occurred in all other daily ...
    J Ramesh KumarJ Ramesh Kumar
    Nov 23, 2011 at 5:55 am
    Dec 2, 2011 at 3:03 am
  • dear all, i am trying to understand if i am missing something on how vacuum works. i ve read the manual, and did some research on the web about that but i am still not sure. to my understanding, ...
    MirrorXMirrorX
    Nov 29, 2011 at 5:12 pm
    Dec 1, 2011 at 7:24 pm
  • Is here any reason why Postgresql calculates subqueries/storable procedures in select list before applying ORDER BY / LIMIT? I talking about cases like: SELECT *, (some very slow subquery or slow ...
    Maxim BogukMaxim Boguk
    Nov 24, 2011 at 2:57 am
    Nov 28, 2011 at 12:06 am
  • Hi folks, I'm running PG 8.3.15 on an itanium box and was seeing lots of floating-point assist faults by the kernel. Searched around, found a couple references/discussions here and there: ...
    Greg MatthewsGreg Matthews
    Nov 18, 2011 at 1:08 am
    Nov 19, 2011 at 5:39 am
  • Hello, A table has two columns id and EffectiveId. First is primary key. EffectiveId is almost always equal to id (95%) unless records are merged. Many queries have id = EffectiveId condition. Both ...
    Ruslan ZakirovRuslan Zakirov
    Nov 11, 2011 at 3:01 pm
    Nov 15, 2011 at 3:04 pm
  • Hi , While performing full vacuum we encountered the error below: INFO: vacuuming "pg_catalog.pg_index" vacuumdb: vacuuming of database "xxxx" failed: ERROR: duplicate key value violates unique ...
    Bhakti GhatkarBhakti Ghatkar
    Nov 4, 2011 at 12:38 pm
    Nov 8, 2011 at 7:49 am
  • Hi All; The below contab2 table conmtains ~400,000 rows. This query should not take this long. We've tweaked work_mem up to 50MB, ensured that the appropriate indexes are in place, etc... Thoughts? ...
    CS DBACS DBA
    Nov 2, 2011 at 8:21 pm
    Nov 3, 2011 at 2:28 pm
  • Very Fast Version: Recently my database stopped respecting one of my indexes, which took a query that should run in "subsecond response time" and turning it into something that with small data sets ...
    Gary WarnerGary Warner
    Nov 23, 2011 at 10:24 pm
    Nov 24, 2011 at 3:06 am
  • I have two queries in PG 9.1. One uses an index like I would like, the other does not. Is this expected behavior? If so, is there any way around it? postgres=# explain analyze select min(id) from ...
    Ben ChobotBen Chobot
    Nov 18, 2011 at 1:12 am
    Nov 18, 2011 at 2:16 pm
  • We have anywhere from 60-80 background worker processes connecting to Postgres, performing a short task and then disconnecting. The lifetime of these tasks averages 1-3 seconds. I know that there is ...
    Cody CaughlanCody Caughlan
    Nov 15, 2011 at 12:42 am
    Nov 15, 2011 at 8:12 am
  • Hey guys, I've been running some tests while setting up some tiered storage, and I noticed something. Even having an empty 'echo' as archive_command drastically slows down certain operations. For ...
    Shaun ThomasShaun Thomas
    Nov 11, 2011 at 10:21 pm
    Nov 14, 2011 at 10:07 pm
  • Hi Everyone, I recently saw a crash on one of our databases, and I was wondering if this might be an indication of something with WAL that might be unexpectedly creating more files than it needs to? ...
    Richard YenRichard Yen
    Nov 7, 2011 at 10:25 pm
    Nov 12, 2011 at 6:31 am
  • Hi everybody, I'm having some issues with PostgreSQL 9.03 running on FreeBSD 8.2 on top of VMware ESXi 4.1 U1. The problem is query are taking too long, and some times one query "blocks" everybody ...
    Lucas MocellinLucas Mocellin
    Nov 7, 2011 at 10:36 am
    Nov 7, 2011 at 3:36 pm
  • I basically have 3 tables. One being the core table and the other 2 depend on the 1st. I have the requirement to add upto 70000 records in the tables. I do have constraints (primary & foreign keys, ...
    Siva palanisamySiva palanisamy
    Nov 3, 2011 at 3:52 pm
    Nov 3, 2011 at 6:33 pm
  • Hi all, I've got a query that I need to squeeze as much speed out of as I can. When I execute this query, the average time it takes is about 190 ms. I increased my work_mem from 1 MB to 50MB and it ...
    Brian FehrleBrian Fehrle
    Nov 2, 2011 at 8:14 pm
    Nov 3, 2011 at 1:48 am
  • Hi there, I have the function: CREATE OR REPLACE FUNCTION "Test"( ... ) RETURNS SETOF record AS $BODY$ BEGIN RETURN QUERY SELECT ...; END; $BODY$ LANGUAGE 'plpgsql' STABLE The function call takes ...
    Sabin CoandaSabin Coanda
    Nov 1, 2011 at 2:01 pm
    Nov 2, 2011 at 8:21 am
  • hello to all, i would like your advice on the following matter. i have a table with 150 million rows. there are some indexes on this table but the one that is really important is one that has 3 ...
    MirrorXMirrorX
    Nov 24, 2011 at 5:20 pm
    Nov 24, 2011 at 7:25 pm
  • Hey there,  We are looking at beefing up our servers with SSD's. Some of you did some interesting tests with the Intel 320. So the idea came to make a RAID10 with four 600GB models.  I did ...
    Christiaan WillemsenChristiaan Willemsen
    Nov 21, 2011 at 9:04 pm
    Nov 23, 2011 at 3:19 pm
  • Hello Everyone, I could see the following in the production server (result of the "top" M command) - PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 25265 postgres 15 0 3329m 2.5g 1.9g S 0.0 ...
    Venkat BalajiVenkat Balaji
    Nov 9, 2011 at 9:25 am
    Nov 14, 2011 at 5:54 am
  • Hi, I have some functions that select data from tables which are daily or monthly updated. My functions are marked as STABLE. I am wondering if they perform better if I mark they as IMMUTABLE? Thank ...
    Sorin DuduiSorin Dudui
    Nov 10, 2011 at 1:06 pm
    Nov 10, 2011 at 2:28 pm
  • Folks, After having some production issues, I did some testing and it seems that any SQL function declared STRICT will never inline. As a result, it won't work with either indexes (on the underlying ...
    Josh BerkusJosh Berkus
    Nov 8, 2011 at 11:29 pm
    Nov 9, 2011 at 12:31 am
  • Hi all. Been using postgres for years, and lurking on this newsgroup for a short while now to help me gain the benefit of your expertise and experience and learn how to get most out of postgresql ...
    Andrew BarnhamAndrew Barnham
    Nov 14, 2011 at 10:31 pm
    Nov 30, 2011 at 8:51 pm
  • I have a question on how the analyzer works in this type of scenario. We calculate some results and COPY INTO some partitioned tables, which we use some selects to aggregate the data back out into ...
    Strange, John WStrange, John W
    Nov 8, 2011 at 10:39 pm
    Nov 13, 2011 at 4:17 pm
Nov 2011
Group Navigation
period‹ prev | Nov 2011 | next ›
Group Overview
grouppgsql-performance @
categoriespostgresql
discussions37
posts288
users87
websitepostgresql.org
irc#postgresql

87 users for November 2011

Tomas Vondra: 21 posts Tom Lane: 21 posts Claudio Freire: 15 posts Kevin Grittner: 15 posts Jay Levitt: 12 posts Scott Marlowe: 12 posts Robert Haas: 10 posts Greg Smith: 8 posts Cody Caughlan: 7 posts Shaun Thomas: 7 posts Yeb Havinga: 7 posts Josh Berkus: 6 posts Tory M Blue: 6 posts Albe Laurenz: 5 posts Merlin Moncure: 5 posts Craig Ringer: 4 posts Jon Nelson: 4 posts Joost Kraaijeveld: 4 posts Mario Weilguni: 4 posts MirrorX: 4 posts
show more
Archives