Under what conditions would SELECT by PRIMARY KEY be slow?

Question

Chasing down some DB performance issues in a fairly typical EclipseLink/JPA application.

I am seeing frequent queries that are taking 25-100ms. These are simple queries, just selecting all columns from a table where its primary key is equal to a value. They shouldn't be slow.

I'm looking at the query time in the postgres log, using the log_min_duration_statement so this should eliminate any network or application overhead.

This query is not slow, but it is used very often.

Why would selecting * by primary key be slow? Is this specific to postgres or is it a generic DB issue? How can I speed this up? In general? For postgres?

Sample query from the pg log:

2010-07-28 08:19:08 PDT - LOG:  duration: 61.405 ms  statement: EXECUTE <unnamed>  [PREPARE:  SELECT coded_ele
ment_key, code_system, code_system_label, description, label, code, concept_key, alternate_code_key FROM coded
_element WHERE (coded_element_key = $1)]

Table has around 3.5 million rows.

I have also run EXPLAIN and EXPLAIN ANALYZE on this query, its only doing an index scan.

Answer 1

Could you be getting some kind of locking contention? What kind of locks are you taking when performing these queries?

Answer 2

Well, I don't know much about postgres SQL, so I'll give you a tip for MS SQL Server which might be applicable.

MS SQL Server has the concept of a "cluster index" which is the physical layout of the data on the disk. It's good to use on field where you'll be seeking a range between to values (date fields mostly). It's not much use if you're looking for a exact value (like a primary key lookup). However, sometimes the primary key index is inadvertantly set as a clustered index. This makes an index lookup into a table scan.

Answer 3

Select * makes your database work harder, and as a general rule, is a bad practice. There are tons of questions/answers on stackoverflow talking about that.

have you tried replacing * with the field names?

Answer 4

select * is almost always a very very bad idea.

1. If the order of the fields changes, it will break your code. According to comments, this isn't really important given the abstraction library you're using.
2. You're probably returning more data from the table than you actually want. Selecting for the specific fields you want can save transfer time.

25ms is about the lower bound you're going to see on almost any kind of SQL query -- that's only two disk accesses! You might want to look into ways to reduce the number of times the query is run rather than trying to optimize the query.

Answer 5

The the row unusually large or contain BLOBs and large binary fields?

Is this directly through console or is this query being run through some data access API like jdbc or ADO.NET? You mention JPA that looks like a data access API. For short queries, data access API become a larger percent of execution time-- creating the command, creating objects to hold the rows and cells, etc.