Re: JOIN performance

"Dean Gibson (DB Administrator)" <postgresql3(at)ultimeth(dot)com> writes:
> Question: why do the last two column definitions in the second VIEW change
> the scan on _LicHD from indexed to sequential ??

It's the CASE that's getting you. The poor plan is basically because
the sub-view isn't getting "flattened" into the upper query, and so it's
not possible to choose a plan for it that's dependent on the upper query
context. And the reason it's not getting flattened is that subselects
that are on the nullable side of an outer join can't be flattened unless
they have nullable targetlists --- otherwise the results might not go to
NULL when they are supposed to. A CASE construct is always going to be
treated as non-nullable.

Fixing this properly is a research project, and I haven't thought of any
quick-and-dirty hacks that aren't too ugly to consider :-(

In the meantime, you could easily replace that CASE construct with a
min() function that's declared strict. I think date_smaller would
do nicely, assuming the columns are actually of type date.

regards, tom lane