Thread: 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
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
> > 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 > 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 > >
....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: >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 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: >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
... 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: >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
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, > Ioannis > > At 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? > > http://archives.postgresql.org 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: > ... 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: > >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? > > http://www.postgresql.org/users-lounge/docs/faq.html >