From: | Hannu Krosing <hannu(at)tm(dot)ee> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Ed Loehr <ELOEHR(at)austin(dot)rr(dot)com>, PostgreSQL HACKERS <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Re: [HACKERS] "ExecInitIndexScan: both left and right..." meaning? |
Date: | 1999-12-16 10:01:56 |
Message-ID: | 3858B894.C7CBA49C@tm.ee |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Tom Lane wrote:
>
> Ed Loehr <ELOEHR(at)austin(dot)rr(dot)com> writes:
> > create index mytable_dog_idx on mytable(dog_id);
> > create index mytable_cat_idx on mytable(cat_id);
> > create index mytable_dogcat_idx on mytable(dog_id,cat_id);
>
> > ...thinking these indices would allow the fastest lookups from 3 different
> > angles (at the cost of slower inserts, of course). Not sure my intuition
> > here corresponds directly with the technical reality...
>
> I doubt the 2-column index earns its keep given that you have another
> index on the front column. A multicolumn index is a pretty specialized
> beast, so I don't recommend creating one unless you have a very specific
> heavily-used query in mind. (Of course, if you're making a multicol
> UNIQUE index to enforce uniqueness of a multicol primary key, that's
> a different matter entirely. But if you're just fishing for performance
> improvements, you're probably fishing in the wrong place.)
Actually I think that the first (dog_id) is worthless in this situation as
(dog_id,cat_id) can be used instead of it.
I vaguely remember that Hiroshi posted a patch some time ago that fixed
the plan to use more then only the first column of multi-column index
if possible.
The first column of a multi-column index has always been used afaik.
------------------------
Hannu
From | Date | Subject | |
---|---|---|---|
Next Message | feng hao | 1999-12-16 12:05:13 | |
Previous Message | Karel Zak - Zakkr | 1999-12-16 09:54:25 | Re: [HACKERS] pg_dump --help |