Обсуждение: The type int8 and the use of indexes
Hi, I realized that PostgreSQL (7.0.3) optimizer doesn´t use indexes in a query like "select * from my table where pk=1", where the field pk is of type int8. I changed the type to int4, and now it uses the index (a lot faster). I also tryed changing the type to numeric, with the same results. Why does it happen? Is there something special with type int8 ??? Thanks, Nelson
On Thu, 25 Jan 2001 nelson@radix.com.br wrote: > > Hi, > > I realized that PostgreSQL (7.0.3) optimizer doesn�t use indexes in a query > like "select * from my table where pk=1", where the field pk is of > type int8. > I changed the type to int4, and now it uses the index (a lot faster). I also > tryed changing the type to numeric, with the same results. > Why does it happen? Is there something special with type int8 ??? Forgot about that. :( The reason is that the 1 is being treated as an int4 and it's not smart enough to realize it can use the int8 index for the search. select * from table where pk=1::int8 should use the index if pk is an int8 column.
on Jan 25, 2001, 15:14, Stephan Szabo std::cout'ed: [snip] | select * from table where pk=1::int8 should use the index if pk is an | int8 column. Would that work for other operators (such as <, >, etc.) as well? Last time I tried something similar on Postgres-7.0.2, the index was used on equality but not on "less-than" (the number of rows in the result was very small). ivr -- Everybody has to have a philosophy. Some people believe in laissez faire economics, others believe in reincarnation. Some people even believe that COBOL is a real programming language. S. Meyers, "Effective C++", item 26
"Igor V. Rafienko" <igorr@ifi.uio.no> writes: > on Jan 25, 2001, 15:14, Stephan Szabo std::cout'ed: > | select * from table where pk=1::int8 should use the index if pk is an > | int8 column. > Would that work for other operators (such as <, >, etc.) as well? It's a necessary prerequisite, anyway. > Last time I tried something similar on Postgres-7.0.2, the index was > used on equality but not on "less-than" (the number of rows in the > result was very small). It won't use an index unless the optimizer knows that the number of rows to be selected is small, which for a one-sided "<" query would depend on where the endpoints of the data range are. Had you done a VACUUM ANALYZE recently? regards, tom lane
on Jan 26, 2001, 10:20, Tom Lane std::cout'ed: | It won't use an index unless the optimizer knows that the number of | rows to be selected is small, which for a one-sided "<" query would | depend on where the endpoints of the data range are. Had you done | a VACUUM ANALYZE recently? Yes, I believe I did run vacuum analyze just before the test. ivr -- "You have an internet connection on your calculator." "You can type 70 words per minute -- on a TI-82." "You've gotten electronic copies of text books in TI-82 format." -- Technology in IB