Message-ID: <4529DDCC.6010909@actarg.com>
Date: Sun, 08 Oct 2006 23:27:40 -0600
From: Kyle Bateman <kyle@actarg.com>
User-Agent: Mozilla Thunderbird 1.0.8-1.1.fc4 (X11/20060501)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: PG 8.2beta reordering working for this case?
References: <44DF6582.4000806@actarg.com> <82mza77rch.fsf@mid.bfk.de>
	<44E11E1A.8020507@actarg.com> <27367.1155606817@sss.pgh.pa.us>
	<44E349BA.4070205@actarg.com> <26676.1155747492@sss.pgh.pa.us>
	<44E3840E.4080502@actarg.com> <45284DBC.3060901@actarg.com>
	<25779.1160329771@sss.pgh.pa.us>
In-Reply-To: <25779.1160329771@sss.pgh.pa.us>
Content-Type: multipart/mixed; boundary="------------090102040909050606070803"

This is a multi-part message in MIME format.
--------------090102040909050606070803
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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


--------------090102040909050606070803
Content-Type: text/x-sql;
 name="workaround.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="workaround.sql"

-- 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
;

--------------090102040909050606070803--