How can I tune this query? It got even slower when I created the index for (state_id, order_id desc). The following explain analyze output is without an index. It takes 13 seconds if I create that index. Could you help me?
Thank you so much for your help.
SELECT DISTINCT ON (order_history.order_id) order_id, order_history.creation_date AS c_date FROM work.order_history WHERE (order_history.state_id = ANY (ARRAY[30, 51, 63, 136, 195, 233, 348])) AND order_history.is_false = 0 ORDER BY order_history.order_id DESC;
This query doesn't make much sense to me. You are selecting an arbitrary creation_date for each order_id, which seems like a weird thing to do on purpose. Is your ORDER BY supposed to list another column in it to break the ties?
I wonder how much benefit you are actually getting from the parallel workers. If you lower max_parallel_workers_per_gather, does the plan take proportionally longer?
You should turn on track_io_timing, then repeat the query with EXPLAIN (ANALYZE, BUFFERS).
