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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
