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 eBO4BT601880 for ; Sat, 23 Dec 2000 23:11:29 -0500 (EST) (envelope-from jconway2@home.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 <20001224023515.HFAT21435.femail9.sdc1.sfba.home.com@jecw2k1> for ; Sat, 23 Dec 2000 18:35:15 -0800 Message-ID: <026201c06d52$1fde92c0$0705a8c0@jecw2k1> From: "Joe Conway" To: "PostgreSQL-SQL" Subject: Fw: Optimization recommendations request Date: Sat, 23 Dec 2000 18:35:05 -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/266 X-Sequence-Number: 603 Well, this message never made it through, but I managed to answer my own question -- I never ran vacuum analyze which caused a table scan instead of an index scan. After running vacuum analyze the query returns immediately. I would still be interested in any hints to optimize performance for very large tables (>10M tuples). Thanks, Joe > 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 >