Search Postgresql Archives

Re: Multiple column index usage question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



-----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@xxxxxxx> 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-----


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux