On 04/05/2017 12:04 PM, Bruno Wolff III wrote:
> 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.
>
>
That's an incredible difference. Is it believable? Same resource, etc?