I have some huge postgres tables that seem to be using the wrong index. In a big way. Like, in a 'if I remove one index, the query performance goes up by six orders of magnitude' way. (For those of you counting, that's ~1ms to 32 minutes.) We vacuum and analyze this table daily.
Simplified table for easier parsing:
action
-----
id bigint
org bigint
created datetime without time zone
action_time datetime without time zone
Query:
SELECT min(created) FROM action
WHERE org = 10
AND created > NOW() - INTERVAL '25 hour'
AND action_time < NOW() - INTERVAL '1 hour'
Two indexes:
action (org, action_time, created)
action (org, created, action_time)
Let's say an org creates 200k events a day, and has been running for a year. That means that 99.99% of the items in the action table were created more than an hour ago, and action_time is almost always roughly around when they are created, with much less than 0.01% of them more than a few minutes earlier. This means that around 99.99% of rows satisfy the action_time < NOW() - INTERVAL '1 hour' clause.
On the other hand, around 0.3% of rows were created in the last 25 hours, thereby satisfying the created > NOW() - INTERVAL '25 hour' clause.
So guess which index it uses?
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=55.45..55.46 rows=1 width=8)
InitPlan 1 (returns $0)
-> Limit (cost=0.71..55.45 rows=1 width=8)
-> Index Only Scan using ix_action_org_action_time_created on action (cost=0.71..11498144.88 rows=210051 width=8)
Index Cond: ((org = 50) AND (action_time IS NOT NULL) AND (action_time < (now() - '01:00:00'::interval)) AND (created > (now() - '25:00:00'::interval)))
(5 rows)
Yup! It loads the entire index and scans through literally 99.99% of it searching for the 0.3%, rather than loading 0.3% of the other index and then examining it for the matching 99.99% of those records. Of course, if I drop the second index, it immediately starts using the correct one and the performance goes up accordingly.
Postgres doesn't support index hinting, and as far as I can tell none of the workarounds that the postgres dev team says are much better than index hinting would help here in any way. Possibly there is some way to tell it that created has a roughly uniform distribution over years (and so does action_time)? Would that even help, given that I can't even imagine how it wouldn't know that already? Is there anything else that could help?
edit: explain verbose:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=57.48..57.49 rows=1 width=8)
Output: $0
InitPlan 1 (returns $0)
-> Limit (cost=0.72..57.48 rows=1 width=8)
Output: action.action_time
-> Index Only Scan using ix_action_org_action_time_created on public.action (cost=0.72..11851726.67 rows=208788 width=8)
Output: action.action_time
Index Cond: ((action.org = 10) AND (action.action_time IS NOT NULL) AND (action.action_time < (now() - '01:00:00'::interval)) AND (action.created > (now() - '25:00:00'::interval)) AND (action.created < now()))
(8 rows)
I'll add explain (analyze, buffers, verbose) should this ever finish running. Sigh.
edit2: business logic: action_time is ALMOST always before created. 99.999+% of the time. No other requirements, and even that one isn't perfect.
