Re: Speeding up DELETEs on table with FKs ...

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(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

"Marc G. Fournier" <scrappy(at)postgresql(dot)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

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(at)postgresql(dot)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(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

On Sun, 10 Oct 2004, Tom Lane wrote:

> "Marc G. Fournier" <scrappy(at)postgresql(dot)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(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

"Marc G. Fournier" <scrappy(at)postgresql(dot)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

On Mon, 11 Oct 2004, Tom Lane wrote:

> "Marc G. Fournier" <scrappy(at)postgresql(dot)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(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

"Marc G. Fournier" <scrappy(at)postgresql(dot)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