From: | Bruno Wolff III <bruno(at)wolff(dot)to> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Is there a point to having both a normal gist index and an exclude index? |
Date: | 2017-04-05 02:58:09 |
Message-ID: | 20170405025809.GA8335@wolff.to |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | lin | 2017-04-05 03:28:20 | AEXPR_OR, AEXPR_AND is not in postgres 9.6, how can I rewrite where it used in 9.3 ? |
Previous Message | Adrian Klaver | 2017-04-04 23:55:02 | Re: keeping WAL after dropping replication slots |