pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimization recommendations request
5+ messages / 4 participants
[nested] [flat]
* Optimization recommendations request
@ 2000-12-23 17:07 Joe Conway <joe@conway-family.com>
2000-12-29 23:12 ` Re: Optimization recommendations request Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-12-29 23:49 ` Re: Optimization recommendations request Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Joe Conway @ 2000-12-23 17:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Optimization recommendations request
2000-12-23 17:07 Optimization recommendations request Joe Conway <joe@conway-family.com>
@ 2000-12-29 23:12 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-12-29 23:39 ` Re: Optimization recommendations request Joe Conway <jconway2@home.com>
1 sibling, 1 reply; 5+ messages in thread
From: Stephan Szabo @ 2000-12-29 23:12 UTC (permalink / raw)
To: Joe Conway <joe@conway-family.com>; +Cc: pgsql-sql
What does explain show for your query?
On Sat, 23 Dec 2000, Joe Conway wrote:
> 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Optimization recommendations request
2000-12-23 17:07 Optimization recommendations request Joe Conway <joe@conway-family.com>
2000-12-29 23:12 ` Re: Optimization recommendations request Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2000-12-29 23:39 ` Joe Conway <jconway2@home.com>
0 siblings, 0 replies; 5+ messages in thread
From: Joe Conway @ 2000-12-29 23:39 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
Subject: Re: [SQL] Optimization recommendations request
>
> What does explain show for your query?
>
I sent this a week ago using the wrong (i.e. not the one I signed up with)
reply-to address, so it didn't make it to the list until just now. In the
meantime I ran explain and noticed that the index wasn't being used. So I
ran vacuum analyze and now I'm getting the expected performance (and explain
shows the index being used). If I understand the logged statistics
correctly, I'm getting results returned in anywhere from about 3 to 45 ms,
depending on cache hit rate. I also repeated my test with 15 million records
with similar results. Not bad at all!
I am still interested in any generic optimization tips for very large
tables.
Thanks for taking the time to reply!
Joe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Optimization recommendations request
2000-12-23 17:07 Optimization recommendations request Joe Conway <joe@conway-family.com>
@ 2000-12-29 23:49 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2000-12-29 23:49 UTC (permalink / raw)
To: Joe Conway <joe@conway-family.com>; +Cc: pgsql-sql
"Joe Conway" <joe@conway-family.com> writes:
> 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.
Why are you using a hash index? btree is a lot better for every
conceivable purpose.
It would also be a good idea to check to make sure that the query is
using the index (see EXPLAIN). You didn't mention whether you'd done
a VACUUM ANALYZE, so there's a risk the planner will make the wrong
choice.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Fw: Optimization recommendations request
@ 2000-12-24 02:35 Joe Conway <jconway2@home.com>
0 siblings, 0 replies; 5+ messages in thread
From: Joe Conway @ 2000-12-24 02:35 UTC (permalink / raw)
To: 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).
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2000-12-29 23:49 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-12-23 17:07 Optimization recommendations request Joe Conway <joe@conway-family.com>
2000-12-29 23:12 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-12-29 23:39 ` Joe Conway <jconway2@home.com>
2000-12-29 23:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-12-24 02:35 Fw: Optimization recommendations request Joe Conway <jconway2@home.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox