From: Kyle Bateman <kyle@actarg.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: PG 8.2beta reordering working for this case?
Date: Sun, 08 Oct 2006 23:27:40 -0600
Message-ID: <4529DDCC.6010909@actarg.com> (raw)
In-Reply-To: <25779.1160329771@sss.pgh.pa.us>
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>
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
Attachments:workaround.sql (text/x-sql, 428B) download
-- 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
;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kyle@actarg.com, tgl@sss.pgh.pa.us
Subject: Re: PG 8.2beta reordering working for this case?
In-Reply-To: <4529DDCC.6010909@actarg.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox