postgre performance question
Hello,
I am using a PostgreSQL database and I recently ran to some
problems.
I have a table of around two hunded thousand entries (each entry is
78 bytes) and a simple (selct * from table) query takes a lot of
time to
complete). Moreover a (select * from table where column = (select
oid from another_table)) takes several tens of minutes. An index is
already used for `column'.
The `another_table' has something like 200 entries, while column
takes its values from the OIDs of `another_table'.
The server where the database is installed is a sun4u sparc,
UltraAX-i2 running sunOS 5.8.
Could you please tell me if there is any way to optimase queries on
such big tables?
At some later instance the table will reach million of entries. But
with this
high performance penalty, it would be useless! The table is updated
regularly
and cleaned (every entry of table is removed) on a daily basis.
Thank you for any answer,
Ioannis
Ioannis <Ioannis@dante.org.uk> writes:
Hello,
I am using a PostgreSQL database and I recently ran to some
problems.
I have a table of around two hunded thousand entries (each entry is
78 bytes) and a simple (selct * from table) query takes a lot of
time to
complete). Moreover a (select * from table where column = (select
oid from another_table)) takes several tens of minutes. An index is
already used for `column'.
The `another_table' has something like 200 entries, while column
takes its values from the OIDs of `another_table'.
Sounds as if you're not running VACUUM.
-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863
Import Notes
Reply to msg id not found: Ioannis'smessageofMon04Mar2002115744+0000
....and I really do run vacuum when I clean the table.
Is this the expected performance (a question to those who manage such big
tables), or there might be something wrong with the configuration?
Thanks again,
Ioannis
At 08:46 04/03/02 -0500, Doug McNaught wrote:
Show quoted text
Ioannis <Ioannis@dante.org.uk> writes:
Hello,
I am using a PostgreSQL database and I recently ran to some
problems.
I have a table of around two hunded thousand entries (each entry is
78 bytes) and a simple (selct * from table) query takes a lot of
time to
complete). Moreover a (select * from table where column = (select
oid from another_table)) takes several tens of minutes. An index is
already used for `column'.
The `another_table' has something like 200 entries, while column
takes its values from the OIDs of `another_table'.Sounds as if you're not running VACUUM.
-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863
....and I really do run vacuum when I clean the table.
Is this the expected performance (a question to those who manage so big
tables), or there might be something wrong with the configuration?
Thanks again,
Ioannis
At 08:46 04/03/02 -0500, Doug McNaught wrote:
Show quoted text
Ioannis <Ioannis@dante.org.uk> writes:
Hello,
I am using a PostgreSQL database and I recently ran to some
problems.
I have a table of around two hunded thousand entries (each entry is
78 bytes) and a simple (selct * from table) query takes a lot of
time to
complete). Moreover a (select * from table where column = (select
oid from another_table)) takes several tens of minutes. An index is
already used for `column'.
The `another_table' has something like 200 entries, while column
takes its values from the OIDs of `another_table'.Sounds as if you're not running VACUUM.
-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863
Ioannis <Ioannis.Kappas@dante.org.uk> writes:
....and I really do run vacuum when I clean the table.
You should run it more often than that. Any update or delete on the
table will result in space that can't be reused until you run VACUUM.
PG has to scan all that empty space to make sure there are no valid
records in it whenever you do a query.
Run it at least every night.
-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863
Import Notes
Reply to msg id not found: Ioannis'smessageofMon04Mar2002140758+0000
... it really does clean the table at midnight and then immediately vacuums
the table after it.
What it really does is to populate the table with two hundred thousand of
entries each day and
later on the table will be populated with million of entries each day.
Again at midnight, all the entries from the table are removed and the table
is vacuumed (I want to make this clear).
Do you think this is the expected behaviour I am getting? Can I do
something to improve the
perfrormance? Should I try to find another database that can handle such
`big?' amount of entries?
Can I change something on the configuration of the database that will speed
up the queries?
Thanks a lot,
Ioannis
At 09:35 04/03/02 -0500, Doug McNaught wrote:
Show quoted text
Ioannis <Ioannis.Kappas@dante.org.uk> writes:
....and I really do run vacuum when I clean the table.
You should run it more often than that. Any update or delete on the
table will result in space that can't be reused until you run VACUUM.
PG has to scan all that empty space to make sure there are no valid
records in it whenever you do a query.Run it at least every night.
-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863
Ioannis Kappas <Ioannis.Kappas@dante.org.uk> writes:
... it really does clean the table at midnight and then immediately
vacuums the table after it.
What it really does is to populate the table with two hundred thousand
of entries each day and
later on the table will be populated with million of entries each day.
Again at midnight, all the entries from the table are removed and the
table is vacuumed (I want to make this clear).
Thanks for the clarification. Are you doing a lot of updates during
the day, or just inserts?
Do you think this is the expected behaviour I am getting? Can I do
something to improve the
perfrormance? Should I try to find another database that can handle
such `big?' amount of entries?
Can I change something on the configuration of the database that will
speed up the queries?
Well, if you're selecting every record from a table with millions of
records, any database is going to be slow. There, the bottleneck is
disk i/o and how fast the server can send data to the client.
For more selective queries, make sure you:
1) VACUUM ANALYZE (or just ANALYZE in 7.2) after the table is populated.
2) Put indexes on the appropriate columns (depends on what queries you
make).
Without seeing your schema and the queries you're running, it's hard
to give you any more advice.
-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863
Import Notes
Reply to msg id not found: IoannisKappas'smessageofMon04Mar2002145837+0000
Again at midnight, all the entries from the table are removed and the
table is vacuumed (I want to make this clear).
If you are "removing all of the the entries" from the table, and then
vacuuming/analysing, then the stats table will be updated for the object
with no rows in it. Query plans for any select from that point on, will be
forced to do a table scan.
----- Original Message -----
From: "Doug McNaught" <doug@wireboard.com>
To: "Ioannis Kappas" <Ioannis.Kappas@dante.org.uk>
Cc: <pgsql-general@postgresql.org>
Sent: Tuesday, March 05, 2002 2:34 AM
Subject: Re: [GENERAL] postgre performance question
Show quoted text
Ioannis Kappas <Ioannis.Kappas@dante.org.uk> writes:
... it really does clean the table at midnight and then immediately
vacuums the table after it.
What it really does is to populate the table with two hundred thousand
of entries each day and
later on the table will be populated with million of entries each day.
Again at midnight, all the entries from the table are removed and the
table is vacuumed (I want to make this clear).Thanks for the clarification. Are you doing a lot of updates during
the day, or just inserts?Do you think this is the expected behaviour I am getting? Can I do
something to improve the
perfrormance? Should I try to find another database that can handle
such `big?' amount of entries?
Can I change something on the configuration of the database that will
speed up the queries?Well, if you're selecting every record from a table with millions of
records, any database is going to be slow. There, the bottleneck is
disk i/o and how fast the server can send data to the client.For more selective queries, make sure you:
1) VACUUM ANALYZE (or just ANALYZE in 7.2) after the table is populated.
2) Put indexes on the appropriate columns (depends on what queries you
make).Without seeing your schema and the queries you're running, it's hard
to give you any more advice.-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
On Monday 04 March 2002 15:07, you wrote:
....and I really do run vacuum when I clean the table.
Yes, but did you run 'vacuum analyze' on the filled DB? Else, your indices
will not be used...
If so, could you provide us with table structure, query and query plan?
Is this the expected performance (a question to those who manage so big
tables), or there might be something wrong with the configuration?Thanks again,
IoannisAt 08:46 04/03/02 -0500, Doug McNaught wrote:
Ioannis <Ioannis@dante.org.uk> writes:
Hello,
I am using a PostgreSQL database and I recently ran to some
problems.
I have a table of around two hunded thousand entries (each entry is
78 bytes) and a simple (selct * from table) query takes a lot of
time to
complete). Moreover a (select * from table where column = (select
oid from another_table)) takes several tens of minutes. An index is
already used for `column'.
The `another_table' has something like 200 entries, while column
takes its values from the OIDs of `another_table'.Sounds as if you're not running VACUUM.
-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
Greetings,
Joerg
--
Leading SW developer - S.E.A GmbH
Mail: joerg.hessdoerfer@sea-gmbh.com
WWW: http://www.sea-gmbh.com
I've noticed that if I blow away the contents of a table and then
repopulate, I only get back to good performance with a vacuum full,
instead of just a simple vacuum. Perhaps I'm doing something wrong as
well.....
On Mon, 4 Mar 2002, Ioannis Kappas wrote:
Show quoted text
... it really does clean the table at midnight and then immediately vacuums
the table after it.
What it really does is to populate the table with two hundred thousand of
entries each day and
later on the table will be populated with million of entries each day.
Again at midnight, all the entries from the table are removed and the table
is vacuumed (I want to make this clear).Do you think this is the expected behaviour I am getting? Can I do
something to improve the
perfrormance? Should I try to find another database that can handle such
`big?' amount of entries?
Can I change something on the configuration of the database that will speed
up the queries?Thanks a lot,
IoannisAt 09:35 04/03/02 -0500, Doug McNaught wrote:
Ioannis <Ioannis.Kappas@dante.org.uk> writes:
....and I really do run vacuum when I clean the table.
You should run it more often than that. Any update or delete on the
table will result in space that can't be reused until you run VACUUM.
PG has to scan all that empty space to make sure there are no valid
records in it whenever you do a query.Run it at least every night.
-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?