Обсуждение: index
We are currently storing a large amount of networking data. The database size is over 50 Gigabytes. It also grows by 10 Gigabytes every month. We are looking if there is a way to speedup lookups by IP Address. The primary key is a set of values. And the key does include IP Address as well. Will it be more efficient to also add index on IP Address to speedup lookups by IP? -- Regards, Maksim
On Wed, Jan 14, 2009 at 12:53 AM, Maksim Sosnovskiy <msosno01@gmail.com> wrote: Will it be more efficient to also add index on IP > Address to speedup lookups by IP? Most likely, especially if the IP address is not the first column in your primary key index. Have you done an explain analyze of your ip lookup query? If not, do so; that can help. Then try creating the index and explain analyze the query again to see what happens. Knowing your existing schema/indices and such would let us do more than guess- and not knowing the plan your current query is using makes it difficult to know if there's a better one using a to-be-created index. -- - David T. Wilson david.t.wilson@gmail.com
Maksim Sosnovskiy escreveu: > The primary key is a set of values. And the key does include IP > Address as well. Will it be more efficient to also add index on IP > Address to speedup lookups by IP? > It depends on what set of queries that you run. If the most frequent queries use ip_field, then you need an index on it. Maybe you could try ip4r [1]. [1] http://pgfoundry.org/projects/ip4r/ -- Euler Taveira de Oliveira http://www.timbira.com/