Re: Multiple indexes, huge table - Mailing list pgsql-general

From Tom Lane
Subject Re: Multiple indexes, huge table
Date
Msg-id 2376.1346977379@sss.pgh.pa.us
Whole thread Raw
In response to Re: Multiple indexes, huge table  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-general
Jeff Janes <jeff.janes@gmail.com> writes:
>> That sounds like you lack an index on the referencing column of the
>> foreign key constraint.  Postgres doesn't require you to keep such
>> an index, but it's a really good idea if you ever update the referenced
>> column.

> For updating 20 million out of 500 million rows, wouldn't a full table
> scan generally be preferable to an index scan anyway?

Foreign key triggers do their checks retail, though, so you really want
the probe for any referencing rows for a particular row-being-updated
to be able to use an index.

(It would be nice if we could replace that with a mass revalidation
once it got to be a big fraction of the table, but we don't have a
mechanism for that.  Yet.)

            regards, tom lane


pgsql-general by date:

Previous
From: Alan Hodgson
Date:
Subject: Re: Multiple indexes, huge table
Next
From: Sergio Basurto
Date:
Subject: Re: regexp_matches question SOLVED