Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 52BB59FB460 for ; Tue, 10 Apr 2007 10:01:59 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 37305-02-2 for ; Tue, 10 Apr 2007 10:01:35 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.224]) by postgresql.org (Postfix) with ESMTP id 473EE9FB507 for ; Tue, 10 Apr 2007 09:59:25 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id i28so1685636wxd for ; Tue, 10 Apr 2007 05:59:24 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=pzGScobJoSUmF2GnnobZeZszt37oqqmLRdCc943T5D2Vcdkhbwrbx8o+f/hWnwBBDGgqjBXiaBEqysCKe1AuiykQam2weauJkBt9wKek6PJqLyR6qhQcvC01oPwlDM+4TMcbgh+jpjDg6Yz4ILya9eKplkwBFo+RbilEpINzr5U= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=QHmTMbLQ1lkbAYRdJrSh2pAMBFR/+TJvckCzo1OoNo9DTm867mebk5pcZorDGnaZm20UZgbeoKT3VzMR0CtweeYgnir3c/sEtxnNjctQBiWdKq2YibabtuN1FaXkytfjRwE/jpczzojNhRsmseETQjQaYiexPEnE/sseWnBxltc= Received: by 10.78.150.7 with SMTP id x7mr1051099hud.1176209963220; Tue, 10 Apr 2007 05:59:23 -0700 (PDT) Received: by 10.78.35.18 with HTTP; Tue, 10 Apr 2007 05:59:23 -0700 (PDT) Message-ID: Date: Tue, 10 Apr 2007 08:59:23 -0400 From: "Merlin Moncure" To: "Tom Lane" Subject: Re: join to view over custom aggregate seems like it should be faster Cc: "PostgreSQL Performance" In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <10651.1176152866@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/224 X-Sequence-Number: 24101 On 4/9/07, Merlin Moncure wrote: > On 4/9/07, Tom Lane wrote: > > "Merlin Moncure" 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