Basically the query matches the rows that have a tag1 OR tag2 OR tag3 OR tag4 OR tag5...
Each branch of the OR is anticipated to return 400 rows, but it actually returns 0. If it actually were to return 400 rows per branch, than eventually the plan switch actually would make sense.
Why is the estimate off by so much? If you run a simple select, what the actual and expected number of rows WHERE project_id = 12345? WHERE tags @> '{crt:2018_11}'? Is one of those estimates way off reality, or is it only the conjunction which is deranged?
How can I encourage PostgreSQL to use the Bitmap Index Scan even when there are many OR conditions? I have tried with VACUUM ANALYZE subscriptions but it doesn't help.
Note: the query is generated dynamically by customers of a SaaS, so I don't have full control on it
Do you have enough control to change the ORDER BY to:
ORDER BY ("subscriptions"."created_at" + interval '0 days') DESC
