Re: View with an outer join - is there any way to optimise

Tom Lane wrote:
> Rich Doughty <rich(at)opusvl(dot)com> writes:
>
>>I have a view vw_tokens defined as
>>...
>>I cannot however perform a meaningful join against this view.
>>...
>>PG forms the full output of the view.
>
>
> You seem to be wishing that PG would push the INNER JOIN down inside the
> nested LEFT JOINs. In general, rearranging inner and outer joins like
> that can change the results. There are limited cases where it can be
> done without breaking the query semantics, but the planner doesn't
> currently have any logic to analyze whether it's safe or not, so it just
> doesn't try.
>
> Improving this situation is (or ought to be) on the TODO list, but I dunno
> when it will happen.

ok, thanks. as i suspected, i don't think i'm going to be able to views for
this. when the query is ultimately returning only 100 or so rows, i cannot
afford a full 4 million row table scan to form the full view when a nested
loop might make more sense (anything makes more sense than the full view :-)

i have a workaround (of sorts). instead of

WHERE token_id IN (SELECT token_id FROM ta_tokenhist WHERE sarreport_id = 9)

if i perform the subquery manually, then create a second query of the form

WHERE token_id IN (?,?,?,?,?)

i get decent results. it's pretty ugly but it works. i doubt that it will
scale up to 500 or more results (if that), but thankfully in general, neither
do the query results.

cheers anyway

- Rich Doughty