Re: join to view over custom aggregate seems like it should be faster - Mailing list pgsql-performance

On 4/9/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Merlin Moncure" <mmoncure@gmail.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