Planner won't use composite index if there is an order by ????

Any idea why it wouldn't choose the right index ?

This is 8.3

> # \d battles
> Table "public.battles"
> Column | Type |
> Modifiers
> ---------------------+-----------------------------
> +------------------------------------------------------
> id | integer | not null default
> nextval('battles_id_seq'::regclass)
> user_id | integer | not null
> contest_id | integer | not null
> entry_1_id | integer | not null
> entry_2_id | integer | not null
> new_entry_1_score | integer |
> new_entry_2_score | integer |
> score | integer |
> scored_at | timestamp without time zone |
> created_at | timestamp without time zone | not null
> function_profile_id | integer |
> battle_type | integer | default 0
> Indexes:
> "battles_pkey" PRIMARY KEY, btree (id)
> "unique_with_type" UNIQUE, btree (user_id, entry_1_id, entry_2_id,
> battle_type)
> "battles_by_contest_and_type" btree (contest_id, battle_type)
> "battles_by_time" btree (scored_at)
> Foreign-key constraints:
> "fk_battles_contests" FOREIGN KEY (contest_id) REFERENCES
> contests(id)
> "fk_battles_lefty" FOREIGN KEY (entry_1_id) REFERENCES entries(id)
> "fk_battles_righty" FOREIGN KEY (entry_2_id) REFERENCES entries(id)
> "fk_battles_users" FOREIGN KEY (user_id) REFERENCES users(id)
>
>
> Here is the analyze of the query we want but it takes forever because
> its using the index for the sort instead of restricting the number of
> battles by user_id:
>
> ourstage_production=# explain analyze SELECT * FROM battles WHERE
> user_id = 196698 and scored_at is not null and score in (-3,3) ORDER
> BY
> id DESC LIMIT 5;
>
> QUERY PLAN
>
> -------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..8381.61 rows=5 width=56) (actual
> time=124421.499..183659.404 rows=2 loops=1)
> -> Index Scan Backward using battles_pkey on battles
> (cost=0.00..670528.67 rows=400 width=56) (actual
> time=124421.495..183659.394 rows=2 loops=1)
> Filter: ((scored_at IS NOT NULL) AND (score = ANY
> ('{-3,3}'::integer[])) AND (user_id = 196698))
> Total runtime: 183659.446 ms
> (4 rows)
>
>
> If you remove the ORDER BY then it runs in 4 ms:
>
> ourstage_production=# explain analyze SELECT * FROM battles WHERE
> user_id = 196698 and scored_at is not null and score in (-3,3) LIMIT
> 5;
> QUERY
> PLAN
>
> ---------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..126.65 rows=5 width=56) (actual time=4.607..4.621
> rows=2 loops=1)
> -> Index Scan using unique_with_type on battles
> (cost=0.00..10131.66 rows=400 width=56) (actual time=4.603..4.611
> rows=2 loops=1)
> Index Cond: (user_id = 196698)
> Filter: ((scored_at IS NOT NULL) AND (score = ANY
> ('{-3,3}'::integer[])))
> Total runtime: 4.660 ms
> (5 rows)
>
>
> Here we tried to limit the table scan by time so that it would scan
> far
> fewer records. But what ended up happening is that it flipped it over
> to using the right index. The one that is based on user_id is much
> preferred:
>
>
> ourstage_production=# explain analyze SELECT * FROM battles WHERE
> user_id = 196698 and scored_at is not null and score in (-3,3) and
> scored_at > now() - INTERVAL '6 month' ORDER BY id DESC LIMIT 5;
> QUERY
> PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=10158.16..10158.18 rows=5 width=56) (actual
> time=0.097..0.106 rows=2 loops=1)
> -> Sort (cost=10158.16..10158.92 rows=302 width=56) (actual
> time=0.094..0.096 rows=2 loops=1)
> Sort Key: id
> Sort Method: quicksort Memory: 25kB
> -> Index Scan using unique_with_type on battles
> (cost=0.00..10153.15 rows=302 width=56) (actual time=0.069..0.078
> rows=2 loops=1)
> Index Cond: (user_id = 196698)
> Filter: ((scored_at IS NOT NULL) AND (score = ANY
> ('{-3,3}'::integer[])) AND (scored_at > (now() - '6 mons'::interval)))
> Total runtime: 0.152 ms
> (8 rows)
>
>
> Notice that we added time restriction and it now chooses to not use
> the
> time index and goes after the index based on user_id. Why? We
> don't know.