Here's a quick quiz. You have a table in Postgres 7.3.x with BIGINT primary keys.
Q: What is the difference between the following queries?- select * from foo where pkey = 12345;
- select * from foo where pkey = '12345';
A: The latter does an index lookup. The former performs a full table scan.
We discovered this while messing around with Postgres's EXPLAIN command, trying to work out why Javablogs was running so slowly. It turned out that without the quotes, selecting a single blog entry by its primary key cost 5000 of whatever metric Postgres measures query performance in. With the quotes, the cost dropped to... 5.
If you make the Primary Key a NUMERIC instead of a BIGINT, you get the index scan both ways (but it looks like the index scan over the numeric key is slower than it is over the bigint key...)
Anton tried to explain it to me, but my brain seized up. It was something to do with casting between INT4 and INT8. You can get an unofficial patch for the Postgres JDBC driver that fixes the problem with explicit casts, but it's ugly and untested.
Alan: "Now you know why good DBAs get paid so much, and why they are so horrified at the thought of developers writing SQL."
