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@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!