Here's a corner case where the optimizer is doing a redundant sort. I'm not
sure if I'm doing something wrong or if it's just something the optimizer
doesn't notice.
The second index scan, the one on cache_foo, is on a two-column index. Since
it has an Index Cond on the first column, it's effectively scanning in the
order of the second column. That second column is precisely the join
condition, so it could do a merge join without an extra sort. It's actually
doing the merge join but it's doing a useless sort first.
db=> explain analyze select * from foo_bar join cache_foo using (foo_id) where key_id = 839;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=4053.86..5060.00 rows=2641 width=32) (actual time=111.47..562.41 rows=8640 loops=1)
Merge Cond: ("outer".foo_id = "inner".foo_id)
-> Index Scan using idx_foo_bar_foo on foo_bar (cost=0.00..853.34 rows=45288 width=8) (actual time=0.03..239.75
rows=45140loops=1)
-> Sort (cost=4053.86..4060.46 rows=2640 width=24) (actual time=111.37..121.70 rows=8641 loops=1)
Sort Key: cache_foo.foo_id
-> Index Scan using idx_cache_foo_foo on cache_foo (cost=0.00..3903.82 rows=2640 width=24) (actual
time=0.05..47.48rows=8666 loops=1)
Index Cond: (key_id = 839)
Total runtime: 577.10 msec
(8 rows)
Time: 580.41 ms
db=> \d cache_foo
Table "public.cache_foo"
Column | Type | Modifiers
-------------------+------------------+-----------
key_id | integer |
foo_id | integer |
Indexes: idx_cache_foo_foo btree (key_id, foo_id)
[Sorry, but I have to search+replace on the names at the client's request]
--
greg
