Обсуждение: when to use index, and when not to us index - *the* answer :)
hi i just made tests which show following results: data type: | rec# | idx-time | seq-time | diff. --------------+------+----------+----------+---------- int4.rnd | 91 | 0.941282 | 0.984096 | 0.042814 int4.seq | 97 | 0.937176 | 0.988626 | 0.051450 int8.rnd | 135 | 0.946168 | 1.036118 | 0.089950 int8.seq | 65 | 0.941527 | 0.963275 | 0.021748 char4.rnd | 32 | 0.945356 | 0.965213 | 0.019857 char4.seq | 39 | 0.949922 | 0.997675 | 0.047753 char8.rnd | 61 | 0.963562 | 1.030349 | 0.066787 char8.seq | 35 | 0.991704 | 1.002345 | 0.010641 char16.rnd | 37 | 0.978170 | 0.990919 | 0.012749 char16.seq | 17 | 0.975246 | 0.978144 | 0.002898 char32.rnd | 34 | 0.992438 | 0.998808 | 0.006370 char32.seq | 18 | 1.005228 | 1.020824 | 0.015596 char64.rnd | 66 | 1.042605 | 1.139358 | 0.096753 char64.seq | 20 | 1.066124 | 1.128776 | 0.062652 char128.rnd | 55 | 1.146153 | 1.187139 | 0.040986 char128.seq | 12 | 1.175579 | 1.215656 | 0.040077 what exactly it is? is shows that for a single-field table (field of type "data type"), you get index-scan faster then seq-scan, only when you reach "rec#" records in table. for example: for table: create table test (field int4); and values inserted sequentially, you will benefit from creating index only after you'll reach 97 records. before this, index scan will be slower then seq-scan. hope this helps some people. feel free to ask questions. depesz -- hubert depesz lubaczewski http://www.depesz.pl/ ------------------------------------------------------------------------ Mój Boże, spraw abym milczał, dopóki się nie upewnię, że naprawdę mam coś do powiedzenia. (c) 1998 depesz
Вложения
On Mon, Aug 12, 2002 at 08:24:50PM +0200, Hubert depesz Lubaczewski wrote: > hi > i just made tests which show following results: Very interesting, but how large was the table? And is that time in seconds? Also, the results would be much more interesting on a table whose size was larger than the available memory (thus would not be completely stored within the cache). > data type: | rec# | idx-time | seq-time | diff. > --------------+------+----------+----------+---------- > int4.rnd | 91 | 0.941282 | 0.984096 | 0.042814 > int4.seq | 97 | 0.937176 | 0.988626 | 0.051450 > int8.rnd | 135 | 0.946168 | 1.036118 | 0.089950 > int8.seq | 65 | 0.941527 | 0.963275 | 0.021748 > char4.rnd | 32 | 0.945356 | 0.965213 | 0.019857 > char4.seq | 39 | 0.949922 | 0.997675 | 0.047753 > char8.rnd | 61 | 0.963562 | 1.030349 | 0.066787 > char8.seq | 35 | 0.991704 | 1.002345 | 0.010641 > char16.rnd | 37 | 0.978170 | 0.990919 | 0.012749 > char16.seq | 17 | 0.975246 | 0.978144 | 0.002898 > char32.rnd | 34 | 0.992438 | 0.998808 | 0.006370 > char32.seq | 18 | 1.005228 | 1.020824 | 0.015596 > char64.rnd | 66 | 1.042605 | 1.139358 | 0.096753 > char64.seq | 20 | 1.066124 | 1.128776 | 0.062652 > char128.rnd | 55 | 1.146153 | 1.187139 | 0.040986 > char128.seq | 12 | 1.175579 | 1.215656 | 0.040077 > > what exactly it is? > is shows that for a single-field table (field of type "data type"), you > get index-scan faster then seq-scan, only when you reach "rec#" records > in table. > for example: > for table: > create table test (field int4); and values inserted sequentially, you > will benefit from creating index only after you'll reach 97 records. > before this, index scan will be slower then seq-scan. > > hope this helps some people. > feel free to ask questions. > > depesz > > -- > hubert depesz lubaczewski http://www.depesz.pl/ > ------------------------------------------------------------------------ > Mój Bo¿e, spraw abym milcza³, dopóki siê nie upewniê, ¿e naprawdê mam > co¶ do powiedzenia. (c) 1998 depesz > -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
Re: when to use index, and when not to us index - *the* answer :)
От
Hubert depesz Lubaczewski
Дата:
On Tue, Aug 13, 2002 at 10:31:03AM +1000, Martijn van Oosterhout wrote: > Very interesting, but how large was the table? And is that time in seconds? > Also, the results would be much more interesting on a table whose size was > larger than the available memory (thus would not be completely stored within > the cache). number of records is shown in "rec#" column. showing results for *big* tables doesn't make any sense, because there index-scan will always be faster than seq-scan. my point was just to show *when* index scan becomes faster than seq-scan. And yes, i forgot to say. times are in seconds for 100 requests, ie. "0.984096" means that 100 select took time (together) 0.984096 seconds. > > data type: | rec# | idx-time | seq-time | diff. > > --------------+------+----------+----------+---------- > > int4.rnd | 91 | 0.941282 | 0.984096 | 0.042814 > > int4.seq | 97 | 0.937176 | 0.988626 | 0.051450 > > int8.rnd | 135 | 0.946168 | 1.036118 | 0.089950 > > int8.seq | 65 | 0.941527 | 0.963275 | 0.021748 > > char4.rnd | 32 | 0.945356 | 0.965213 | 0.019857 > > char4.seq | 39 | 0.949922 | 0.997675 | 0.047753 > > char8.rnd | 61 | 0.963562 | 1.030349 | 0.066787 > > char8.seq | 35 | 0.991704 | 1.002345 | 0.010641 > > char16.rnd | 37 | 0.978170 | 0.990919 | 0.012749 > > char16.seq | 17 | 0.975246 | 0.978144 | 0.002898 > > char32.rnd | 34 | 0.992438 | 0.998808 | 0.006370 > > char32.seq | 18 | 1.005228 | 1.020824 | 0.015596 > > char64.rnd | 66 | 1.042605 | 1.139358 | 0.096753 > > char64.seq | 20 | 1.066124 | 1.128776 | 0.062652 > > char128.rnd | 55 | 1.146153 | 1.187139 | 0.040986 > > char128.seq | 12 | 1.175579 | 1.215656 | 0.040077 depesz -- hubert depesz lubaczewski http://www.depesz.pl/ ------------------------------------------------------------------------ Mój Boże, spraw abym milczał, dopóki się nie upewnię, że naprawdę mam coś do powiedzenia. (c) 1998 depesz