Thread: postgre performance question

postgre performance question

From
Ioannis
Date:
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


Re: postgre performance question

From
Doug McNaught
Date:
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

Re: postgre performance question

From
Doug McNaught
Date:
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

Re: postgre performance question

From
Doug McNaught
Date:
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

Re: postgre performance question

From
"Andrew Bartley"
Date:
> > 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
>
>


Re: postgre performance question

From
Ioannis
Date:
....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


Re: postgre performance question

From
Ioannis Kappas
Date:
....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


Re: postgre performance question

From
Ioannis Kappas
Date:
... 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


Re: postgre performance question

From
Joerg Hessdoerfer
Date:
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

Re: postgre performance question

From
Ben
Date:
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
>