X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 6D75A4764D7 for ; Wed, 14 May 2003 19:51:39 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 71037-05 for ; Wed, 14 May 2003 19:51:37 -0400 (EDT) Received: from water.oasis.net.au (water.oasis.net.au [210.8.139.2]) by postgresql.org (Postfix) with ESMTP id 8B38C4764AD for ; Wed, 14 May 2003 19:51:36 -0400 (EDT) Received: from oasis.net.au (guardian [210.8.139.5]) by water.oasis.net.au (8.9.3/8.9.3/Debian 8.9.3-21) with ESMTP id KAA15851 for ; Thu, 15 May 2003 10:01:03 +1000 X-Authentication-Warning: water.oasis.net.au: Host guardian [210.8.139.5] claimed to be oasis.net.au Message-ID: <3EC2D7D5.1010404@oasis.net.au> Date: Thu, 15 May 2003 09:57:09 +1000 From: Rudi Starcevic User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: en-us, en MIME-Version: 1.0 Cc: Postgres Performance Subject: Re: constraint with reference to the same table References: <20030514162559.O51040-100000@megazone23.bigpanda.com> In-Reply-To: <20030514162559.O51040-100000@megazone23.bigpanda.com> Content-Type: multipart/alternative; boundary="------------030507030600090204060106" X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/158 X-Sequence-Number: 1937 This is a multi-part message in MIME format. --------------030507030600090204060106 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Hi, Can I confirm what this means then .. For large table's each column with ref. inegritry I should create an index on those columns ? So if I create a table like this : CREATE TABLE business_businesstype ( b_bt_id serial PRIMARY KEY, b_id integer REFERENCES business ON UPDATE CASCADE ON DELETE CASCADE NOT NULL, bt_id integer REFERENCES businesstype ON UPDATE CASCADE ON DELETE CASCADE NOT NULL ); I should then create 2 index's CREATE INDEX business_idx ON business_businesstype (business); CREATE INDEX businesstype_idx ON business_businesstype (businesstype); Thanks Regards Rudi. Stephan Szabo wrote: >On Thu, 15 May 2003, Victor Yegorov wrote: > > > >>I'm using PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66. >> >>Here is topic. Table transactions: >> >>=> \d transactions >> Table "public.transactions" >> Column | Type | Modifiers >>-------------+--------------+----------- >> trxn_id | integer | not null >> trxn_ret | integer | >> trxn_for | integer | >> status | numeric(2,0) | not null >> auth_status | numeric(2,0) | not null >>Indexes: transactions_pkey primary key btree (trxn_id) >>Foreign Key constraints: trxns_id FOREIGN KEY (trxn_id) REFERENCES connections(conn_id) ON UPDATE NO ACTION ON DELETE NO ACTION, >> trxns_ret FOREIGN KEY (trxn_ret) REFERENCES transactions(trxn_id) ON UPDATE NO ACTION ON DELETE NO ACTION, >> trxns_for FOREIGN KEY (trxn_for) REFERENCES transactions(trxn_id) ON UPDATE NO ACTION ON DELETE NO ACTION >> >>As you can see, trxns_ret and trxns_for constraints references to the same table they come from. >> >>Maintenance of system includes the following step: >>delete from transactions where transactions.trxn_id = uneeded_trxns.trxn_id; >>transactions volume is about 10K-20K rows. >>uneeded_trxns volume is about 3K-5K rows. >> >> >>Problem: It takes to MUCH time. EXPLAIN says: >> >>I was waiting for about 30 minutes and then hit ^C. >> >>After some time spent dropping indexes and constraints, I've found out, that problem was in >>those 2 "cyclic" constraints. After drop, query passed in some seconds (that is suitable). >> >>Question: why so? >> >> > >For each row dropped it's making sure that no row has either a trxn_ret or >trxn_for that pointed to that row. If those columns aren't indexed it's >going to be amazingly slow (if they are indexed it'll probably only be >normally slow ;) ). > > > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > > > --------------030507030600090204060106 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Hi,

Can I confirm what this means then ..

For large table's each column with ref. inegritry I should create an index on those columns ?

So if I create a table like this :
CREATE TABLE business_businesstype
(
b_bt_id serial PRIMARY KEY,
b_id integer REFERENCES business ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
bt_id integer REFERENCES businesstype ON UPDATE CASCADE ON DELETE CASCADE NOT NULL
);

I should then create 2 index's

CREATE  INDEX business_idx ON  business_businesstype (business);
CREATE  INDEX businesstype_idx ON  business_businesstype (businesstype);

Thanks
Regards
Rudi.



Stephan Szabo wrote:
On Thu, 15 May 2003, Victor Yegorov wrote:

  
I'm using PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66.

Here is topic. Table transactions:

=> \d transactions
      Table "public.transactions"
   Column    |     Type     | Modifiers
-------------+--------------+-----------
 trxn_id     | integer      | not null
 trxn_ret    | integer      |
 trxn_for    | integer      |
 status      | numeric(2,0) | not null
 auth_status | numeric(2,0) | not null
Indexes: transactions_pkey primary key btree (trxn_id)
Foreign Key constraints: trxns_id FOREIGN KEY (trxn_id) REFERENCES connections(conn_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
                         trxns_ret FOREIGN KEY (trxn_ret) REFERENCES transactions(trxn_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
                         trxns_for FOREIGN KEY (trxn_for) REFERENCES transactions(trxn_id) ON UPDATE NO ACTION ON DELETE NO ACTION

As you can see, trxns_ret and trxns_for constraints references to the same table they come from.

Maintenance of system includes the following step:
delete from transactions where transactions.trxn_id = uneeded_trxns.trxn_id;
transactions volume is about 10K-20K rows.
uneeded_trxns volume is about 3K-5K rows.


Problem: It takes to MUCH time. EXPLAIN says:

I was waiting for about 30 minutes and then hit ^C.

After some time spent dropping indexes and constraints, I've found out, that problem was in
those 2 "cyclic" constraints. After drop, query passed in some seconds (that is suitable).

Question: why so?
    

For each row dropped it's making sure that no row has either a trxn_ret or
trxn_for that pointed to that row.  If those columns aren't indexed it's
going to be amazingly slow (if they are indexed it'll probably only be
normally slow ;) ).



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

  

--------------030507030600090204060106--