CODEWITHSUNDEEP
databasepostgresqlip
Irshad Khan
Irshad Khan

Reputation: 6026

Which Index is best for PostgreSQL inet datatype?

I am using PostgreSQL to get "IP to City" data from the table, but it is taking too much time to respond. I am also using btree indexing on network column.

My Query is :

SELECT city_name, subdivision, country_name, continent_name FROM ip4_to_city_tbl WHERE '104.225.250.92' <<= network LIMIT 1;

Table 'ip4_to_city_tbl' is have 9685491 records.

If we have more than 400 requests in 1 second, then it takes 200 to 900 milliseconds to respond.

I want the response of query within 10 milliseconds.

EXPLAIN command shows Seq Scan on ip4_to_city_tbl query instead of Index Scan

So please tell me, what should do to improve response time and Which Index is best for PostgreSQL inet datatype?

Upvotes: 3

Views: 3218

Answers (1)

Joe Love
Joe Love

Reputation: 5932

In general, inet_ops index types are preferred for inet and cidr data types.

 create index on ip4_to_city_tbl using gist (network inet_ops);

Upvotes: 10

Related Questions