Is there a point to having both a normal gist index and an excludeindex?
От | Bruno Wolff III |
---|---|
Тема | Is there a point to having both a normal gist index and an excludeindex? |
Дата | |
Msg-id | 20170405025809.GA8335@wolff.to обсуждение исходный текст |
Ответы |
Re: Is there a point to having both a normal gist index and an exclude index?
|
Список | 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.
В списке pgsql-general по дате отправления: