Postgres Pro
Downloads
Home   >   mailing lists

Thread: The type int8 and the use of indexes

The type int8 and the use of indexes

From
nelson@radix.com.br
Date:
   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

Re: The type int8 and the use of indexes

From
Stephan Szabo
Date:
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.

Re: The type int8 and the use of indexes

From
"Igor V. Rafienko"
Date:
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

Re: The type int8 and the use of indexes

From
Tom Lane
Date:
"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

Re: The type int8 and the use of indexes

From
"Igor V. Rafienko"
Date:
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