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.