Postgres Pro
Downloads
Home   >   mailing lists

Thread: Primary Key and Indices

Primary Key and Indices

From
Rich Shepard
Date:
   I'm converting mysql tables to postgres. One of the tables has this:

   PRIMARY KEY (org_id, contact_id),
   KEY contact (contact_id, org_id)

   Is there really a difference in the two indices if the sequence of fields
is reversed?

Thanks,

Rich

--
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM)
<http://www.appl-ecosys.com>   Voice: 503-667-4517   Fax: 503-667-8863

Re: Primary Key and Indices

From
Ragnar Hafstað
Date:
On Sun, 2005-04-24 at 09:49 -0700, Rich Shepard wrote:
>    I'm converting mysql tables to postgres. One of the tables has this:
>
>    PRIMARY KEY (org_id, contact_id),
>    KEY contact (contact_id, org_id)
>
>    Is there really a difference in the two indices if the sequence of fields
> is reversed?

yes.
for example, only the first one can be used for ORDER BY org_id

gnari

Re: Primary Key and Indices

From
Rich Shepard
Date:
  This message is in MIME format.  The first part should be readable text,
  while the remaining parts are likely unreadable without MIME-aware tools.

---1463811785-1899490222-1114369822=:4797
Content-Type: TEXT/PLAIN; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: QUOTED-PRINTABLE

On Sun, 24 Apr 2005, Ragnar Hafstað wrote:

> yes. for example, only the first one can be used for ORDER BY org_id

gnari,

   Thank you. I thought it might be the case but I wasn't sure.

Rich

--
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM)
<http://www.appl-ecosys.com>   Voice: 503-667-4517   Fax: 503-667-8863
---1463811785-1899490222-1114369822=:4797--