
I posted to -sql the other day about an atrociously slow DELETE on a table 
that has two FKs to a 'parent' table ... if the # of records in the table 
that match the condition is 1, its fast ... in the sample I'm working 
with, there are 1639 records in the table ...

Now, I'm making a guess that for each row that needs to be DELETEd, the FK 
forces a 'SELECT * FROM fk_table WHERE fk = value', to check for its 
existence ... so, in this case, we're talking about 1639*2 SELECTs to the 
backend ... is this correct?

If this is correct ... is 7.4/8.0 any smarter when it comes to 
'duplicates'?  Somehow keeping a list of 'fk = value's that have already 
been checked, instead of re-issuing a new SELECT for each row?  In the 
case of the table I'm working on, all row DELETEs would have the same 
result, as the delete is *on* the FK value itself, so the first check of 
the fk_table should be all that is required ...

Not sure if this is even possible ... or is already done ...

Note that I'm working on a 7.3 database right now, so if this is something 
that is improved with 7.4, please let me know ..

  ----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Sun, 10 Oct 2004 19:13:59 -0300 (ADT)
"Marc G. Fournier" <scrappy@postgresql.org>
Speeding up DELETEs on table with FKs ... 
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> I posted to -sql the other day about an atrociously slow DELETE on a table 
> that has two FKs to a 'parent' table ... if the # of records in the table 
> that match the condition is 1, its fast ... in the sample I'm working 
> with, there are 1639 records in the table ...

"parent" table?  A DELETE doesn't check FKs in the table it's deleting.
What it checks are FKs in other tables that reference items in the
deletion table.  You sure you are worrying about the right set of FKs?

			regards, tom lane

Sun, 10 Oct 2004 18:45:02 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Speeding up DELETEs on table with FKs ... 

Not anymore I'm not :(  Have I been looking at this backwards?

I was looking at what the table being deleted from REFERENCES,but its what 
is REFERENCing that table I should be looking at, isn't? :(



On Sun, 10 Oct 2004, Tom Lane wrote:

> "Marc G. Fournier" <scrappy@postgresql.org> writes:
>> I posted to -sql the other day about an atrociously slow DELETE on a table
>> that has two FKs to a 'parent' table ... if the # of records in the table
>> that match the condition is 1, its fast ... in the sample I'm working
>> with, there are 1639 records in the table ...
>
> "parent" table?  A DELETE doesn't check FKs in the table it's deleting.
> What it checks are FKs in other tables that reference items in the
> deletion table.  You sure you are worrying about the right set of FKs?
>
> 			regards, tom lane
>

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Sun, 10 Oct 2004 20:16:50 -0300 (ADT)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: Speeding up DELETEs on table with FKs ... 
On Sun, 10 Oct 2004, Tom Lane wrote:

> "Marc G. Fournier" <scrappy@postgresql.org> writes:
>> I posted to -sql the other day about an atrociously slow DELETE on a table
>> that has two FKs to a 'parent' table ... if the # of records in the table
>> that match the condition is 1, its fast ... in the sample I'm working
>> with, there are 1639 records in the table ...
>
> "parent" table?  A DELETE doesn't check FKs in the table it's deleting.
> What it checks are FKs in other tables that reference items in the
> deletion table.  You sure you are worrying about the right set of FKs?

'k, now that I've seen the error of my ways *groan*  I've gone back 
through, and checked for what is referencing that table, and there is only 
one place that is, and it does have an INDEX:

> explain analyze select * from table where raw_id = 20722;
                                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
  Index Scan using ind_raw_id on table  (cost=0.00..3.09 rows=1 width=122) (actual time=0.33..0.33 rows=0 loops=1)
    Index Cond: (raw_id = 20722::numeric)
  Total runtime: 0.37 msec
(3 rows)

and raw_id is the primary key in the table that I'm trying to run the 
delete on, and an EXPLAIN ANALYZE for that one shows slightly slower, but 
similar results (it a much bigger table) ...

And, doing a join of the two tables based on raw_id shows that the indices 
are being used:

> explain select bdar.detail_id from detail bda,detail_raw bdar where bdar.raw_avl_id = bda.raw_avl_id;
                                                      QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
  Merge Join  (cost=0.00..29829.28 rows=250567 width=37)
    Merge Cond: ("outer".raw_avl_id = "inner".raw_avl_id)
    ->  Index Scan using ind_raw_avl_id on detail bda  (cost=0.00..8456.34 rows=250567 width=12)
    ->  Index Scan using pk_detail_raw on detail_raw bdar  (cost=0.00..16941.06 rows=269349 width=25)
(4 rows)

Now, the DELETE query that I'm trying to run is to delete 9997 rows from 
the table, so that means 9997 checks to detail as well, to make sure 
raw_id isn't being used, correct?

Am I in the right ballpark now with this?  Or am I still totally lost?


----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Sun, 10 Oct 2004 21:44:18 -0300 (ADT)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: Speeding up DELETEs on table with FKs ... 
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> 'k, now that I've seen the error of my ways *groan*  I've gone back 
> through, and checked for what is referencing that table, and there is only 
> one place that is, and it does have an INDEX:

>> explain analyze select * from table where raw_id = 20722;
>                                                              QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------
>   Index Scan using ind_raw_id on table  (cost=0.00..3.09 rows=1 width=122) (actual time=0.33..0.33 rows=0 loops=1)
>     Index Cond: (raw_id = 20722::numeric)
>   Total runtime: 0.37 msec

"numeric", hm?  Is the referenced column also of type numeric?

			regards, tom lane

Mon, 11 Oct 2004 01:26:57 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Speeding up DELETEs on table with FKs ... 
On Mon, 11 Oct 2004, Tom Lane wrote:

> "Marc G. Fournier" <scrappy@postgresql.org> writes:
>> 'k, now that I've seen the error of my ways *groan*  I've gone back
>> through, and checked for what is referencing that table, and there is only
>> one place that is, and it does have an INDEX:
>
>>> explain analyze select * from table where raw_id = 20722;
>>                                                              QUERY PLAN
>> -----------------------------------------------------------------------------------------------------------------------------------
>>   Index Scan using ind_raw_id on table  (cost=0.00..3.09 rows=1 width=122) (actual time=0.33..0.33 rows=0 loops=1)
>>     Index Cond: (raw_id = 20722::numeric)
>>   Total runtime: 0.37 msec
>
> "numeric", hm?  Is the referenced column also of type numeric?

Correct, and a join of the two tables appears to use both indices, altho 
one is of numeric(9,0) and the other numeric(12,0) ... could that 
difference post a problem from a FK perspective that a SELECT/JOIN 
wouldn't show?

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Mon, 11 Oct 2004 03:39:10 -0300 (ADT)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: Speeding up DELETEs on table with FKs ... 
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> On Mon, 11 Oct 2004, Tom Lane wrote:
>> "numeric", hm?  Is the referenced column also of type numeric?

> Correct, and a join of the two tables appears to use both indices, altho 
> one is of numeric(9,0) and the other numeric(12,0) ... could that 
> difference post a problem from a FK perspective that a SELECT/JOIN 
> wouldn't show?

No.  I wonder though how big a speed boost you'd get from converting
these columns (and all the ones linked to them) to BIGINT ...

			regards, tom lane

Mon, 11 Oct 2004 09:48:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Speeding up DELETEs on table with FKs ... 