Re: optimizing Postgres queries

On Monday 05 January 2004 17:35, David Teran wrote:
> explain analyze SELECT --columns-- FROM KEY_VALUE_META_DATA t0 WHERE
> t0.ID_FOREIGN_TABLE = 21110;
>
> i see that no index is being used whereas when i use
>
> explain analyze SELECT --columns-- FROM KEY_VALUE_META_DATA t0 WHERE
> t0.ID_FOREIGN_TABLE = 21110::bigint;
>
> an index is used. Very fine, the performance is about 10 to 100 times
> faster for the single select.
>
> I am using WebObjects with JDBC. I will now create a DB with integer
> instead of bigint and see how this performs.

The performance will likely to be the same. Its just that integer happens to
be default integer type and hence it does not need an explicit typecast. ( I
don't remember exactly which integer is default but it is either of int2,int4
and int8...:-))

The performance diffference is likely due to use of index, which is in turn
due to typecasting. If you need bigint, you should use them. Just remember to
typecast whenever required.

Shridhar