Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id D4481650E93
	for <pgsql-admin-postgresql.org@postgresql.org>;
	Fri,  4 Jul 2008 13:00:13 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 87306-06 for <pgsql-admin-postgresql.org@postgresql.org>;
	Fri,  4 Jul 2008 13:00:09 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from web56406.mail.re3.yahoo.com (web56406.mail.re3.yahoo.com
	[216.252.111.85])
	by postgresql.org (Postfix) with SMTP id B4518650E7B
	for <pgsql-admin@postgresql.org>; Fri,  4 Jul 2008 13:00:09 -0300 (ADT)
Received: (qmail 14879 invoked by uid 60001); 4 Jul 2008 16:00:08 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID;
	b=QdsdPt1e+OJv3AQtmaXsa93oL8f2v31JlKGxwtMYMkq+66wjdcaubAayVW+hIr+SyOD/2qWe5iOVycaZPQLpI5F7MGo5UzCVaBy4+GatWIsHjRcggGrWXdtdb0CwZlLUf5dhaFGNftWoaBBamO/bbN1UrnniceUhviK6qDWJo4Y=;
Received: from [198.119.134.32] by web56406.mail.re3.yahoo.com via HTTP;
	Fri, 04 Jul 2008 09:00:06 PDT
X-Mailer: YahooMailRC/1042.33 YahooMailWebService/0.7.199
Date: Fri, 4 Jul 2008 09:00:06 -0700 (PDT)
From: Jessica Richard <rjessil@yahoo.com>
Subject: How many table scans in a delete...
To: pgsql-admin@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1445676858-1215187206=:14193"
Message-ID: <522587.14193.qm@web56406.mail.re3.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200807/70
X-Sequence-Number: 29680

--0-1445676858-1215187206=:14193
Content-Type: text/plain; charset=us-ascii

I am just trying to understand how Postgres does its delete work.

If I have a table testDad with a primary key on cola and no index on colb, and I have a kid table testKid with a foreign key to reference testDad but no index created on the foreign key column on table testKid.

I have 10,000 rows in each table and I want to delete 5000 rows from the Dad table, of course , I have to kill all the kids in the Kid table first so that I won't get the constraint error. 

Now I am ready to run my delete command on the Dad table with the following command:

delete from testDad where colb = 'abc';

(supposed select count(*) from testDad where colb = 'abc' will give me 5000 rows)

Since I don't have any index on testDad.colb, I know it is going to do a table scan on the table testDad to find all the qualified rows for the delete. 

My question1: how many table scans will this single transaction do  to find all  5000 qualified rows on the Dad table testDad? Does it scan the entire table once to get all qualified deletes? or it has to do the table scan 5000 times on testDad?

then, after all the 5000 qualified rows have been found on table testDad, the constraints between the Dad and Kid table will be checked against those 5000 qualified rows on table testDad.

My question 2: does it take one qualified row at a time from the Dad table then  do a table scan on the kid table for constraint check? In this case, it will have to do 5000 times of table scan on the kid table. very unlikely it will scan the kid table only once to do all constraint checking for 5000 different primary values...


Thanks,
Jessica


      
--0-1445676858-1215187206=:14193
Content-Type: text/html; charset=us-ascii

<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:times new roman, new york, times, serif;font-size:12pt"><div>I am just trying to understand how Postgres does its delete work.<br><br>If I have a table testDad with a primary key on cola and no index on colb, and I have a kid table testKid with a foreign key to reference testDad but no index created on the foreign key column on table testKid.<br><br>I have 10,000 rows in each table and I want to delete 5000 rows from the Dad table, of course , I have to kill all the kids in the Kid table first so that I won't get the constraint error. <br><br>Now I am ready to run my delete command on the Dad table with the following command:<br><br>delete from testDad where colb = 'abc';<br><br>(supposed select count(*) from testDad where colb = 'abc' will give me 5000 rows)<br><br>Since I don't have any index on testDad.colb, I know it is going to do a table scan
 on the table testDad to find all the qualified rows for the delete. <br><br>My question1: how many table scans will this single transaction do&nbsp; to find all&nbsp; 5000 qualified rows on the Dad table testDad? Does it scan the entire table once to get all qualified deletes? or it has to do the table scan 5000 times on testDad?<br><br>then, after all the 5000 qualified rows have been found on table testDad, the constraints between the Dad and Kid table will be checked against those 5000 qualified rows on table testDad.<br><br>My question 2: does it take one qualified row at a time from the Dad table then&nbsp; do a table scan on the kid table for constraint check? In this case, it will have to do 5000 times of table scan on the kid table. very unlikely it will scan the kid table only once to do all constraint checking for 5000 different primary values...<br><br><br>Thanks,<br>Jessica<br><br><br></div></div><br>



      </body></html>
--0-1445676858-1215187206=:14193--