Re: How are foreign key constraints built?

On Tue, Jan 25, 2005 at 09:38:20AM -0600, Wes wrote:
> On 1/23/05 1:01 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)decibel(dot)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?"