I am trying different solutions and what I have found is even more surprising to me...
The query is always this: https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-slow_query-txt I have added this index which would allow an index only scan: "index_subscriptions_on_project_id_and_created_at_and_tags" btree (project_id, created_at DESC, tags) WHERE trashed_at IS NULL But Postgresql continues to use this index (which has less information and then requires slow access to disk): "index_subscriptions_on_project_id_and_created_at" btree (project_id, created_at DESC) On Fri, Jan 10, 2020 at 4:06 AM Justin Pryzby <pry...@telsasoft.com> wrote: > On Fri, Jan 10, 2020 at 02:11:14AM +0100, Marco Colli wrote: > > I have a query on a large table that is very fast (0s): > > > https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-fast_query-txt > > ORDER BY + LIMIT is a query which sometimes has issues, you can probably > find > more by searching. The planner thinks it'll hit the LIMIT pretty soon and > only > run a fraction of the index scan - but then it turns out to be wrong. > > You might have poor statistics on project_id and/or tags. This *might* > help: > ALTER TABLE subscriptions ALTER project_id SET STATISTICS 2000; ANALYZE > subscriptions; > > But I'm guessing there's correlation between the two, which the planner > doesn't > know. If you're running at least v10, I'm guessing it would help to CREATE > STATISTICS on those columns (and analyze). > > See one similar problem here (not involving LIMIT). > > https://www.postgresql.org/message-id/flat/CABFxtPedz4zL%2BaPWut4%2B%3Dum4av1aAXr6OVRfRB_6K7mJKMbEcw%40mail.gmail.com >
