-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 12/12/06 11:30, Tom Lane wrote: > "Brandon Aiken" <BAiken@xxxxxxxxxxxxxxx> writes: >> If you have, say, an index(x, y) then that index will often double as an >> index(x). It will generally not double as an index(y). > > It's not hard to understand why, if you think about the sort ordering of > a double-column index: > > x y > > 1 1 > 1 2 > 1 3 > 2 1 > 2 2 > 2 3 > 3 1 > ... > > All similar values of x are brought together, so scanning the index for > x alone works just the same as it would in a one-column index ... the > index entries are bigger so it's marginally less efficient, but only > marginally. On the other hand, the entries for a specific value or > range of y will be scattered all over the index, so it's almost useless > to use the index for a search on y alone. Some DBMSs call this an "index scan". > As of PG 8.1 or 8.2 (I forget) the optimizer will *consider* using such > an index for a y-only query, but it'll nearly always decide it's a bad > idea. Scanning segment-2 of a 2-segment index seems like it would be faster than scanning the table, if for no other reason than "locality of data": the index will be smaller than the table, so scanning it looking for record pointers should be faster than scanning the table. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFfun7S9HxQb37XmcRAvaqAJ0X4m933xqHaKBfdYEM0KHaMST/TgCfQsEA 4dBgCERRzIlBrkUK18gfZ08= =PGjb -----END PGP SIGNATURE-----
