Thread: Performance Expectations

Performance Expectations

From
"Derek Hamilton"
Date:
Hello all,

We're using PostgresQL with a fairly large database (about 2GB).  I have one
table that currently exceeds 4.5 million records and will probably grow to
well over 5 fairly soon.  The searching of this table is basically done on
one field, which field I have set up a btree index on.  My question is, if I
search this table and get the results back in about 6-7 seconds is that
pretty good, not so good...?  What are the things I should look at in
determining the performance on this?

BTW, forgive the lack of information.  I'd be happy to post more info on the
table, hardware, etc.  I just didn't want to overwhelm the initial question.

Thanks,
Derek Hamilton


Re: Performance Expectations

From
"Marc Mitchell"
Date:
I would suggest simply doing an EXPLAIN on your query and examining the
output.  It will tell you whether your index is being used or not.  That's
a big first step in determining what your optimal performance expectations
should be.

Marc Mitchell - Senior Application Architect
Enterprise Information Solutions, Inc.
4910 Main Street
Downers Grove, IL 60515
(630) 512-0570
marcm@eisolution.com

----- Original Message -----
From: "Derek Hamilton" <derek@capweb.com>
To: <pgsql-admin@postgresql.org>
Sent: Friday, April 18, 2003 1:24 PM
Subject: [ADMIN] Performance Expectations


> Hello all,
>
> We're using PostgresQL with a fairly large database (about 2GB).  I have
one
> table that currently exceeds 4.5 million records and will probably grow
to
> well over 5 fairly soon.  The searching of this table is basically done
on
> one field, which field I have set up a btree index on.  My question is,
if I
> search this table and get the results back in about 6-7 seconds is that
> pretty good, not so good...?  What are the things I should look at in
> determining the performance on this?
>
> BTW, forgive the lack of information.  I'd be happy to post more info on
the
> table, hardware, etc.  I just didn't want to overwhelm the initial
question.
>
> Thanks,
> Derek Hamilton
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


Re: Performance Expectations

From
"Derek Hamilton"
Date:
Hey Marc,

I've run the EXPLAIN statement on the query and it does use the index.

I also use an "order by" on the table that does not include the indexed
field.  Would adding an index for the fields I'm ordering by help at all?

Thanks,
Derek

> I would suggest simply doing an EXPLAIN on your query and examining the
> output.  It will tell you whether your index is being used or not.  That's
> a big first step in determining what your optimal performance expectations
> should be.
>


Re: Performance Expectations

From
Bruno Wolff III
Date:
On Fri, Apr 18, 2003 at 12:55:35 -0700,
  Derek Hamilton <derek@capweb.com> wrote:
> Hey Marc,
>
> I've run the EXPLAIN statement on the query and it does use the index.
>
> I also use an "order by" on the table that does not include the indexed
> field.  Would adding an index for the fields I'm ordering by help at all?

Maybe. If the search returns only a small portion of the records in the
table it is probably more efficient to use the current index for searching
and then do a sort on the returned records rather than scan the table
in the output order selecting records that match the search criteria.
Also extra indexes will slow down inserts and updates so if you are doing
a lot of those, you need to balance that against your select performance.


Re: Performance Expectations

From
Steve Crawford
Date:
Hard to say. Here are the initial questions I have:

What is the nature of the query? Returns single row or 10,000? Single table
or multiple joined tables? Aggregates? Grouping?

Is the database updated often? How about vacuumed to manage on-disk size?

Is data delivered locally or over the wire? What is the client?

What platform (CPU, RAM, Disk, OS) and PG version?

What is the system load - single PG user or hundreds of simultaneous queries
and running the web server and mail server, too? What does sar report for CPU
and disk load?

If you are using a 7.3 version, what does psql report when you run the query
with \timing on?

Out of curiosity I just did a quick search for all records for a single hour
from a table that has part of our phone bill from last year (1.8 million
records) and the result (810 records) was returned in under 0.5 seconds. This
table resides on my desktop so I'm the only user but it's also far from a
"server-class" machine.

Cheers,
Steve



On Friday 18 April 2003 11:24 am, Derek Hamilton wrote:
> Hello all,
>
> We're using PostgresQL with a fairly large database (about 2GB).  I have
> one table that currently exceeds 4.5 million records and will probably grow
> to well over 5 fairly soon.  The searching of this table is basically done
> on one field, which field I have set up a btree index on.  My question is,
> if I search this table and get the results back in about 6-7 seconds is
> that pretty good, not so good...?  What are the things I should look at in
> determining the performance on this?
>
> BTW, forgive the lack of information.  I'd be happy to post more info on
> the table, hardware, etc.  I just didn't want to overwhelm the initial
> question.
>
> Thanks,
> Derek Hamilton
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster