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