Planner won't use composite index if there is an order by ???? - Mailing list pgsql-performance

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.