Re: join to view over custom aggregate seems like it should be faster

On 4/9/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> > I have an odd performance issue on 8.2 that I'd thought I'd document
> > here. I have a workaround, but I'm if there is something that I'm not
> > seeing.
>
> It's hard to comment on this without seeing the full details of the view
> and tables. I'm wondering where the SubPlans are coming from, for instance.

ok, this is really odd. I was in the process of busting all that out
for you when I noticed this:

here is the source sql for the view
create or replace view latest_download as
select software_binary_id, host_id,
((
select latest_software_download(
(bds_status_id,
mtime,
dl_window_open,
dl_window_close,
download_start,
download_stop,
info,
userupgradeable,
overrideflag,
percent_complete)::software_download_data)
)::software_download_data).*
from software_download group by host_id, software_binary_id;

here is what psql \d shows:

SELECT software_download.software_binary_id,
software_download.host_id, ((SELECT
latest_software_download(ROW(software_download.bds_status_id,
software_download.mtime, software_download.dl_window_open,
software_download.dl_window_close, software_download.download_start,
software_download.download_stop, software_download.info,
software_download.userupgradeable, software_download.overrideflag,
software_download.percent_complete)::software_download_data) AS
latest_software_download)).bds_status_id AS bds_status_id, ((SELECT l
[snip]

this is repeated several more times...I replace the view just to be safe.

for posterity:
create or replace function max_software_download(l
software_download_data, r software_download_data) returns
software_download_data as
$$
begin
if l.mtime > r.mtime then
return l;
end if;

return r;
end;
$$ language plpgsql;

CREATE TYPE software_download_data as
(
bds_status_id integer,
mtime timestamp with time zone,
dl_window_open time without time zone,
dl_window_close time without time zone,
download_start timestamp with time zone,
download_stop timestamp with time zone,
info text,
userupgradeable boolean,
overrideflag boolean,
percent_complete integer
);

CREATE AGGREGATE latest_software_download
(
BASETYPE=software_download_data,
SFUNC=max_software_download,
STYPE=software_download_data
);

merlin