Skip site navigation (1) Skip section navigation (2)

Fw: Optimization recommendations request

From: "Joe Conway" <jconway2(at)home(dot)com>
To: "PostgreSQL-SQL" <pgsql-sql(at)postgresql(dot)org>
Subject: Fw: Optimization recommendations request
Date: 2000-12-24 02:35:05
Message-ID: 026201c06d52$1fde92c0$0705a8c0@jecw2k1 (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
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).



> 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
> multiple tables, and even possibly multiple servers, but even so each
> may need to grow to the 10 - 15 million tuple range. This table will be
> 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
> MB RAM, running RedHat Linux 6.1, and a late November 7.1 pre-beta
> of PostgreSQL (PGOPTS="-D/opt/pgsql/data -i  -B 25000"). I used a test
> with about 5 million tuples.
> Details:
>     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 =
> 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

pgsql-sql by date

Next:From: Keith WongDate: 2000-12-24 03:44:01
Subject: Re: Create table doesn't work in plpgsql
Previous:From: xyziiDate: 2000-12-23 19:16:07
Subject: left join ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group