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

On 4/9/07, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> 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.
>
> ah, it looks like the aggregate is being re-expanded for each field
> returned by the aggregate. I notice this for non-trivial record
> returning functions also. standard m.o. is to push into a subquery
> and expand afterwords.

[sorry for the deluge of info]

I cleaned up the view from:
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;

to this:
create or replace view latest_download as
select software_binary_id, host_id, (v).* from
(
select
software_binary_id, host_id,
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) as v
from software_download group by host_id, software_binary_id
) q;

this cleaned up the odd subplans but is still slow:
dev20400=# explain analyze select * from foo join latest_download
using (host_id, software_binary_id);

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=1308.84..1467.81 rows=25 width=40) (actual
time=1472.668..1914.799 rows=494 loops=1)
Hash Cond: ((q.host_id = foo.host_id) AND (q.software_binary_id =
foo.software_binary_id))
-> HashAggregate (cost=1293.48..1350.17 rows=4535 width=94)
(actual time=1467.002..1700.388 rows=37247 loops=1)
-> Seq Scan on software_download (cost=0.00..953.42
rows=45342 width=94) (actual time=0.014..274.747 rows=45342 loops=1)
-> Hash (cost=7.94..7.94 rows=494 width=8) (actual
time=5.028..5.028 rows=494 loops=1)
-> Seq Scan on foo (cost=0.00..7.94 rows=494 width=8)
(actual time=0.022..2.507 rows=494 loops=1)
Total runtime: 1918.721 ms

compare it to this:
dev20400=# explain analyze select * from foo f where exists (select *
from latest_download where host_id = f.host_id and software_binary_id
= f.software_binary_id);

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on foo f (cost=0.00..3122.01 rows=247 width=8) (actual
time=0.152..45.941 rows=494 loops=1)
Filter: (subplan)
SubPlan
-> Subquery Scan q (cost=0.00..6.30 rows=1 width=40) (actual
time=0.081..0.081 rows=1 loops=494)
-> GroupAggregate (cost=0.00..6.29 rows=1 width=94)
(actual time=0.065..0.065 rows=1 loops=494)
-> Index Scan using software_download_idx on
software_download (cost=0.00..6.27 rows=1 width=94) (actual
time=0.013..0.021 r
Index Cond: ((host_id = $0) AND
(software_binary_id = $1))
Total runtime: 48.323 ms
(8 rows)

Time: 49.851 ms

I since I need both sides, I can't figure out a way to force the index
to be used during the join except to use a function to look up the
view based on the key, which works:
dev20400=# explain analyze select latest_download(host_id,
software_binary_id) from foo;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..9.18 rows=494 width=8) (actual
time=0.566..51.605 rows=494 loops=1)
Total runtime: 54.290 ms
(2 rows)

dev20400=# explain analyze select * from latest_download where host_id
= 1 and software_binary_id = 12345;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan q (cost=0.00..6.30 rows=1 width=40) (actual
time=0.046..0.046 rows=0 loops=1)
-> GroupAggregate (cost=0.00..6.29 rows=1 width=94) (actual
time=0.035..0.035 rows=0 loops=1)
-> Index Scan using software_download_idx on
software_download (cost=0.00..6.27 rows=1 width=94) (actual
time=0.024..0.024 rows=0 lo
Index Cond: ((host_id = 1) AND (software_binary_id = 12345))
Total runtime: 0.134 ms

For some reason, I can't get the index to be used on the table sitting
under a view during a join, even though it should be, or at least it
seems....

merlin