Thread: b-tree index performance

On Fri, Dec 15, 2006 at 01:13:00PM +0200, Yonatan Ben-Nes wrote:
> Hi all,
>
> I was wondering does the b-tree index performance change when it's
> implemented on different data types fields? is it better to use one of them
> instead of the other for (=) comparisons?
> I'm especially interested between INT8 and TEXT data types.

The difference in performence will be determined by the cost of
comparison. The cost of comparing strings is much higher than for
integers, so it will be slower.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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

On 12/15/06 05:41, Martijn van Oosterhout wrote:
> On Fri, Dec 15, 2006 at 01:13:00PM +0200, Yonatan Ben-Nes wrote:
>> Hi all,
>>
>> I was wondering does the b-tree index performance change when it's
>> implemented on different data types fields? is it better to use one of them
>> instead of the other for (=) comparisons?
>> I'm especially interested between INT8 and TEXT data types.
>
> The difference in performence will be determined by the cost of
> comparison. The cost of comparing strings is much higher than for
> integers, so it will be slower.

And comparing INT8 is more expensive on a 32-bit system.

Since TEXT is totally variable, is there a big difference in TEXT vs
CHAR(8)?

- --
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)

iD8DBQFFgqawS9HxQb37XmcRAte5AKC984ov7nwW9XfDHGU/75tfmNkeFQCfdrD8
2O8Ia4/Luo3RbVsIW1ImBx4=
=E2uZ
-----END PGP SIGNATURE-----

On Fri, Dec 15, 2006 at 07:44:16AM -0600, Ron Johnson wrote:
> > The difference in performence will be determined by the cost of
> > comparison. The cost of comparing strings is much higher than for
> > integers, so it will be slower.
>
> And comparing INT8 is more expensive on a 32-bit system.

The difference between int4 and int8 is probably negligable.

> Since TEXT is totally variable, is there a big difference in TEXT vs
> CHAR(8)?

Nothing measurable I'd think. It's probably the same code.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

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

On 12/15/06 07:50, Martijn van Oosterhout wrote:
> On Fri, Dec 15, 2006 at 07:44:16AM -0600, Ron Johnson wrote:
>>> The difference in performence will be determined by the cost of
>>> comparison. The cost of comparing strings is much higher than for
>>> integers, so it will be slower.
>> And comparing INT8 is more expensive on a 32-bit system.
>
> The difference between int4 and int8 is probably negligable.

Probably compiler-dependent...

>> Since TEXT is totally variable, is there a big difference in TEXT vs
>> CHAR(8)?
>
> Nothing measurable I'd think. It's probably the same code.
>
> Have a nice day,


- --
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)

iD8DBQFFgq6KS9HxQb37XmcRAlE6AJ9LxzCHq95wggefa0Q4a6/sBAw7aACgyasp
t8qCwkdarDlhc2N8PKkprrY=
=BBBp
-----END PGP SIGNATURE-----