What does pg_stats say about column customer_id? Specifically, how many ndistinct, and what is the sum of the most common values? If you have 1000 distinct customer_id values, and the (default 100) most common values only cover 2% of the total rows, then the optimizer will assume that any given customer_id will yield approx reltuples * .98 / ( 5000 - 100 ) rows. So if your table has 1 million rows, your estimate might be that there should be 200 rows in the table per customer_id in your array.
Looking at your query plan, the optimizer expects rows=8686 for those customer_id and it knows you only want 10 of the most recent ones. It made the right call based on the information it has.
Looking at your query plan, the optimizer expects rows=8686 for those customer_id and it knows you only want 10 of the most recent ones. It made the right call based on the information it has.
Increase default_statistics_target, at least on that column, and see if you get a much much better plan. I don't know where I got this query from online, but here ya go. I'd be curious how frac_MCV in this changes when default_statistics_target is more like 250 or 500 and the table is analyzed again to reflect that change.
SELECT
( SELECT SUM (x) FROM UNNEST (most_common_freqs) x ) frac_MCV,
tablename,
attname,
inherited,
null_frac,
n_distinct,
array_length(most_common_vals,1) n_mcv,
array_length(histogram_bounds,1) n_hist,
correlation,
*
FROM pg_stats
WHERE
schemaname = 'public'
AND tablename='test_orders'
AND attname='customer_id'
ORDER BY 1;
