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

["Merlin Moncure" <mmoncure@xxxxxxxxx>]

On 4/9/07, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
> "Merlin Moncure" <mmoncure@xxxxxxxxx> 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