Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, Apr 05, 2017 at 12:11:09 -0600,
 Rob Sargent <robjsargent@xxxxxxxxx> wrote:


On 04/05/2017 12:04 PM, Bruno Wolff III wrote:
On Wed, Apr 05, 2017 at 00:05:31 -0400,
Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Bruno Wolff III <bruno@xxxxxxxx> 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?

Same data, same load scripts other than spgist replacing gist and pointing to the 10 server instead of the 9.6 server.

If gist is scaling at n^2 because of bad splits, then with 3.5M records I could see that big of a difference if spgist is n log n. I don't know for sure if that was what is really going on. The index creation seems to be CPU bound rather than I/O bound as it is pegging a CPU.


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux