Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f6GGwua13470 for ; Mon, 16 Jul 2001 12:58:56 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6B4EIa96270; Wed, 11 Jul 2001 00:14:18 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f6B3ukB04599; Tue, 10 Jul 2001 23:56:46 -0400 (EDT) (envelope-from news) From: Raymond Chui X-Newsgroups: comp.databases.postgresql.sql, comp.databases.postgresql.general, comp.databases.postgresql.questions Subject: Performance tuning in PostgreSQL? Date: Tue, 10 Jul 2001 08:17:03 -0400 Organization: Department of Commerce/NOAA/NWS Lines: 90 Message-ID: <3B4AF23F.D7D4B1@noaa.gov> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="------------DD291FB39F02D329E6D629F3" X-Mailer: Mozilla 4.75 [en] (Win98; U) X-Accept-Language: en To: pgsql-general@postgresql.org.pgsql-sql@postgresql.org X-Archive-Number: 200107/205 X-Sequence-Number: 4000 This is a multi-part message in MIME format. --------------DD291FB39F02D329E6D629F3 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi dear dudes (buddies)! I have have a table with 8 columns, the primary key are column ID and orders. CREATE TABLE tablename ( ID char(8) not null, orders integer not null, col3 float(6) DEFAULT 0.01, col4 integer DEFAULT 15, col5 float(6) DEFAULT 0.0, col6 char(5) not null, col7 char(2) not null, col8 timestamp DEFAULT 1970-01-01 00:00:00, primary key (ID, orders), check (orders >= 0)); CREATE INDEX indexname ON tablename (ID, orders); There are more than 25 thousand rows in the table. This is a relative small database compare to Wal-Mark, Macy (big retail stores) inventory. But I have some performance issue. When I randomly query the table SELECT columnlist FROM tablename WHERE ID = '12345678' ORDER BY orders; It sometime take few seconds to display output query, sometime display output query instantly. This tell me when the ID I query is in the cache memory, I got output results instantly, when the ID is not in cache memory, I have to wait for few seconds. Because when I randomly pick an ID for above SELECT statement, 1st time I need to wait for few seconds, if I do the same SELECT again, I got the query output immediately/instantly. To wait for few seconds is not a big deal for most of application. If you doing online shopping, wait for few seconds is very fast. But for our application is close to the real-time application. We can't have any wait, must no waiting at all time. In other database systems, such as Informix, Sybase, etc. The database is stored in the raw disk partitions, you can config the database system into different partitions, different disks, slice into different trunks, etc. But PostgreSQL is stored the database in the file system in PGDATA directory. So how do I boost the performance in PostgreSQL? Thank you very much in advance! --Raymond --------------DD291FB39F02D329E6D629F3 Content-Type: text/x-vcard; charset=us-ascii; name="raymond.chui.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Raymond Chui Content-Disposition: attachment; filename="raymond.chui.vcf" begin:vcard n:Chui;Raymond tel;fax:(301)713-0963 tel;work:(301)713-0624 Ext. 168 x-mozilla-html:TRUE url:http://members.xoom.com/rchui/ org:NWS, NOAA version:2.1 email;internet:Raymond.Chui@noaa.gov title:SA, DBA note:ICQ #: 16722494 adr;quoted-printable:;;NOAA, NWS, Office of Hydrology, OH=0D=0A1325 East-West Highway, Room 8112;Silver Spring;MD;20910-3283;U.S.A. x-mozilla-cpt:;-6384 fn:Raymond Chui end:vcard --------------DD291FB39F02D329E6D629F3--