Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id eBTMY6L33257 for ; Fri, 29 Dec 2000 17:34:06 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from femail9.sdc1.sfba.home.com (femail9.sdc1.sfba.home.com [24.0.95.89]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBNH82673184 for ; Sat, 23 Dec 2000 12:08:02 -0500 (EST) (envelope-from joe@conway-family.com) Received: from jecw2k1 ([24.0.42.111]) by femail9.sdc1.sfba.home.com (InterMail vM.4.01.03.00 201-229-121) with SMTP id <20001223170732.QSNR21435.femail9.sdc1.sfba.home.com@jecw2k1> for ; Sat, 23 Dec 2000 09:07:32 -0800 Message-ID: <018601c06d02$d007ace0$0705a8c0@jecw2k1> From: "Joe Conway" To: "PostgreSQL-SQL" References: Subject: Optimization recommendations request Date: Sat, 23 Dec 2000 09:07:20 -0800 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-Archive-Number: 200012/289 X-Sequence-Number: 626 Hello, I'm working on an application where I need to design for one table to grow to an extremely large size. I'm already planning to partition the data into multiple tables, and even possibly multiple servers, but even so each table may need to grow to the 10 - 15 million tuple range. This table will be used for a keyed lookup and it is very important that the query return in well under a second. I've done a small test using a dual ppro 200 server with 512 MB RAM, running RedHat Linux 6.1, and a late November 7.1 pre-beta snapshot of PostgreSQL (PGOPTS="-D/opt/pgsql/data -i -B 25000"). I used a test table with about 5 million tuples. Details: CREATE TABLE foo( guid varchar(20) not null, ks varchar(20) not null ); --> insert 5,000,000 rows, both guid and ks are 20 byte sha-1 hashes -- tried this first -- create index foo_idx1 on foo(guid); -- then tried create index foo_idx1 on foo using HASH (guid); SELECT ks FROM foo WHERE guid = 'f9ee1930f4010ba34cc5ca6efd27d988eb4f921d'; The query currently takes in excess of 40 seconds. I would appreciate any suggestions for optimizing to bring this down substantially. Thanks in advance, Joe Conway