Re: Performance Problem

On Thu, Oct 12, 2006 at 10:26:28PM -0700, roopa perumalraja wrote:
> I am new to postgres and I have 4 doubts.
>
> 1) I have a performance problem as I am trying to insert around 60
> million rows to a table which is partitioned. So first I copied the
> .csv file which contains data, with COPY command to a temp table
> which was quick. It took only 15 to 20 minutes. Now I am inserting
> data from temp table to original table using insert into org_table
> (select * from temp_table); which is taking more than an hour & is
> still inserting. Is there an easy way to do this?

Does the table you're inserting into have indexes or foreign keys?
Either of those slow down loading considerably. One commen workaround
is to drop the indexes and constraints, load the data and re-add them.

> 2) I want to increase the performance of database as I find it very
> slow which has more than 60 million rows in one table. I increased
> the shared_buffer parameter in postgres.conf file to 20000 but that
> does help much.

Find out the queries that are slow and use EXPLAIN to identify possible
useful indexes.

> 2) I have partitioned a parent table into 100 child tables so when
> I insert data to parent table, it automatically inserts to child
> table. I have followed
> http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html.
> When I did this, I noticed that when I viewed data of parent table,
> it had the rows of the child table and is not empty. But the child
> tables do have the rows in it. I don’t understand.

When you select from a parent table, it shows the rows of the child
tables also, that's kind of the point. You can say: SELECT * FROM ONLY
parent;

The partitioning may only explain the slow loading...

> 3) I want to use materialized views, I don’t understand it from
> http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html,
> can anyone explain me with a simple example.

Can't help you there...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.