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

[Tom Lane]

Bruno Wolff III  writes:
> 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.

Interesting.  That probably traces back to Emre Hasegeli's work from
last year (commit 77e290682).

regards, tom lane


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

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

[Bruno Wolff III]

On Wed, Apr 05, 2017 at 12:11:09 -0600,
 Rob Sargent  wrote:



On 04/05/2017 12:04 PM, Bruno Wolff III wrote:

On Wed, Apr 05, 2017 at 00:05:31 -0400,
Tom Lane  wrote:

Bruno Wolff III  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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Rob Sargent]

On 04/05/2017 12:04 PM, Bruno Wolff III wrote:

On Wed, Apr 05, 2017 at 00:05:31 -0400,
 Tom Lane  wrote:

Bruno Wolff III  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?



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

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

[Bruno Wolff III]

On Wed, Apr 05, 2017 at 00:05:31 -0400,
 Tom Lane  wrote:

Bruno Wolff III  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.



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

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

[Tom Lane]

Bruno Wolff III  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.

regards, tom lane


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

[GENERAL] Is there a point to having both a normal gist index and an exclude index?

[Bruno Wolff III]

I am trying to load a database with about 3.5 million records relating 
netblocks to locations. I currently don't know whether or not any of the 
netblocks overlap. If they don't, then I can simplify queries that 
find the locations of IP addresses.


I create the table as follows:
DROP TABLE IF EXISTS iplocation;
 CREATE TABLE iplocation (
 network INET NOT NULL,
 geoname_id INT,
 registered_country_geoname_id INT,
 represented_country_geoname_id INT,
 is_anonymous_proxy BOOLEAN NOT NULL,
 is_satellite_provider BOOLEAN NOT NULL,
 postal_code TEXT,
 latitude DOUBLE PRECISION,
 longitude DOUBLE PRECISION,
 accuracy_radius DOUBLE PRECISION
);

Then I load the table with /copy.

Then I create both a normal gist index and an exclude index using the 
following:

DROP INDEX IF EXISTS contains;
CREATE INDEX contains ON iplocation USING gist (network inet_ops);
ANALYZE VERBOSE iplocation;
ALTER TABLE iplocation 
 ADD CONSTRAINT overlap EXCLUDE USING gist (network inet_ops WITH &&)

;

So far the exclude index hasn't finished being created.

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.



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