Re: Using indices with long unique IDs.

Hi,

D. Dante Lorenso wrote:
> Sergey Olefir wrote:
>
>> So the logical choice would be int8, right? Unfortunately quite wrong.
>> Statement of the form: "SELECT * FROM table WHERE id=1"
>> will never use index for id (assumming id is int8) since '1' is of type
>> int4. This is confirmed both by documentation and SQL EXPLAIN (after set
>> enable_seqscan TO 'off').
>>
>>
> I'm using BIGSERIAL as the primary key for all my tables. Please tell
> me that what is described above will not be true for me as well!
> When I say:
>
> SELECT x, y, z
> FROM mytable
> WHERE pk_mybigint = 1;
>
> That had better be using an index, or in a few months, OMG! Let me check:
>
> leads=> EXPLAIN ANALYSE SELECT * FROM leads WHERE lead_id = 555300;
> QUERY
> PLAN
> ---------------------------------------------------------------------------------------------------
>
> Seq Scan on leads (cost=0.00..334.66 rows=1 width=263) (actual
> time=21.35..21.46 rows=1 loops=1)
> Filter: (lead_id = 555300)
> Total runtime: 21.53 msec
> (3 rows)
> leads=> EXPLAIN ANALYSE SELECT * FROM leads WHERE lead_id =
> 555300::bigint;
> QUERY
> PLAN
> ----------------------------------------------------------------------------------------------------------------
>
> Index Scan using pk_leads on leads (cost=0.00..5.36 rows=1
> width=263) (actual time=0.18..0.18 rows=1 loops=1)
> Index Cond: (lead_id = 555300::bigint)
> Total runtime: 0.24 msec
> (3 rows)
>
> Well, that just plain sucks. That means I've gotta go back and
> add casts to all my queries?
>
> Tell me it isn't so!

use always '' even for numbers. where int = '1' or bigint = '1'. Change
your programing style :)

C.