FAQ

PostgreSQL > pgsql-performance > December 2003

Search Discussions

57 discussions - 315 posts

  • Hi, sorry for duplication, I asked this on pgsql-admin first before realizing it wasn't the appropriate list. I'm having trouble optimizing PostgreSQL for an admittedly heinous worst-case scenario ...
    Jack CoatesJack Coates
    Dec 4, 2003 at 4:06 pm
    Dec 9, 2003 at 5:36 pm
  • Hello! I am relative newcomer to SQL and PostgreSQL world, so please forgive me if this question is stupid. I am experiencing strange behaviour, where simple UPDATE of one field is very slow, ...
    Ivar ZaransIvar Zarans
    Dec 4, 2003 at 6:58 pm
    Dec 6, 2003 at 7:54 pm
  • I am currently working on optimizing some fairly time consuming queries on a decently large dataset. The Following is the query in question. SELECT z.lat, z.lon, z.city, z.state, q.date_time, c.make, ...
    Jared CarrJared Carr
    Dec 1, 2003 at 9:44 pm
    Dec 3, 2003 at 11:56 pm
  • Hi, it seems to me that the optimizer parameters (like random_page_cost etc.) could easily be calculated and adjusted dynamically be the DB backend based on the planner's cost estimates and actual ...
    Marinos J. YannikosMarinos J. Yannikos
    Dec 18, 2003 at 6:44 pm
    Dec 24, 2003 at 1:59 pm
  • I took advantage of last weekend to upgrade from 7.2.4 to 7.4.0 on a new faster box. Now I'm trying to implement pg_autovacuum. It seems to work ok, but after about an hour or so, it does nothing. ...
    Vivek KheraVivek Khera
    Dec 2, 2003 at 8:38 pm
    Dec 8, 2003 at 6:41 pm
  • Hi everyone, I want to pick your brains for hardware suggestions about a Linux-based PostgreSQL 7.4 server. It will be a dedicated DB server backing our web sites and hit by application servers ...
    Jeff BohmerJeff Bohmer
    Dec 11, 2003 at 8:20 pm
    Dec 15, 2003 at 4:38 pm
  • I'm observing that when I have many processes doing some work on my system that the transactions run along almost in lockstep. It appears from messages posted here that the foreign keys are acquiring ...
    Vivek KheraVivek Khera
    Dec 29, 2003 at 8:34 pm
    Jan 5, 2004 at 10:34 pm
  • To all, The facts: PostgreSQL 7.4.0 running on BSD 5.1 on Dell 2650 with 4GB RAM, 5 SCSI drives in hardware RAID 0 configuration. Database size with indexes is currently 122GB. Schema for the table ...
    Sean ShannySean Shanny
    Dec 29, 2003 at 4:36 pm
    Dec 30, 2003 at 3:03 pm
  • I need some help tracking down a sudden, massive slowdown in inserts in one of our databases. PG: 7.2.3 (RedHat 8.0) Background. We currently run nearly identical systems at two sites: Site A is a ...
    Steve WamplerSteve Wampler
    Dec 5, 2003 at 9:52 pm
    Dec 8, 2003 at 3:36 pm
  • To all, We are building a data warehouse composed of essentially click stream data. The DB is growing fairly quickly as to be expected, currently at 90GB for one months data. The idea is to keep 6 ...
    Sean ShannySean Shanny
    Dec 3, 2003 at 9:41 pm
    Dec 6, 2003 at 1:19 pm
  • I backed up my database using pg_dump, and then restored it onto a different server using psql. I see that the query "SELECT COUNT(*) FROM myTable" executes immediately on the new server but takes ...
    David ShadovitzDavid Shadovitz
    Dec 17, 2003 at 5:30 am
    Dec 18, 2003 at 3:13 pm
  • This is a well-worn thread title - apologies, but these results seemed interesting, and hopefully useful in the quest to get better performance on Solaris: I was curious to see if the rather ...
    Mark KirkwoodMark Kirkwood
    Dec 10, 2003 at 5:56 am
    Dec 12, 2003 at 9:29 pm
  • Dear all We would be recommending to our ct. on the use of Postgresql db as compared to MS SQL Server. We are targetting to use Redhat Linux ES v2.1, Postgresql v7.3.4 and Postgresql ODBC 07.03.0100. ...
    ChewtcChewtc
    Dec 3, 2003 at 2:23 am
    Dec 4, 2003 at 1:28 pm
  • I have a database where the vast majority of information that is related to a customer never changes. However, there is a single field (i.e. balance) that changes potentially tens to hundreds of ...
    Keith BottnerKeith Bottner
    Dec 27, 2003 at 12:12 am
    Dec 27, 2003 at 6:31 pm
  • Well, now that I have the plan for my slow-running query, what do I do? Where should I focus my attention? Thanks. -David Hash Join (cost=16620.59..22331.88 rows=40133 width=266) (actual ...
    David ShadovitzDavid Shadovitz
    Dec 12, 2003 at 8:28 am
    Dec 14, 2003 at 7:24 am
  • Hi. I have a table with 24k records and btree index on column 'id'. Is this normal, that 'select max(id)' or 'select count(id)' causes a sequential scan? It takes over 24 seconds (on a pretty fast ...
    Ryszard LachRyszard Lach
    Dec 22, 2003 at 10:52 am
    Dec 22, 2003 at 3:29 pm
  • Hi- I'm trying to optimize a query that I *think* should run very fast. Essentially, I'm joining two tables that have very selective indexes and constraining the query on an indexed field. (There's a ...
    Nick FankhauserNick Fankhauser
    Dec 16, 2003 at 5:12 pm
    Dec 17, 2003 at 3:26 pm
  • has anyone else noticed a huge difference in "DELETE TABLE <lol " vs. "TRUNCATE <lol " starting w/postgres 7.4? putting aside details (num rows, indexes....): ca. 300 tables (already empty if ...
    Hartmut RaschickHartmut Raschick
    Dec 8, 2003 at 2:04 pm
    Dec 15, 2003 at 6:22 pm
  • Dear all , I have created my tables without OIDS now my doubts are : 1. Will this speed up the data insertion process 2. Though I have not written any code in my any of the pgsql functions which ...
    Sai Hertz And Control SystemsSai Hertz And Control Systems
    Dec 12, 2003 at 4:16 pm
    Dec 15, 2003 at 2:10 am
  • Hello, I am facing a problem trying to put 500 concurrent users accessing a postgresql instance. Basically, the machine begins to do a lot i/o... swap area increases more and more... The vmstat began ...
    Alfranio Correia JuniorAlfranio Correia Junior
    Dec 11, 2003 at 4:22 am
    Dec 12, 2003 at 2:35 am
  • Hi! We have been running a rather busy website using pg 7.3 as the database. Peak hitrate is something like 120 request / second without images and other static stuff. The site is a sort of image ...
    Kari LavikkaKari Lavikka
    Dec 13, 2003 at 6:39 pm
    Dec 16, 2003 at 4:56 pm
  • Hi list, I need to know if there is anything like hints of Oracle in Postgres..otherwise..I wish to find a way to force a query plan to use the indexes or tell the optimizer things like "optimize ...
    Sandra ruizSandra ruiz
    Dec 11, 2003 at 4:00 pm
    Dec 11, 2003 at 8:28 pm
  • As a note, pgsql-performance is a better list for these questions. So, standard questions: How many rows are in the table, what does EXPLAIN ANALYZE show for the queries, if you force index usage ...
    Stephan SzaboStephan Szabo
    Dec 4, 2003 at 3:20 pm
    Dec 5, 2003 at 3:35 pm
  • Folks: I´m running a query which is designed to generate a foreign key for a table of approx. 10 million records (I've mentioned this in an earlier posting). The table is called "indethom", and each ...
    Erik NorvelleErik Norvelle
    Dec 2, 2003 at 3:53 pm
    Dec 2, 2003 at 4:41 pm
  • Hi! I have 4 question which probably someone can answer. 1) I have a transaction during which no data was modified, does it make a difference whether i send COMMIT or ROLLBACK? The effect is the ...
    Evil AzraelEvil Azrael
    Dec 1, 2003 at 1:02 pm
    Dec 1, 2003 at 2:01 pm
  • I consider using PostgreSQL for a project we have in our company and, to get a better picture of the product, I started scanning its source code and internal documentation. Based on what I saw (and ...
    Michael RothschildMichael Rothschild
    Dec 24, 2003 at 4:27 pm
    Dec 25, 2003 at 4:29 pm
  • I just restored a database running on a solaris box to a linux box and queries take forever to execute. The linux box is faster and has twice the memory allocated to postgresql, is there anything ...
    Michael GuerinMichael Guerin
    Dec 22, 2003 at 7:13 pm
    Dec 23, 2003 at 12:34 am
  • Hi, This a kind of newbie-question. I've been using Postgres for a long time in a low transction environment - and it is great. Now I've got an inquiry for using Postgresql in a heavy-load on-line ...
    Conny ThimrenConny Thimren
    Dec 18, 2003 at 5:05 pm
    Dec 22, 2003 at 10:50 pm
  • Hi- After having done my best to squeeze better performance out of our application by tuning within our existing resources, I'm falling back on adding memory as a short-term solution while we get ...
    Nick FankhauserNick Fankhauser
    Dec 17, 2003 at 7:57 pm
    Dec 17, 2003 at 10:19 pm
  • Hello everyone. Can anyone explain why this table which has never had more than a couple rows in it shows 500k in the query planner even after running vacuum full. Its terribly slow to return 2 rows ...
    Chadwick, RussellChadwick, Russell
    Dec 12, 2003 at 10:41 pm
    Dec 17, 2003 at 6:35 pm
  • I have tried to modify the program to more closely resemble WAL writes (all writes to WAL are 8k), the file is usually already open, and test larger (16k) transactions. [email protected]:~ test_sync1 ...
    Andreas ZeugswetterAndreas Zeugswetter
    Dec 12, 2003 at 12:22 pm
    Dec 17, 2003 at 4:58 pm
  • Hi- I'm trying to optimize a query that I *think* should run very fast. Essentially, I'm joining two tables that have very selective indexes and constraining the query on an indexed field. (There's a ...
    Nick Fankhauser - DoxpopNick Fankhauser - Doxpop
    Dec 16, 2003 at 5:06 pm
    Dec 17, 2003 at 3:26 pm
  • I'm running PG 7.2.2 on RH Linux 8.0. I'd like to know why "VACUUM ANALYZE <table " is extemely slow (hours) for certain tables. Here's what the log file shows when I run this command on my ...
    David ShadovitzDavid Shadovitz
    Dec 9, 2003 at 9:21 pm
    Dec 17, 2003 at 5:30 am
  • I am working on migrating to postgres and had some questions regarding optimization that I could not find references in the documentation: 1. Is there any performance difference for declaring a ...
    Sean P. ThomasSean P. Thomas
    Dec 11, 2003 at 4:51 pm
    Dec 17, 2003 at 1:14 am
  • I have stored procedure written in pl/pgsql which takes about 13 seconds to finish. I was able to identify that the slowness is caused by one update SQL: UPDATE shopping_cart SET ...
    Jenny ZhangJenny Zhang
    Dec 16, 2003 at 11:50 pm
    Dec 17, 2003 at 12:04 am
  • Some arbitrary data processing job WAL on single drive: 7.990 rec/s WAL on 2nd IDE drive: 8.329 rec/s WAL on tmpfs: 13.172 rec/s A huge jump in performance but a bit scary having a WAL that can ...
    William YuWilliam Yu
    Dec 12, 2003 at 10:29 pm
    Dec 12, 2003 at 10:59 pm
  • Hi, I've got very slow insert performance on some table which has trigger based on complex PL/pgSQL function. Apparently insert is slow due some slow sql inside that function, since CPU load is very ...
    Aram KananovAram Kananov
    Dec 12, 2003 at 1:17 am
    Dec 12, 2003 at 7:47 am
  • Hello, I use php as front-end to query our database. When I use System Monitor to check the usage of cpu and memory, I noticed that the cpu very easily gets up to 100%. Is that normal? if not, could ...
    LIANHE SHAOLIANHE SHAO
    Dec 5, 2003 at 9:00 pm
    Dec 6, 2003 at 7:42 pm
  • Greetings: Apologies if this question has already been answered, but I was unable to locate a prior answer in the archives... I have a table with approximately 10 million records, called "indethom", ...
    Erik NorvelleErik Norvelle
    Dec 1, 2003 at 1:45 pm
    Dec 1, 2003 at 3:11 pm
  • Hello, Today I met a very strange query problem, which I spend several hours on it but have no clue. To make thing clear, let me write somewhat in detail. I have two almost exactly same queries, ...
    LIANHE SHAOLIANHE SHAO
    Dec 9, 2003 at 11:21 pm
    Dec 15, 2003 at 6:06 pm
  • Hi everyone, I found that performance get worse as the size of a given table increases. I mean, for example I´ve just run some scripts shown in http://www.potentialtech.com/wmoran/postgresql.php I ...
    NbarrazaNbarraza
    Dec 12, 2003 at 6:05 pm
    Dec 12, 2003 at 9:11 pm
  • Hi List, First of all, I tried to subcribe the ODBC list but it seems that the subscription's link is broken ! So here it goes: I have a delphi software use ttable components that converts dbf ...
    Rhaoni Chiu PereiraRhaoni Chiu Pereira
    Dec 12, 2003 at 12:40 pm
    Dec 12, 2003 at 4:13 pm
  • I have some problems on performance using postgresql v. 7.3.2 running on Linux RedHat 9. An update involving several rows (about 500000) on a table having 2800000 tuples takes in the order of 6 ...
    NbarrazaNbarraza
    Dec 10, 2003 at 7:30 pm
    Dec 10, 2003 at 8:35 pm
  • I am using Asynchronous Query Processing interface from libpq library. And I got some strange results on Solaris My test select query is 'SELECT * from pg_user;' and I use select system synchronous ...
    Passynkov, VadimPassynkov, Vadim
    Dec 4, 2003 at 6:22 am
    Dec 4, 2003 at 12:48 pm
  • Thanks to Greg Stark, Tom Lane and Stephan Szabo for their advice on rewriting my query... the revised query plan claims it should only take about half the time my original query did. Now for a ...
    ErikErik
    Dec 3, 2003 at 9:23 pm
    Dec 3, 2003 at 9:33 pm
  • Hello, I am wondering if it is possible to use several machine as cluster to boost the slow queries. Is that possible? Anybody have tried that before? Initially, I was thinking to use dual CPUS ...
    LIANHE SHAOLIANHE SHAO
    Dec 1, 2003 at 10:24 pm
    Dec 1, 2003 at 10:37 pm
  • I guess this may have come up before, but now that 7.4 has the IN with improved performance, it may be time to revisit this topic. Compare these two algorithms (in plpgsql): (a) DELETE FROM foo WHERE ...
    Jonathan GardnerJonathan Gardner
    Dec 30, 2003 at 4:33 pm
    Dec 30, 2003 at 4:33 pm
  • G'day all ... Dave asked me today about 'slow downs' on the search engines, so am looking at the various queries generated by enabling log_statement/log_duration, to get a feel for is something is ...
    Marc G. FournierMarc G. Fournier
    Dec 22, 2003 at 9:15 pm
    Dec 22, 2003 at 9:15 pm
  • Hi, This a kind of newbie-question. I've been using Postgres for a long time in a low transction environment - and it is great. Now I've got an inquiry for using Postgresql in a heavy-load on-line ...
    Conny ThimrenConny Thimren
    Dec 18, 2003 at 7:42 pm
    Dec 18, 2003 at 7:42 pm
  • Hi, I am developing a program using postgres and linux like operating system. My problem is this: I have a quite complicated view with roughly 10000 record. When I execute a simple query like this ...
    Claudia D'amatoClaudia D'amato
    Dec 16, 2003 at 3:41 pm
    Dec 16, 2003 at 3:41 pm
Dec 2003
12
Group Navigation
period‹ prev | Dec 2003 | next ›
Group Overview
grouppgsql-performance @
categoriespostgresql
discussions57
posts315
users94
websitepostgresql.org
irc#postgresql

94 users for December 2003

Tom Lane: 29 posts Neil Conway: 12 posts Ivar Zarans: 11 posts Jack Coates: 10 posts Josh Berkus: 10 posts Richard Huxton: 10 posts Vivek Khera: 10 posts Greg Stark: 9 posts William Yu: 9 posts Scott.marlowe: 8 posts Sean Shanny: 8 posts Stephan Szabo: 8 posts Bruce Momjian: 7 posts Christopher Kings-Lynne: 7 posts Chris Browne: 6 posts David Shadovitz: 6 posts Jared Carr: 6 posts Shridhar Daithankar: 6 posts Eric Soroos: 5 posts Jeff: 5 posts
show more
Archives