Re: when to use index, and when not to us index - *the* answer :)

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.