The type int8 and the use of indexes

The type int8 and the use of indexes

Post by nel.. » Sat, 27 Jan 2001 07:16:54



   Hi,

   I realized that PostgreSQL (7.0.3) optimizer doesnt 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

 
 
 

The type int8 and the use of indexes

Post by Stephan Sza » Sat, 27 Jan 2001 08:21:38



Quote:

>    Hi,

>    I realized that PostgreSQL (7.0.3) optimizer doesnt 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.

 
 
 

The type int8 and the use of indexes

Post by Igor V. Rafienk » Sat, 27 Jan 2001 18:37:15


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

 
 
 

The type int8 and the use of indexes

Post by Igor V. Rafienk » Sun, 28 Jan 2001 00:26:34


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

 
 
 

The type int8 and the use of indexes

Post by Tom La » Sun, 28 Jan 2001 00:26:35



Quote:> 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.

Quote:> 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

 
 
 

The type int8 and the use of indexes

Post by n.. » Sun, 28 Jan 2001 10:55:55


   Hi,

   I realized that PostgreSQL (7.0.3) optimizer doesnt 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