Re: PG 8.2beta reordering working for this case? - Mailing list pgsql-sql

Tom Lane wrote:

>Kyle Bateman <kyle@actarg.com> 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
;