Justin Pryzby <pryzby@telsasoft.com> writes:
>> With only two rows in the table, I'm not real sure why you'd need an MCV
>> list. Could we see the actual problem query (and the other table
>> schemas), rather than diving into the code first?
> Sigh, yes, but understand that it's a legacy report which happens to currently
> be near the top of my list of things to improve:
> https://explain.depesz.com/s/5rN6
Hmm, I wonder what you have join_collapse_limit and from_collapse_limit
set to. There's an awful lot of tables in that query.
Also, it seems like most of the rowcount misestimations have to do with
inheritance child tables, eg
Append (cost=0.000..50,814.990 rows=2,156 width=36) (actual time=9.054..1,026.409 rows=429,692 loops=1)
Seq Scan on delta_mike golf_six (cost=0.000..0.000 rows=1 width=36) (actual time=0.009..0.009 rows=0 loops=1)
Filter: ((four_charlie >= 'alpha_six'::timestamp without time zone) AND (four_charlie <= 'four_three'::timestamp
withouttime zone) AND (echo_tango('seven_november'::text, four_charlie) >= 'november_golf'::double precision) AND
(echo_tango('seven_november'::text,four_charlie) <= 'papa_quebec'::double precision))
Index Scan using bravo on papa_two four_delta (cost=0.430..50,814.990 rows=2,155 width=36) (actual
time=9.043..848.063rows=429,692 loops=1)
Index Cond: ((four_charlie >= 'alpha_six'::timestamp without time zone) AND (four_charlie <=
'four_three'::timestampwithout time zone))
Filter: ((echo_tango('seven_november'::text, four_charlie) >= 'november_golf'::double precision) AND
(echo_tango('seven_november'::text,four_charlie) <= 'papa_quebec'::double precision))
There's not a lot of point in worrying about your two-row table when these
other estimates are off by multiple orders of magnitude. In this
particular case my first bet would be that the planner has no idea about
the selectivity of the conditions on "echo_tango('seven_november'::text,
four_charlie)". Reformulating that, or maybe making an index on it just
so that ANALYZE will gather stats about it, could help.
regards, tom lane
