> That seems strange to me also, particularly if the index column ordering
> is indeed actor_id,case_id and not the other way round
Tom-
Actually, it *is* the other way around- I didn't realize that could make a
difference. Here's the line that creates it:
create unique index actor_case_assignment_both on
actor_case_assignment(case_id,actor_id);
I reversed the order, and now the explain looks more like I expected:
develop=# explain SELECT * FROM CRIMINAL_DETAIL WHERE case_id = '102SC01353'
ORDER BY CHARGE_COUNT,CHARGE_NUMBER;
NOTICE: QUERY PLAN:
Sort (cost=9263.85..9263.85 rows=1 width=308)
-> Hash Join (cost=155.06..9263.84 rows=1 width=308)
-> Hash Join (cost=120.53..6034.05 rows=79880 width=260)
-> Seq Scan on charge (cost=0.00..2664.80 rows=79880
width=184)
-> Hash (cost=109.82..109.82 rows=4282 width=76)
-> Seq Scan on criminal_disposition (cost=0.00..109.82
rows=4282 width=76)
-> Hash (cost=34.53..34.53 rows=4 width=48)
-> Nested Loop (cost=0.00..34.53 rows=4 width=48)
-> Index Scan using case_data_case_id on case_data
(cost=0.00..4.01 rows=1 width=24)
-> Index Scan using actor_case_assignment_case_id on
actor_case_assignment (cost=0.00..30.42 rows=7 width=24)
I think this solves my immediate problem, but it seems like even with the
reversed order, the planner shouldn't have chosen the combined index to
drive the query, so I'm happy for now, but I fear that I've added a task to
someone's list in the developer's enclave.
I'll attach a schema dump to an off-list email to to you. Although we're not
incredibly proud of it, I think the company would consider it proprietary &
not to be posted publicly.
I'm running postgresql v7.1.3 on Debian
Thanks for the help, & let me know if there is any other info I can pass
along to help figure out what is happening.
-Nick
