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

[Bruno Wolff III <bruno@xxxxxxxx>]

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