Re: splitting a table?

--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(at)archonet(dot)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