Thread: sequential scan when using bigint value

I have a table of 2mil records. The table consists of two columns, id
(BYTEA/GUID, PK) and i (INT, UNIQUE INDEX). Could someone explain why,
when using a bigint value like this:

  select * from partition where i=3000000000;

or

  select * from partition where i in (1,2,3,3000000000);

Postgres immediately switches from index scan to seq scan?

--
dave

David Garamond wrote:
> I have a table of 2mil records. The table consists of two columns, id
> (BYTEA/GUID, PK) and i (INT, UNIQUE INDEX). Could someone explain why,
> when using a bigint value like this:
>
>  select * from partition where i=3000000000;
>
> or
>
>  select * from partition where i in (1,2,3,3000000000);
>
> Postgres immediately switches from index scan to seq scan?

I believe it's in the FAQ.  But Postgres always uses a sequential scan
when types don't match.

If you're going to be looking for BIGINTs in that table, you should
probably set i to BIGINT and always cast your search criterea to
BIGINT.  Otherwise, I can't imagine why you would be looking for a
BIGINT in an INT field.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

David Garamond <lists@zara.6.isreserved.com> writes:
> I have a table of 2mil records. The table consists of two columns, id
> (BYTEA/GUID, PK) and i (INT, UNIQUE INDEX). Could someone explain why,
> when using a bigint value like this:
> Postgres immediately switches from index scan to seq scan?

Cross-data-type comparisons aren't indexable.  (At least not in current
releases.  Your examples do work in CVS tip.)

            regards, tom lane