Tom Lane wrote:
Kyle Bateman <[EMAIL PROTECTED]> writes:
Is there a way to make the optimizer do this?
Sorry, that's not happening for 8.2. Consider using a union all (not
union) across the subledg_N tables directly and then joining to that.
That boils down to being a partitioning case and I think probably will
be covered by the 8.2 improvements.
Yup, union all is much more efficient. It hadn't really occurred to me
the difference between union and union all. But it makes sense to
eliminate the need for a unique sort. The q3 query went from 10 seconds
to 1 second with just the addition of union all in the general ledger.
BTW, explain analyze still says 10 seconds of run time (and takes 10
seconds to run), but when I remove the explain analyze, the query runs
in about a second. What's that all about?
Also, I came up with the view shown in the attachment. It is still much
faster than joining to the union-all ledger (40 ms). I'm not sure why
because I'm not sure if explain analyze is telling me the real story (I
see a sequential scan of the ledgers in there when it runs 10 seconds).
I'm not sure what it's doing when it runs in 1 second.
Kyle
-- This view is a possible workaround for the problem
drop view gen_ledg_pr;
--explain analyze
create view gen_ledg_pr as
select lg.*, pr.anst_id
from subview_A lg
join proj_rel pr on pr.prog_id = lg.proj
union all select lg.*, pr.anst_id
from subview_B lg
join proj_rel pr on pr.prog_id = lg.proj
union all select lg.*, pr.anst_id
from subview_C lg
join proj_rel pr on pr.prog_id = lg.proj
;
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
