Re: Multiple column index usage question

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes, it depends.

Given the example from OP, if you have queries that only reference
field bar, then the query optimizer will do a seqscan on the table.
 You would need a separate index on "bar"

And, given index1, you do not need another index on "foo" alone.


On 01/19/07 17:20, Jeremy Haile wrote:
> That's interesting.  So if you have a composite index on two columns, is
> there much of a reason (usually) to create single indexes on each of the
> two columns?  I guess the single indexes might be slightly faster
> depending on the number of different values/combinations, so probably
> "it depends" eh?
>
>
> On Fri, 19 Jan 2007 16:57:42 -0600, "Ron Johnson"
> <ron.l.johnson@cox.net> said:
> On 01/19/07 15:53, Jan Muszynski wrote:
>>>> Rather simple question, of which I'm not sure of the answer.
>>>>
>>>> If I have a multiple column index, say:
>>>>     Index index1 on tableA (foo,bar)
>>>>
>>>> and I then:
>>>>     Select * from "tableA" where foo = <some value>
>>>>
>>>> Will index1 be used, or am I looking at a seqscan in all circumstances?
> Yes, it will use the index.
>
> However, in earlier versions, the lvalue & rvalue needed to match in
> type to use the index.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFsVVbS9HxQb37XmcRAuB1AKDvMEzNgWVzYvwd6Z1OqAvZCOiD3gCg12Mo
vhk/F0f45VNzAn3sA2btrcQ=
=tZ8Z
-----END PGP SIGNATURE-----