From: | Rich Doughty <rich(at)opusvl(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: View with an outer join - is there any way to optimise |
Date: | 2005-12-13 09:47:02 |
Message-ID: | 439E9896.1060900@opusvl.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Marko Kreen | 2005-12-13 10:28:22 | Re: Quick hack: permissions generator |
Previous Message | Jim C. Nasby | 2005-12-13 08:59:09 | Re: pg_autovacuum |