Thread: How are foreign key constraints built?
How are foreign key constraints built? In loading my database into
PostgreSQL 8.0, on the command:
ALTER TABLE ONLY TABLEA
ADD CONSTRAINT "$1" FOREIGN KEY (mkey) REFERENCES tableb(mkey) ON DELETE
CASCADE;
I ended up with the following in pg_tmp as it is adding the constraint:
-rw------- 1 postgres admin 1073741824 23 Jan 06:09 pgsql_tmp2559.25
-rw------- 1 postgres admin 1073741824 23 Jan 06:24 pgsql_tmp2559.26
-rw------- 1 postgres admin 1073741824 23 Jan 06:39 pgsql_tmp2559.27
-rw------- 1 postgres admin 636526592 23 Jan 06:48 pgsql_tmp2559.28
-rw------- 1 postgres admin 1073741824 23 Jan 11:51 pgsql_tmp2559.29
-rw------- 1 postgres admin 1073741824 23 Jan 11:34 pgsql_tmp2559.30
-rw------- 1 postgres admin 1073741824 23 Jan 11:36 pgsql_tmp2559.31
-rw------- 1 postgres admin 1073741824 23 Jan 11:37 pgsql_tmp2559.32
-rw------- 1 postgres admin 1073741824 23 Jan 11:38 pgsql_tmp2559.33
-rw------- 1 postgres admin 1073741824 23 Jan 11:39 pgsql_tmp2559.34
-rw------- 1 postgres admin 1073741824 23 Jan 11:41 pgsql_tmp2559.35
-rw------- 1 postgres admin 1073741824 23 Jan 11:42 pgsql_tmp2559.36
-rw------- 1 postgres admin 1073741824 23 Jan 11:43 pgsql_tmp2559.37
-rw------- 1 postgres admin 1073741824 23 Jan 11:45 pgsql_tmp2559.38
-rw------- 1 postgres admin 1073741824 23 Jan 11:46 pgsql_tmp2559.39
-rw------- 1 postgres admin 1073741824 23 Jan 11:47 pgsql_tmp2559.40
-rw------- 1 postgres admin 1073741824 23 Jan 11:49 pgsql_tmp2559.41
-rw------- 1 postgres admin 1073741824 23 Jan 11:50 pgsql_tmp2559.42
-rw------- 1 postgres admin 603136000 23 Jan 11:51 pgsql_tmp2559.43
I believe files 25-28 are about the size of one of the indexes, but 29-43 is
about 35% larger than the tmp files when building the other index (the other
index is about 10GB).
There's no problem here, I'd just like to understand what it is doing. I
expected adding the foreign key constraint would just use the existing
indexes to verify the database is currently consistent. Is this just
working space to more efficiently build the initial constraint, or does it
actually write this to the database?
Wes
Wes <wespvp@syntegra.com> writes:
> There's no problem here, I'd just like to understand what it is doing.
Either a hash or merge join between the two tables, to verify that all
the keys in the referencing table exist in the referenced table. The
intermediate data is evidently spilling to disk.
> I expected adding the foreign key constraint would just use the existing
> indexes to verify the database is currently consistent.
People have this weird notion that an index-based plan is always faster
than anything else. If you like you can try the operation with "set
enable_seqscan = off", but I bet it will take longer.
regards, tom lane
On Sun, Jan 23, 2005 at 02:01:41PM -0500, Tom Lane wrote: > Wes <wespvp@syntegra.com> writes: > > There's no problem here, I'd just like to understand what it is doing. > > Either a hash or merge join between the two tables, to verify that all > the keys in the referencing table exist in the referenced table. The > intermediate data is evidently spilling to disk. If this were done with a nested loop, you wouldn't need any intermediate data, right? You can just throw the result-set away. ISTM that in the case of just verifying existance of data, it might be faster to do a nested loop that doesn't have to spill anywhere, instead of a hash or merge that needs to generate a bunch of intermediate data. Is the optimizer able to take this into account? > > I expected adding the foreign key constraint would just use the existing > > indexes to verify the database is currently consistent. > > People have this weird notion that an index-based plan is always faster > than anything else. If you like you can try the operation with "set > enable_seqscan = off", but I bet it will take longer. Well, every other database I've used can do index covering, which means index scans *are* faster. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Sun, Jan 23, 2005 at 03:19:10PM -0600, Jim C. Nasby wrote: > > People have this weird notion that an index-based plan is always faster > > than anything else. If you like you can try the operation with "set > > enable_seqscan = off", but I bet it will take longer. > > Well, every other database I've used can do index covering, which means > index scans *are* faster. ... on those database systems. Indexes are different in Postgres in general: they don't have visibility info (other systems don't need it, tuples are always visible), and in some databases you have clustered indexes, where the index is also the heap. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "We are who we choose to be", sang the goldfinch when the sun is high (Sandman)
On Sun, Jan 23, 2005 at 06:45:36PM -0300, Alvaro Herrera wrote: > On Sun, Jan 23, 2005 at 03:19:10PM -0600, Jim C. Nasby wrote: > > > > People have this weird notion that an index-based plan is always faster > > > than anything else. If you like you can try the operation with "set > > > enable_seqscan = off", but I bet it will take longer. > > > > Well, every other database I've used can do index covering, which means > > index scans *are* faster. > > ... on those database systems. Indexes are different in Postgres in > general: they don't have visibility info (other systems don't need it, > tuples are always visible), and in some databases you have clustered > indexes, where the index is also the heap. Yes, I understand. I was just pointing out that in other databases, an index scan of even the entire table can be faster, hence the mentality that index scans are always better. I really hope that the current discussion on hackers about tuple visibility in indexes leads somewhere; I think that would be a huge gain for PostgreSQL. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
"Jim C. Nasby" <decibel@decibel.org> writes: > Well, every other database I've used can do index covering, which means > index scans *are* faster. Still not necessarily true. In a case like this it would still be random access which would be slower than sequential access. Though Oracle is capable of taking the best of both worlds and doing a hash join but taking the data from sequentially reading the index instead of the table. -- greg
On Mon, Jan 24, 2005 at 09:24:49AM -0500, Greg Stark wrote: > "Jim C. Nasby" <decibel@decibel.org> writes: > > > Well, every other database I've used can do index covering, which means > > index scans *are* faster. > > Still not necessarily true. In a case like this it would still be random > access which would be slower than sequential access. Actually, even with random access, a covering index can still be faster. Imagine a single-field index on a table with 40 fields. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On 1/23/05 1:01 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > If you like you can try the operation with "set > enable_seqscan = off", but I bet it will take longer. Ouch! That was a big mistake. Since inquiring minds want to know, I decided to give that a try. The expected outcome is to beat the heck out of the index disks as it read one index and referenced the other to see if the value existed. What appears to have happened is that it went through the same process as before, but read each data record via the index. It still created all the pgsql_tmp files, the data disk was still the heaviest hit (expected no or little access there), and it beat the living daylights out of my swap - pageins/outs like crazy. The I/O on the index disks was negligible compared to the data and swap disks. I won't try that again... Wes
On Tue, Jan 25, 2005 at 09:38:20AM -0600, Wes wrote: > On 1/23/05 1:01 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > > > If you like you can try the operation with "set > > enable_seqscan = off", but I bet it will take longer. > > Ouch! That was a big mistake. Since inquiring minds want to know, I > decided to give that a try. The expected outcome is to beat the heck out of > the index disks as it read one index and referenced the other to see if the > value existed. What appears to have happened is that it went through the > same process as before, but read each data record via the index. It still > created all the pgsql_tmp files, the data disk was still the heaviest hit > (expected no or little access there), and it beat the living daylights out > of my swap - pageins/outs like crazy. The I/O on the index disks was > negligible compared to the data and swap disks. I won't try that again... If the OS is swapping you've got serious issues; you need to look at your configurating settings that deal with memory and figure out why you're running out. And yes, PostgreSQL can't do 'index covering', so even when it accesses a table via an index it still has to read the base table. This is why if you need to read the entire table it's faster to seqscan than index scan. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"