Обсуждение: 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.karthic@gmail.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.karthic@gmail.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-----