Обсуждение: 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
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
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. >
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.
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