Re: multiple table indexes

On Fri, Aug 17, 2001 at 02:15:08PM -0400, Evan Zane Macosko wrote:
> Does Postgres support multiple table indexes? I want to update one table
> with data from another, but it's very slow because I have a rather bulky
> WHERE clause. I was wondering if I could index the two tables together to
> make execution faster.

indexes speed up a select, but they slow down an update or an
insert (or a delete) as each index needs to be updated to match
the new state of the database.

yes, you can have several indexes on a table:

create index name_ix on my_table(lname,fname,mname);
create index name_case_insensitive_ix on my_table(upper(lname),lower(fname));
create index zip_ix on my_table(zip,zip_plus_4);

if you're plowing from tableA into tableB, indexes on fields in
tableA that you're "where"ing on, will help; indexes on anything
in tableB will hinder.

often i see folks doing

drop index tableA_something_ix;
drop index tableA_something_else_ix;
--now do some gargantuan table munge on tableA--
create index tableA_something_else_ix on tableA(...);
create index tableA_something_ix on tableA(...);

--
Khan said that revenge is a dish best served cold. I think
sometimes it's best served hot, chunky, and foaming.
- P.J.Lee ('79-'80)

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!