Thread: indexes multicolumn

Hi list,

as the doc says:

SELECT name FROM test2 WHERE major = constant AND minor = constant;

then it might be appropriate to define an index on the columns major and minor
together, e.g.:

CREATE INDEX test2_mm_idx ON test2 (major, minor);

I may ask a dumb question, but does the query planner also use such an
index with the inversed query, e.g.:

SELECT name FROM test2 WHERE minor = constant AND major = constant;

or am I obliged to define an inversed index?

JY
--
Everybody is going somewhere!!  It's probably a garage sale or a
disaster Movie!!

Hi

On 31 May 2011 15:18, Jean-Yves F. Barbier <12ukwn@gmail.com> wrote:
> Hi list,
>
> as the doc says:
>
> SELECT name FROM test2 WHERE major = constant AND minor = constant;
>
> then it might be appropriate to define an index on the columns major and minor
> together, e.g.:
>
> CREATE INDEX test2_mm_idx ON test2 (major, minor);
>
> I may ask a dumb question, but does the query planner also use such an
> index with the inversed query, e.g.:
>
> SELECT name FROM test2 WHERE minor = constant AND major = constant;
>
> or am I obliged to define an inversed index?

If you run:

explain analyze select name from test2 where major = const and minor = const;

and

explain analyze select name from test2 where minor = const and major = const;

you will see that the query planner is clever enough to see that they
are the same.

--
Michael Wood <esiotrot@gmail.com>

On Tue, 31 May 2011 15:47:45 +0200, Michael Wood <esiotrot@gmail.com> wrote:


...
> If you run:
>
> explain analyze select name from test2 where major = const and minor = const;
>
> and
>
> explain analyze select name from test2 where minor = const and major = const;
>
> you will see that the query planner is clever enough to see that they
> are the same.

I was almost sure about that:)
Thank Michael
JY
--
And miles to go before I sleep.
        -- Robert Frost