FAQ

PostgreSQL > pgsql-performance > March 2007

Search Discussions

57 discussions - 471 posts

  • Hello, I have noticed a strange performance regression and I'm at a loss as to what's happening. We have a fairly large database (~16 GB). The original postgres 7.4 was running on a sun v880 with 4 ...
    Alex DeucherAlex Deucher
    Mar 1, 2007 at 8:40 pm
    Mar 8, 2007 at 9:02 pm
  • Hi, I just try to find out why a simple count(*) might last that long. At first I tried explain, which rather quickly knows how many rows to check, but the final count is two orders of magnitude ...
    Andreas TilleAndreas Tille
    Mar 22, 2007 at 10:53 am
    Mar 23, 2007 at 4:54 pm
  • Greetings, We've recently made a couple changes to our system that have resulted in a drastic increase in performance as well as some very confusing changes to the database statistics, specifically ...
    Erik JonesErik Jones
    Mar 28, 2007 at 7:57 pm
    Apr 18, 2007 at 5:20 pm
  • Folks ! I have a batch application that writes approx. 4 million rows into a narrow table. I am using JDBC addBatch/ExecuteBatch with auto commit turned off. Batch size is 100. So far I am seeing ...
    FemskiFemski
    Mar 13, 2007 at 10:55 am
    Mar 17, 2007 at 4:52 am
  • I see that one can now get compact flash to SATA connectors. If I were to use a filesystem with noatime etc and little non-sql traffic, does the physical update pattern tend to have hot sectors that ...
    James MansionJames Mansion
    Mar 6, 2007 at 11:44 pm
    Apr 4, 2007 at 6:33 am
  • Hi all, When I run multiple TPC-H queries (DBT3) on postgresql, I found the system is not scalable. My machine has 8GB memory, and 4 Xeon Dual Core processor ( 8 cores in total). OS kernel is linux ...
    Xiaoning DingXiaoning Ding
    Mar 30, 2007 at 8:42 pm
    Apr 2, 2007 at 12:26 am
  • Hi all, take a look at those plans: test=# explain analyze SELECT COUNT(id) FROM t_oa_2_00_card WHERE pvcp in (select id from l_pvcp where value ilike '%pi%'); QUERY PLAN - ...
    Gaetano MendolaGaetano Mendola
    Mar 30, 2007 at 9:33 am
    Apr 2, 2007 at 10:42 am
  • Hello, I have upgraded from 7.3.9 to 8.2.3 and now the application that is using Postgres is really slow. Using pgfouine, I was able to identify a SQL select statement that was running in 500 ms ...
    Vincent MoreauVincent Moreau
    Mar 13, 2007 at 8:20 am
    Mar 13, 2007 at 3:48 pm
  • I've found that it would be helpful to be able to tell how busy my dedicated PG server is ( Linux 2.6 kernel, v8.0.3 currently ) before pounding it with some OLAP-type queries. Specifically, I have a ...
    Dan HarrisDan Harris
    Mar 21, 2007 at 12:47 am
    Mar 25, 2007 at 8:12 am
  • Hi! I have two tables with some indices on them: CREATE TABLE subscriber ( � id serial NOT NULL, � anumber character varying(32) NOT NULL, � CONSTRAINT subscriber_pk PRIMARY KEY (id) ) CREATE INDEX ...
    Mezei ZoltánMezei Zoltán
    Mar 9, 2007 at 1:53 pm
    Mar 9, 2007 at 4:24 pm
  • Hi, Vacuum full is very slow for me . I dont know how to speed it up. It takes between 60 and 90 minutes. I have set up autovacuum but I also run vacuum full once per week. The slowest parts in the ...
    Ruben RubioRuben Rubio
    Mar 19, 2007 at 11:09 am
    Mar 23, 2007 at 4:32 pm
  • Folks, is there any constrains/problems/etc. to run several vacuum processes in parallel while each one is 'vaccuming' one different table? Example: vacuum -d db1 -t table1 & vacuum -d db1 -t table2 ...
    DimitriDimitri
    Mar 22, 2007 at 1:34 pm
    Mar 23, 2007 at 5:13 pm
  • Hello, I am looking to use PostgreSQL for storing some very simple flat data mostly in a single table. The amount of data will be in the hundreds of gigabytes range. Each row is on the order of ...
    Peter SchullerPeter Schuller
    Mar 30, 2007 at 5:16 am
    Apr 4, 2007 at 5:44 pm
  • Hi All, I tried posting this last week but it has not come through yet, so please excuse me if there is a double post. We're having some issue's with the vacuum times within our database environment, ...
    Bruce McAlisterBruce McAlister
    Mar 5, 2007 at 12:33 pm
    Mar 16, 2007 at 7:07 pm
  • Dear All, I have to take backup of a database as a SQL file using the pg_dump utility and I have to check the disk space before taking the backup. Hence I need to estimate the size of the pg_dump ...
    Ravindran G-TLS,Chennai.Ravindran G-TLS,Chennai.
    Mar 5, 2007 at 3:17 pm
    Mar 6, 2007 at 4:43 pm
  • I'm posting this to performance in case our workaround may be of benefit to someone with a similar issue. I'm posting to hackers because I hope we can improve our planner in this area so that a ...
    Kevin GrittnerKevin Grittner
    Mar 23, 2007 at 5:24 pm
    Mar 24, 2007 at 1:08 pm
  • My company is purchasing a Sunfire x4500 to run our most I/O-bound databases, and I'd like to get some advice on configuration and tuning. We're currently looking at: - Solaris 10 + zfs + RAID Z - ...
    Matt SmileyMatt Smiley
    Mar 23, 2007 at 2:52 am
    Mar 30, 2007 at 1:37 am
  • Hi, I recently migrated one of our large (multi-hundred GB) dbs from an Intel 32bit platform (Dell 1650 - running 8.1.3) to a 64bit platform (Dell 1950 - running 8.1.5). However I am not seeing the ...
    David BrainDavid Brain
    Mar 22, 2007 at 12:18 pm
    Mar 22, 2007 at 6:23 pm
  • Hi, I am sorry if it is a repeat question but I want to know if database performance will decrease if I increase the max-connections to 2000. At present it is 100. I have a requirement where the ...
    Shiva SarnaShiva Sarna
    Mar 1, 2007 at 5:25 am
    Mar 1, 2007 at 3:47 pm
  • Hi List, how to speedup nested loop queries and by which parameters. -- Regards Gauri
    Gauri KanekarGauri Kanekar
    Mar 26, 2007 at 12:04 pm
    Mar 27, 2007 at 11:34 am
  • Hi, I have database with a huge amount of data so i'm trying to make it as fast as possible and minimize space. One thing i've done is join on a prepopulated date lookup table to prevent a bunch of ...
    Zoolin LinZoolin Lin
    Mar 6, 2007 at 9:56 pm
    Mar 9, 2007 at 2:26 pm
  • Hi, I'm new to tuning PostgreSQL and I have a query that gets slower after I run a vacuum analyze. I believe it uses a Hash Join before the analyze and a Nested Loop IN Join after. It seems the ...
    Jeff ColeJeff Cole
    Mar 5, 2007 at 10:05 pm
    Mar 8, 2007 at 3:30 pm
  • Hi all. I would like to speed up this query: EXPLAIN ANALYZE SELECT relid,schemaname,relname,seq_scan,seq_tup_read,idx_scan,idx_tup_fetch,n_tup_ins,n_tup_upd,n_tup_del FROM pg_stat_user_tables; QUERY ...
    Daniel Cristian CruzDaniel Cristian Cruz
    Mar 28, 2007 at 12:59 pm
    Mar 28, 2007 at 5:08 pm
  • Hi, I have two queries that are very similar, that run on the same table with slightly different conditions. However, despite a similar number of rows returned, the query planner is insisting on a ...
    Noah M. DanielsNoah M. Daniels
    Mar 23, 2007 at 7:45 pm
    Mar 23, 2007 at 10:22 pm
  • Hello guys, is it possible to determine dead tuples size for table? -- Alexey Romanchuk
    Alexey RomanchukAlexey Romanchuk
    Mar 15, 2007 at 8:32 am
    Mar 16, 2007 at 2:29 pm
  • Question for anyone... I tried posting to the bugs, and they said this is a better question for here. I have to queries. One runs in about 2 seconds. The other takes upwards of 2 minutes. I have a ...
    Rob SchallRob Schall
    Mar 1, 2007 at 3:43 pm
    Mar 1, 2007 at 10:37 pm
  • Hi all. I'm running PostgreSQL v8.1.8 (under Linux Ubuntu). A function body is written as "language sql stable". There is just a select for a search in a view with two arguments to do the search. The ...
    Vincenzo RomanoVincenzo Romano
    Mar 29, 2007 at 7:33 pm
    Apr 5, 2007 at 9:51 pm
  • I have a very slow query that I'm trying to tune. I think my performance tuning is being complicated by the system's page cache. If a run the query after the system has been busy with other tasks for ...
    Barry MooreBarry Moore
    Mar 18, 2007 at 12:57 pm
    Mar 18, 2007 at 7:49 pm
  • Hi, Please forgive me if I missed something (I have been searching for a definitive answer for this for 2 days). Is there any way to disable autocommit in libpq? (PG 7.4.1) Thanks Mike
    Dengler, MichaelDengler, Michael
    Mar 13, 2007 at 3:08 pm
    Mar 13, 2007 at 4:14 pm
  • Hi performers, after following this list for a while, I try to configure a database server with a limited budget. Planned are 2 databases - archiveopteryx - ...
    Axel RauAxel Rau
    Mar 8, 2007 at 11:47 am
    Mar 9, 2007 at 7:28 pm
  • I need to cross reference 2 tables. There are O(10M) A's, each has an ordered set of 10 of the O(100K) B's associated with it. The dominant query will be finding the A's and their count associated ...
    Adam L BebergAdam L Beberg
    Mar 2, 2007 at 4:23 am
    Mar 2, 2007 at 7:50 pm
  • Hi all. I'm experiencing a strange behaviour with 8.1.8 (cannot do upgrades to 8.2 at the moment). On a 13+ million rows table I can do a query with results back in less than 100 ms. Result is a set ...
    Vincenzo RomanoVincenzo Romano
    Mar 18, 2007 at 6:50 am
    Mar 19, 2007 at 10:18 am
  • I am having a performance problem with a query implemented within a server side function. If I use an SQL client(EMS Postgres) and manually generate the sql query I get about 100 times performance ...
    Schwarz, KarlSchwarz, Karl
    Mar 8, 2007 at 5:37 pm
    Mar 8, 2007 at 6:45 pm
  • 8.0.3 - Linux 2.6.18.. Freshly vacuumed and analyzed This database has been humming along fine for a while now, but I've got one of those sticky queries that is taking much too long to finish. After ...
    Dan HarrisDan Harris
    Mar 29, 2007 at 2:22 am
    Mar 30, 2007 at 7:53 am
  • AFAIK the only very likely way to cause that is to touch enough different tables in one transaction that you run out of lock entries. While you could postpone the problem by increasing the ...
    Tom LaneTom Lane
    Mar 27, 2007 at 3:38 am
    Mar 27, 2007 at 1:59 pm
  • I try to change my database server from the older one ie. 2Cpu Xeon 2.4 32 bit 4Gb SDram Hdd SCSI RAID 5 and FC 3 ix86 with 7..4.7 PG to the newer one with 2CPU Xeon 3.0 64 Bit 4Gb DDRram SCSI Raid5 ...
    Amrit angsusinghAmrit angsusingh
    Mar 24, 2007 at 6:15 am
    Mar 24, 2007 at 1:31 pm
  • Hi I'm going to install a new server and I'm looking for some advice about how I can help my database performing at its best. I'll be using a redhat ES 4 on a dual core opteron with 2 SCSI 10.000rpm ...
    Paolo NegriPaolo Negri
    Mar 23, 2007 at 12:48 pm
    Mar 23, 2007 at 3:50 pm
  • I've got one logging table that is over 330 million rows to store 6 months' worth of data. It consists of two integers and a 4-character long string. I have one primary key which is the two integers, ...
    Vivek KheraVivek Khera
    Mar 20, 2007 at 3:02 pm
    Mar 20, 2007 at 4:17 pm
  • I'm running in some weird (IMHO) bahviour. When I search a table for certain text (equality est on the relelvant field) it takes much more time than doing the same test by adding a trailing '%' and ...
    Vincenzo RomanoVincenzo Romano
    Mar 18, 2007 at 9:58 am
    Mar 19, 2007 at 12:02 pm
  • Hi, I'm running a Zope web application that obtains most of its data from a PostgreSQL 8.1 database in a virtual machine. I'm able to adjust the memory of this machine according to reasonable values ...
    Andreas TilleAndreas Tille
    Mar 12, 2007 at 8:47 am
    Mar 13, 2007 at 8:23 pm
  • Not quite a performance question, but I can't seem to find a simple answer to this. We're using 8.1.4 and the autovacuum daemon is running every 40 seconds cycling between 3 databases. What is the ...
    Steven FlattSteven Flatt
    Mar 5, 2007 at 4:00 pm
    Mar 5, 2007 at 9:47 pm
  • 1. The function: SELECT a.birth_date FROM ( SELECT indiv_fkey, birth_dt as birth_date, intern_last_update::date as last_update, 'fed' as source FROM cdm.cdm_fedcustomer WHERE birth_dt IS NOT NULL AND ...
    Abu MushayeedAbu Mushayeed
    Mar 1, 2007 at 2:13 pm
    Mar 3, 2007 at 2:12 am
  • Hello! I'm new to performance tuning on postgres. I've read the docs on the posgtres site, as well as: http://www.revsys.com/writings/postgresql-performance.html ...
    David LeangenDavid Leangen
    Mar 2, 2007 at 4:11 am
    Mar 2, 2007 at 9:10 am
  • Hi, In postgres 7.4.* I had to pass --with-java to the configure script for jdbc support. Does postgres 8.2* include it by default? If not, how do I enable it? Thanks Miguel
    Michael DenglerMichael Dengler
    Mar 27, 2007 at 10:16 pm
    Mar 27, 2007 at 10:20 pm
  • Hello I plan to buy a new development server and I wonder what will be the best HD combination. I'm aware that "best combination" also relay on DB structure and usage. so lets assume, heavy duty ...
    Michael Ben-NesMichael Ben-Nes
    Mar 22, 2007 at 9:34 am
    Mar 22, 2007 at 2:37 pm
  • After upgrading to 8.2.3 INSERTs and UPDATEs on one of my tables became incredibly slow. I traced the problem to one of my triggers that calls one of my defined functions (that is IMMUTABLE). If I ...
    Joseph SJoseph S
    Mar 20, 2007 at 7:27 pm
    Mar 20, 2007 at 7:58 pm
  • unsubscribe
    Neelam GoyalNeelam Goyal
    Mar 15, 2007 at 8:41 am
    Mar 16, 2007 at 3:38 pm
  • Howdy- I am currently using PostgreSQL to store and process a high-bandwidth event stream. I do not need old events but the delete and vacuum does not terminate due to the large number of events ...
    James RiordanJames Riordan
    Mar 13, 2007 at 1:36 pm
    Mar 15, 2007 at 3:58 pm
  • Hello!! I have perfectly installed ebmail and Hermes 2 whith JDK 1.4.2 ,postgreSQL 8.1 and Tomacat 5.0.28 and all good works. I send messages whith ebmail and it`s writing in Postgresql but I have ...
    LissetteLissette
    Mar 6, 2007 at 11:36 am
    Mar 9, 2007 at 12:46 am
  • Hi List, Can i find out the timestamp when last a record from a table got updated. Do any of the pg system tables store this info. -- Regards Gauri
    Gauri KanekarGauri Kanekar
    Mar 7, 2007 at 6:43 am
    Mar 7, 2007 at 7:31 am
Mar 2007
12
Group Navigation
period‹ prev | Mar 2007 | next ›
Group Overview
grouppgsql-performance @
categoriespostgresql
discussions57
posts471
users126
websitepostgresql.org
irc#postgresql

126 users for March 2007

Tom Lane: 36 posts Richard Huxton: 23 posts Alex Deucher: 21 posts Merlin Moncure: 15 posts Joshua D. Drake: 14 posts Heikki Linnakangas: 12 posts Ron: 12 posts Craig A. James: 11 posts Dimitri k: 11 posts Erik Jones: 11 posts Michael Stone: 10 posts Alvaro Herrera: 8 posts Carlos Moreno: 8 posts Jeff Frost: 8 posts Vincent Moreau: 7 posts Dave Dutcher: 7 posts Vincenzo Romano: 7 posts Mezei Zoltán: 6 posts Michael Fuhr: 6 posts Scott Marlowe: 6 posts
show more
Archives