Thread: Indexing question

In SQL, given a table T, with two fields f1, f2,

is it possible to create an index such that the same record is indexed in the index, once with field f1 and once with
fieldf2.  (I am not looking for a compound index in which the key would look like <f1, f2>, instead there should be two
entriesin the index <f1> and <f2>).   

we have a few use cases for the above, perhaps we need to alter the schema somehow to accommodate the above,

any advice is greatly appreciated ..

thanks

amit sehas <cun23@yahoo.com> wrote:

> In SQL, given a table T, with two fields f1, f2,
>
> is it possible to create an index such that the same record is indexed
> in the index, once with field f1 and once with field f2.  (I am not
> looking for a compound index in which the key would look like <f1,
> f2>, instead there should be two entries in the index <f1> and <f2>).
>
> we have a few use cases for the above, perhaps we need to alter the
> schema somehow to accommodate the above,

2 separate indexes? One on f1 and one on f2?


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of amit sehas
> Sent: Tuesday, August 14, 2012 12:55 PM
> To: pgsql-sql@postgresql.org; pgsql-general@postgresql.org
> Subject: [GENERAL] Indexing question
>
> In SQL, given a table T, with two fields f1, f2,
>
> is it possible to create an index such that the same record is indexed in
the
> index, once with field f1 and once with field f2.  (I am not looking for a
> compound index in which the key would look like <f1, f2>, instead there
> should be two entries in the index <f1> and <f2>).
>
> we have a few use cases for the above, perhaps we need to alter the
> schema somehow to accommodate the above,
>
> any advice is greatly appreciated ..
>
> thanks
>


In short: No, you cannot create an index on T in the way you describe.  You
need to create a new table: TF, with columns {T(id), f}, and having rows 1
and 2 with the same T(id) value; An index over "f" on table TF will then
contain both values.

Slightly longer:

This seems like a classic case of column duplication.  I am assuming that
the columns in question are, say, phone1 and phone2 an you want to be able
to search by phone number without having to specify the two fields
separately.  The correct way to do this is to create a "phone" table and add
a single line for each phone number you want to store (along with the
corresponding FK value of the original table) - with possibly a "phone_type"
column.

If this is not what you are after then you should be more explicit in your
requirements.  Why is creating two separate indexes (on f1 and f2) not
acceptable?

If indeed you are dealing with variations of the above example you really
want to consider modifying your schema to use two tables with a one-to-many
relationship because the current scenario begs the question(s): "why only f1
and f2?  Why isn't there an f3?".  The idea is that there are generally 3
separate cardinalities {0, 1, >1}.  Zero you ignore, 1 you generally put on
the same table - though not always, and more-than-one you create a separate
table and store multiple values as separate rows instead of as columns.

David J.