Re: Using indices with long unique IDs.

Sergey Olefir (edit e-mail to reply) wrote:

>Hello!
>
>I am planning to use unique IDs in the little system I am building. Now
>being more than a little paranoid (and having no idea about expected loads),
>I am wary of using int4 as a basis for uids (for the fear of ever running
>out of them).
>
>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').
>
>There are two suggested work-arounds:
>"SELECT * FROM table WHERE id='1'"
>"SELECT * FROM table WHERE id=1::int8"
>
>
1. I believe 7.4 contains modifications that would allow for index scans
WHERE id=1
2. You could always start the sequence at 5 billion, in which case a
pre-7.4 version will also use the index:

[estore@lexus] explain select * from employees where employee = 5000000000;
                                   QUERY
PLAN
--------------------------------------------------------------------------------
 Index Scan using i_employees1 on employees  (cost=0.00..4.68 rows=1
width=264)
   Index Cond: (employee = 5000000000::bigint)
(2 rows)

3. If you want *globally* unique ids then you must use a different type.
I believe there's a UUID project on gborg, IIRC.

Mike Mascari