Often, you either can't or don't want to rewrite the query. However, you can still try and speed it up through any of the techniques discussed here.
As a first step, you can start providing better information to the optimizer.
EXPLAIN ANALYZE reveals that the estimates in the database differ a lot from the metrics returned by the actual query execution, you need to instruct PostgreSQL to collect more fine-grained statistics.
The current default statistics target can be shown using this command:
You can set it to a higher value in the
postgresql.conf file. Alternatively, if you want to do this only for a single database, you can use
ALTER DATABASE, as follows: ...