Re: Is there a point to having both a normal gist index and an exclude index?

On Wed, Apr 05, 2017 at 00:05:31 -0400,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>Bruno Wolff III <bruno(at)wolff(dot)to> writes:
>> ... I create both a normal gist index and an exclude index using the
>> following:
>> CREATE INDEX contains ON iplocation USING gist (network inet_ops);
>> ALTER TABLE iplocation
>> ADD CONSTRAINT overlap EXCLUDE USING gist (network inet_ops WITH &&);
>
>> But I am wondering if it is useful to have the normal gist index for
>> finding netblocks containing a specific IP address, as it seems like the
>> exclude index should be usable for that as well.
>
>No, that manually-created index is completely redundant with the
>constraint index.

Thanks.

P.S. Using spgist with version 10 for the exclude index is much faster
than using gist in 9.6. I have run the index creation for as long as
6 hours and it hasn't completed with 9.6. It took less than 10 minutes
to create it in 10. For this project using 10 isn't a problem and I'll
be doing that.