Re: constraint with reference to the same table

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 ;) ).