Postgres Pro
Downloads
Home   >   mailing lists

Thread: performance for insert / update

performance for insert / update

From
Catalin Constantin
Date:
Hello,

I have a pretty big database with about 200 000 rows.
This is the main table. Also some other tables with FKs to this main
table.

I have to calculate some numbers for each entry at a certain amount of
time and update the DB.

I've noticed the update TAKES a very long time.

For example i have a second table which has 3 columns:
1) the FK to the main big table
2) number 1
3) number 2

After the calculation is done i execute many update table2 set
number1=%d, number2=%d.

It takes quite a lot of time.

Any clue of how can i improve this time ?

I did some tests with other engines.

Eg: Mysql (no FKs) - aprox 10 times faster
SQLite - aprox 5 time faster

I tryed to set fsync = false but no luck either.
It still takes quite a lot of time.

I can share the complete test results if somebody is interested.

Thanks for your help !

p.s.: postgresql-7.4.7 is the version i have !

--
Catalin Constantin
Bounce Software
http://www.bounce-software.com
http://www.cabanova.ro

Re: performance for insert / update

From
Bruno Wolff III
Date:
On Mon, Jun 27, 2005 at 18:46:58 +0300,
  Catalin Constantin <catalin@bounce-software.com> wrote:
> Hello,
>
> I have a pretty big database with about 200 000 rows.
> This is the main table. Also some other tables with FKs to this main
> table.
>
> I have to calculate some numbers for each entry at a certain amount of
> time and update the DB.
>
> I've noticed the update TAKES a very long time.

Do you have indexes on the foreign key fields in the referencing tables?
These are created by default and if you are updating the referenced tupples
a sequential search will be needed if there isn't an index.

Also of note is that there is recent a change to only do this if the referenced
fields in the record are changed, but I think this is new for 8.1. That
will make things go a lot faster if you aren't updating the referenced
fields in your main table.

Re: performance for insert / update

From
Catalin Constantin
Date:
this is my "schema" for the table with the "issue" !

# \d url_importance
                   Table "public.url_importance"
  Column   |       Type       |              Modifiers
-----------+------------------+-------------------------------------
 url_id    | bigint           | default nextval('url_id_seq'::text)
 links_in  | integer          | default 0
 links_out | integer          | default 0
 rank      | double precision | default 0
Indexes:
    "ak_url_id_key_url_impo" unique, btree (url_id)
Foreign-key constraints:
    "fk_url_impo_reference_url" FOREIGN KEY (url_id) REFERENCES url(url_id) ON UPDATE CASCADE ON DELETE CASCADE


based on this table i calculate the "rank" for each page and the
reupdate the table.

on update it takes QUITE a lot time to make the update.

basically i am updating just a number which sould not be that resource
consuming !

is upgrading to 8.X a "solution" in this case ?

p.s.: i am not updating anything except the rank column !

Tuesday, June 28, 2005, 4:01:02 PM, Bruno Wolff III wrote:
> Do you have indexes on the foreign key fields in the referencing tables?
> These are created by default and if you are updating the referenced tupples
> a sequential search will be needed if there isn't an index.

> Also of note is that there is recent a change to only do this if the referenced
> fields in the record are changed, but I think this is new for 8.1. That
> will make things go a lot faster if you aren't updating the referenced
> fields in your main table.


--
Catalin Constantin
Bounce Software
http://www.bounce-software.com
http://www.cabanova.ro