Re: Complex filters -> Bad row estimates -> bad query plan

Thanks Michael.

I'll give some join alternatives a shot first... but, that's cool.

What about OFFSET 0 makes this approach work? I'm thinking the OFFSET 0
create an optimization barrier that prevents the planner from collapsing
that sub-query into the top query, and enforces ordering in the query?

I appreciate your thoughts, thank-you very much for the feedback.

Mathieu

On Wed, Aug 21, 2019 at 12:08 PM Michael Lewis <mlewis(at)entrata(dot)com> wrote:

> If those conditions that are throwing off the stats are expected to be
> minimally impactful/filtering few rows, then you can use the one
> tried-and-true optimizer hint (aside from materialized CTEs, stylized
> indexes, etc) --- OFFSET 0 at the end of a sub-query.
>
> SELECT * FROM ( [your existing query without the sub-selects that are
> complicated and produce bad estimates] OFFSET 0 ) WHERE [your other
> conditions that don't produce good estimates]
>
> If there is correlation between field1 and field2, you might also look at
> CREATE STATISTICS assuming you are on PG 10 or 11.
>
> Before I do any of that, I would try LEFT JOIN for Table3 and Table4 then
> use the where conditon "AND 2 = COALESCE( Table3.Status, Table4.Status"
> and see if the optimizer likes that option better.
>