How to force the parser to use index scan instead of sequential scan

Hello,

I am using PostgreSQL 7.3.2. I have a large table in a
database , its primary key field is int8.
Almost all of my queries are written relaing to that field. That field
is B-Tree indexed. When i check the query plan i found that all the
queries are using sequential scan and index was not used. So my queries
are likely to be slow. But when i tried to convert the values given to
that field to int8 in a where condition then the parser is using the
index scan, Eg
select * from h057 where h057001 = 1142::int8
the above query uses index scan
select * from h057 where h057001 = 1142
the above query uses sequential scan.
Since there are large number of queries written and being used in the
production it is impossible to change all the queries. Please help me
by giving a suggestion to improve my query performance. I mean any
configuration level changes that helps to force the parser to use index
scan instead of sequential scan in the above case.
Thanks in advance.

ck

On 6 Oct 2006 22:32:16 -0700, ck <ck(dot)karthic(at)gmail(dot)com> wrote:
> Hello,
>
> I am using PostgreSQL 7.3.2. I have a large table in a

that's very old... you should upgrade at least to 7.3.15

> database , its primary key field is int8.
> Almost all of my queries are written relaing to that field. That field
> is B-Tree indexed. When i check the query plan i found that all the
> queries are using sequential scan and index was not used. So my queries
> are likely to be slow. But when i tried to convert the values given to
> that field to int8 in a where condition then the parser is using the
> index scan, Eg
> select * from h057 where h057001 = 1142::int8
> the above query uses index scan
> select * from h057 where h057001 = 1142
> the above query uses sequential scan.

that's because in 7.3 you must cast to the type of the indexed column
in order to use the indexes...

had you never seen this?

TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

upgrade

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook

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

On 10/07/06 21:10, Jaime Casanova wrote:
> On 6 Oct 2006 22:32:16 -0700, ck <ck(dot)karthic(at)gmail(dot)com> wrote:
[snip]
>> index scan, Eg
>> select * from h057 where h057001 = 1142::int8
>> the above query uses index scan
>> select * from h057 where h057001 = 1142
>> the above query uses sequential scan.
>
> that's because in 7.3 you must cast to the type of the indexed column
> in order to use the indexes...
>
> had you never seen this?
>
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

And the default data type for a scalar constant is int4.

- --
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.5 (GNU/Linux)

iD8DBQFFKGD9S9HxQb37XmcRAk+qAJ9BdmEvE8Iug641O7XBnl/AAxiUwwCfWV3V
J1hBmh26MHOcAQ+Fur6EP2U=
=GOPt
-----END PGP SIGNATURE-----