Skip Orderby Execution for Materialized Views - Mailing list pgsql-hackers

From Zhang Mingli
Subject Skip Orderby Execution for Materialized Views
Date
Msg-id 5c00bffe-3d90-4157-8627-d650c41e0ea4@Spark
Whole thread Raw
Responses Re: Skip Orderby Execution for Materialized Views
Re: Skip Orderby Execution for Materialized Views
List pgsql-hackers
Hi, all


When create  or refresh a Materialized View, if the view’s query has order by, we may sort and insert the sorted data into view.

Create Materialized View mv1 as select c1, c2 from t1 order by c2;
Refresh Materialized View mv1;

And it appears that we could get ordered data  when select from Materialized View;

Select * from mv1;

But it’s not true if we use other access methods, or we choose a parallel seqscan plan.
A non-parallel seqscan on heap table appears ordered as we always create new rel file and swap them, in my opinion, it’s more like a free lunch.

So, conclusion1:  We couldn’t rely on the `ordered-data` even the mv’s sql has order by clause, is it right?

And if it’s true, shall we skip the order by clause for Materialized View  when executing create/refresh statement?

Materialized View’s order by clause could be skipped if 
  1. Order by clause is on the top query level
  2. There is no real limit clause
The benefit is the query may be speeded up without sort nodes each time creating/refreshing Materialized View.


A simple results:

create table t1 as select i as c1 , i/2 as c2 , i/5 as c3 from generate_series(1, 100000) i;
create materialized view mvt1_order as select c1, c2, c3 from t1 order by c2, c3, c1 asc

Without this patch: 
zml=# refresh materialized view mvt1_order;
REFRESH MATERIALIZED VIEW
Time: 228.548 ms
zml=# refresh materialized view mvt1_order;
REFRESH MATERIALIZED VIEW
Time: 230.374 ms
zml=# refresh materialized view mvt1_order;
REFRESH MATERIALIZED VIEW
Time: 217.079 ms


With this patch:

zml=# refresh materialized view mvt1_order;
REFRESH MATERIALIZED VIEW
Time: 192.409 ms
zml=# refresh materialized view mvt1_order;
REFRESH MATERIALIZED VIEW
Time: 204.398 ms
zml=# refresh materialized view mvt1_order;
REFRESH MATERIALIZED VIEW
Time: 197.510 ms



Zhang Mingli
www.hashdata.xyz
Attachment

pgsql-hackers by date:

Previous
From: Andy Fan
Date:
Subject: Re: make add_paths_to_append_rel aware of startup cost
Next
From: Tom Lane
Date:
Subject: Re: Skip Orderby Execution for Materialized Views