--thanks for the reply.
--pretty slow i guess is relative to other
--applications. there are other factors
--that i didn't put in (sorry).
* clients are using MS Access to grab
the data
* the server's load average is pretty
high (in our book, anything over 4 is
high)
--i have been *told* by my boss that the
--query is using the indexed columns in
--her where clause. i can only go by
--that - i will a copy of her query
--and test using 'explain analyze'.
--i started the vacuum verbose about 20
--minutes ago - i will see if it is any
--better after that.
--haven't done a vaccumdb / analyze
--on the entire db lately because of
--the errors i get
[snip from log]
FATAL 2: open of /var/lib/pgsql/data/pg_clog/0003 failed: No such file or
directory
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
[/snip from log]
--thanks again!
-X
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
On Friday 20 Jun 2003 3:02 pm, Johnson, Shaunn wrote:
> Howdy:
>
> Running PostgreSQL 7.2.1 on RedHat Linux 7.2.
>
> I have a table with about 10 million records and
> even though it's indexed on three key columns,
> it's still pretty slow, I believe, because of the
> size.
Depends what you mean by "pretty slow" - can you provide us with and example
(EXPLAIN ANALYSE SELECT ...) and details of the schema?
> Is there a way to split the data so that I
> can access all the records, but maintain
> smaller tables?
You could split the table and build a view that UNIONs the individual tables,
but that's not very elegant and 10 million records isn't much.
Things to check for:
- Have you run VACCUM FULL or VACUUM ANALYSE recently?
- Are your "where" data-types the same as the index data-types?
- See if the indexes are used in the EXPLAIN ANALYSE output.
--
Richard Huxton