Обсуждение: join to view over custom aggregate seems like it should be faster
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. ok, for starters: I have a large table that is basically organized like this: create table big ( key1 int, key2 int, ts timestamp [other fields] ); and a view most_recent_big which lists for each combination of key1 and key2, the '[other fields]' that are behind the highest (most recent) timestamp. The original view implementation involved a self join which is the classic sql approach to pulling values from a denormalized table (the real solution of course is to normalize the data but I can't do that for various reasons). This wasn't very fast, so I wrote a custom aggregate to optimize the view (there are usuallly very small #s of records for key1, key2 pair: create view latest_big_view as select key1, key2, max_other_fields[other fields] from big group by key1, key2; This worked very well, but sometimes the index on key1, key2 does not get utilized when joining against latest_big_view. Let's say I have a number of key1, key2 pairs in another table: for example: select * from foo, latest_big_view using (key1, key2); breaks down. here is a example of the 'breakdown' plan on real tables. selecting a single record from the view is very fast...1ms or less. The join can't 'see through' the view to filter the index. dev20400=# explain analyze select * from foo join latest_download using (host_id, software_binary_id); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=15.35..4616.65 rows=25 width=90) (actual time=229.623..10601.317 rows=494 loops=1) Hash Cond: ((latest_download.host_id = foo.host_id) AND (latest_download.software_binary_id = foo.software_binary_id)) -> GroupAggregate (cost=0.00..4499.01 rows=4535 width=94) (actual time=0.346..10370.383 rows=37247 loops=1) -> Index Scan using software_download_idx on software_download (cost=0.00..2526.53 rows=45342 width=94) (actual time=0.028..344.591 SubPlan -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.006..0.011 rows=1 loops=37247) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.006..0.011 rows=1 loops=37247) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.005..0.011 rows=1 loops=37247) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.006..0.011 rows=1 loops=37247) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.006..0.011 rows=1 loops=37247) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.006..0.011 rows=1 loops=37247) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.005..0.011 rows=1 loops=37247) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.005..0.011 rows=1 loops=37247) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.006..0.011 rows=1 loops=37247) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.005..0.010 rows=1 loops=37247) -> Hash (cost=7.94..7.94 rows=494 width=8) (actual time=5.568..5.568 rows=494 loops=1) -> Seq Scan on foo (cost=0.00..7.94 rows=494 width=8) (actual time=0.018..2.686 rows=494 loops=1) Total runtime: 10604.260 ms (18 rows) Here is the same query but on the root table, instead of the view: dev20400=# explain analyze select * from foo join software_download using (host_id, software_binary_id); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..1521.60 rows=19 width=94) (actual time=0.084..24.992 rows=607 loops=1) -> Seq Scan on foo (cost=0.00..7.94 rows=494 width=8) (actual time=0.044..2.753 rows=494 loops=1) -> Index Scan using software_download_idx on software_download (cost=0.00..3.05 rows=1 width=94) (actual time=0.011..0.019 rows=1 loops=49 Index Cond: ((foo.host_id = software_download.host_id) AND (foo.software_binary_id = software_download.software_binary_id)) Total runtime: 28.385 ms (5 rows) I can use a trick with a function to make the view give out reasonalbe results: create function foo(int, int) returns latest_download as $$ select * from latest_download where software_binary_id = $1 and host_id = $2; $$ language sql; dev20400=# explain analyze select (v).* from (select foo(software_binary_id, host_id) as v from foo) q; QUERY PLAN --------------------------------------------------------------------------------------------------------- Subquery Scan q (cost=0.00..14.12 rows=494 width=32) (actual time=1.436..139.644 rows=494 loops=1) -> Seq Scan on foo (cost=0.00..9.18 rows=494 width=8) (actual time=1.414..131.144 rows=494 loops=1) Total runtime: 142.887 ms (3 rows) Time: 144.306 ms merlin
"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. regards, tom lane
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
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. 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. merlin
On 4/9/07, Merlin Moncure <mmoncure@gmail.com> wrote: > 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. > > 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
"Merlin Moncure" <mmoncure@gmail.com> writes: > 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.... Nope, that's not going to work, because the aggregate keeps the subquery from being flattened into the upper query, which is what would have to happen for a nestloop-with-inner-indexscan join to be considered. AFAICS you've got to structure it so that the aggregation happens above the join. regards, tom lane
On 4/10/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Merlin Moncure" <mmoncure@gmail.com> writes: > > 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.... > > Nope, that's not going to work, because the aggregate keeps the subquery > from being flattened into the upper query, which is what would have to > happen for a nestloop-with-inner-indexscan join to be considered. > AFAICS you've got to structure it so that the aggregation happens above > the join. right, i see that it's actually the 'group by' that does it: select a, b from foo join (select a, b from bar group by a,b) q using (a,b); is enough to keep it from using the index on a,b from bar. thats too bad... merlin
"Merlin Moncure" <mmoncure@gmail.com> writes: > right, i see that it's actually the 'group by' that does it: > select a, b from foo join (select a, b from bar group by a,b) q using (a,b); > is enough to keep it from using the index on a,b from bar. thats too bad... Some day it'd be nice to be able to reorder grouping/aggregation steps relative to joins, the way we can now reorder outer joins. Don't hold your breath though ... I think it'll take some pretty major surgery on the planner. regards, tom lane