June 17, 2004
I Love Postgres
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.”
Posted to nerd at June 17, 2004 03:34 PMI luckily stumbled upon this blog about PostgreSQL indexing. I never really considered that it would be my database holding me back much, but it seems I should really analize ...
From: John M Flinchbaugh at June 18, 2004 12:57 AM[PS-560] Problems with postgres indexes and BIGINT columns.: It appears there are some issues with Postgres, indexes on BIGINT coloumns & hibernate. This was first noticed here: http://jira.atlassian.com/browse/CONF-2202 More info here: http://fishbowl.pastiche.org/2004/06/17/i_love_postgres http:...
From: JIRA: PatentSafe at January 23, 2005 09:55 PMif i remember correctly to make 1 use index query should be modified to do explicit cast to type of primary index :
select * from foo where pkey = cast(12345 as bigint);
Posted by: alex korobov at June 17, 2004 08:31 PM (#link)I'll have my DBAs writing SQL for me when they pry my keyboard out of my cold, dead fingers (and then they'll still have trouble until they learn the Dvorak layout...). The DBAs that I have worked with couldn't code their way out of a paper bag, SQL or otherwise. Even if they could, I'll leave it up to them to manage tablespaces and backups - I'll handle SQL and indexing, thank you very much. And yes, this does mean as a developer, it's my responsibilty to understand and troubleshoot problems like the above.
Posted by: Jason Dufair at June 18, 2004 12:37 AM (#link)DBAs shouldn't be writing SQL code for developers. They're administrators....not developers. That being said developers shouldn't be creating indexes either. The DBAs should be doing that.
Posted by: Jason Kratz at June 18, 2004 03:24 AM (#link)Ah, what you need it a tool that will track query frequency and times:
http://pqa.projects.postgresql.org/
My work here is done. *Cackles*
Posted by: Tom Copeland at June 18, 2004 03:56 AM (#link)The skillset for being a DBA doesn't necessarily overlap the skillset for developing good SQL. The skills for developing software generally overlap with SQL development more, but there are often some big blind spots. Good DBA's point out those blind spots and assist the developers with code. The best DBA's also possess the development skill set and tune/write SQL with the best of them.
Posted by: Rob Meyer at June 18, 2004 04:38 AM (#link)developing good SQL? thats funny.. SQL is pretty basic..
Posted by: at July 8, 2004 01:28 PM (#link)developing good SQL? thats funny.. SQL is pretty basic..
Posted by: Ed at July 8, 2004 01:29 PM (#link)I just got bit by this bug.
Is the behavior still there in 7.4?
Er, I don't think it's a bug-- I think it's a datatype feature :)
Posted by: l.m.orchard at July 28, 2004 09:42 AM (#link)