From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | "Sergey Olefir (edit e-mail to reply)" <spam-me-not-so3lv(at)yahoo(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Using indices with long unique IDs. |
Date: | 2004-01-09 03:51:41 |
Message-ID: | 3FFE254D.9030208@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)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
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Browne | 2004-01-09 04:05:28 | Re: Natural upgrade path for RedHat 9? |
Previous Message | Michael Glaesemann | 2004-01-09 03:32:38 | Re: start/stop a database |