Which index methodology is better?-

From: "Chris Hoover" <revoohc(at)gmail(dot)com>
To: "PGSQL Performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Which index methodology is better?-
Date: 2007-11-05 18:43:07
Message-ID: 1d219a6f0711051043x569b7f51r4cee3353e6ea9f10@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I have a question.

Consider this scenario.

Table customer (
customer_id int8,
customer_l_name varchar(35),
customer_f_name varchar(30),
customer_addr_1 varchar(100),\
customer_addr_2 varchar(100),
customer_city varchar(50),
customer_state char(2),
customer_zip varchar(9)
);

On this table, a customer can search by customer_id, customer_l_name,
and customer_f_name.

Is it better to create 3 indexes, or one index on the three columns?

I did some initial testing with index customer_test_idx(customer_id,
customer_l_name, customer_f_name) and postgres would use the index for
select * from customer where customer_f_name = 'zxy' - so the single
index will cover the three.

My question is, is this better? Does it end up using less memory
and/or disk or more? I am trying to find ways to keep more of my
customers databases in memory, and I am thinking that loading one
index is probably a little better than loading three.

Thanks for any advice,

Chris

PG 8.1
RH 4.0

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2007-11-05 19:56:29 Re: Which index methodology is better?-
Previous Message Erik Jones 2007-11-05 16:54:05 Re: Migrating to 8.3 - checkpoints and background writer