I've been working quite a bit on a query that's not operating very efficiently on a DB2 database. The thing will return up to 30k rows or so and take up to 10 seconds. I've been working on getting this thing to run faster and I think I've finally tracked it down to an improper index. A new and more appropriate index should fix the problem, but I'm not entirely sure how this should be ordered as there appears to be different ways for different data.
The query itself isn't terribly complicated. Here's a brief rundown:
SELECT
...
B.TYPE_CODE,
B.FIELD,
...
FROM A
INNER JOIN B
ON A.ID1 = B.A_ID1
AND A.ID2 = B.A_ID2
--A few inner joins and some left joins--
WHERE
B.TYPE_CODE IN (:parameter) --No more than four values within the IN clause
A few details about these tables:
- A has about 900k rows and B has about 2.7 million.
- B.TYPE_CODE has 15 possible values. Stats are accurate. The four parameters together are contained in about 400k rows (about 15% of all entries in the table). The A_ID1/2 columns are much more unique, occurring 15 times at most, of course.
- B already has an index on (TYPE_CODE, A_ID1, A_ID2). This is being used, but it's not optimal at all
- There are 6 rows being selected by the DB2-optimized query. It grabs A_ID1 and 2 along with TYPE_CODE and FIELD, of course, but it's also grabbing B.ID1 and B.ID2, which together form the primary key for B. These fields are not used in the original query, but the optimizer is including them, although I don't think they're used in any joins or filters. They're just there.
- B will have far more reads than write operations on it
The join between A and B is taking a third of the query cost. The explain plan shows a FETCH operation that uses a particular index four times. I was able to get a VERY detailed explain plan report and found that this operation is returning all 400k rows, then performing the join, which only needs about 26k of them.
I found a short-circuit method that added this to the predicate:
AND COALESCE(B.TYPE_CODE, B.TYPE_CODE) = B.TYPE_CODE
Logically this doesn't affect the result set, but it does tweak the optimizer. Adding this cut the query time in half. Checking the detailed explain showed that it added an additional filter factor that reduced the number of estimated rows in that FETCH operation to 26k or so, which is about right. This in turn reduced the estimate for the number of rows from A that would be returned overall and enabled index scans on A that hadn't been used prior due to the optimizer thinking it could be grabbing nearly half of A. The result was a much faster query. However, the COALESCE is a hacky bit and not suited for production, but it was handy for finding an issue.
So now it falls to creating a new index on B to fix this problem. The DB2 query analyzer suggested an index on all six fields, starting with B.TYPE_CODE and following with the rest in no particular logical order. This would cover all selected fields and the table itself would not need to be touched, which certainly has its advantages. It has also been suggested that an index on (B.A_ID1, B.A_ID2, B.TYPE_CODE) could be used as the more selective columns are first, which would narrow the results faster. I've seen different suggestions based on the cardinality of the data, so I'm wondering if anyone has some advice on how to construct the index here. I've been doing a lot of reading on indexes lately, but it can be difficult to find a good guide on certain aspects.
UPDATE: Well, DB2 refuses to use the new index and keeps using the existing index of (TYPE_CODE, B.ID1, B.ID2), and those latter two are the primary key for B, not to be confused with B.A_ID1, B.A_ID2. I think it has to to do with the optimized query grabbing B.ID1 and B.ID2 even though the original query doesn't touch those fields and using them as a SORTKEY. It may be that any index will need to include those fields.
