Using indices with long unique IDs. - Mailing list pgsql-general

From Sergey Olefir (edit e-mail to reply)
Subject Using indices with long unique IDs.
Date
Msg-id 00bd01c3d5fa$bcc82740$b001a8c0@exigengroup.lv
Whole thread Raw
Responses Re: Using indices with long unique IDs.
List pgsql-general
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"

Unfortunately neither of them seem to be portable (for example, the one with
single quotes fails if I create PreparedStatement in Java:
con.prepareStatement("SELECT * FROM table WHERE id='?'"); apparently Java
doesn't parse question mark inside quotes).

I cannot have non-portable SQL as I am not convinced that PostgreSQL is the
right choice for my system. Oh, and by the way, I am using Java + JDBC for
my DB needs.


So what other options do I have? One seems to be NUMERIC( 13, 0 ) [or
something to that effect]. But what impact does it have performance-wise?
(code-wise it makes no difference through JDBC whether I use int4, int8, or
NUMERIC; I still represent that as 'long' in Java)

One thing I am definitely noticing is that NUMERIC( 13, 0 ) does not seem to
ever use sequential scan for queries (verified with EXPLAIN), it always goes
for indices. It that bad (performance wise)? Are there any other issues I
need to be aware of before settling on an uid type?

Thanks in advance,
-------------
Sergey Olefir
Exigen Latvia, system analyst

Honesty is a virtue.
That is if you manage to survive.


pgsql-general by date:

Previous
From: "Earnshaw, Peter J"
Date:
Subject: RedHat install question.
Next
From: Christopher Browne
Date:
Subject: Re: 7.4, 'group by' default ordering?