Re: A limit clause can cause a poor index choice - Mailing list pgsql-general

From Michael Lewis
Subject Re: A limit clause can cause a poor index choice
Date
Msg-id CAHOFxGreoSAfz0dwoCGPCFJzbLv1h_aP=ojhDSudOhGEAZG8pw@mail.gmail.com
Whole thread Raw
In response to Re: A limit clause can cause a poor index choice  (Mohamed Wael Khobalatte <mkhobalatte@grubhub.com>)
Responses Re: A limit clause can cause a poor index choice  (Nick Cleaton <nick@cleaton.net>)
List pgsql-general
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.

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;

pgsql-general by date:

Previous
From: Mohamed Wael Khobalatte
Date:
Subject: Re: A limit clause can cause a poor index choice
Next
From: Alfonso
Date:
Subject: Q: Comparing PostgreSQL and Oracle