Re: Using indices with long unique IDs.

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!

Dante