Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

[PGPRO-6448] Function unnest renamed to vops_unnest#24

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to ourterms of service andprivacy statement. We’ll occasionally send you account related emails.

Already on GitHub?Sign in to your account

Merged
funny-falcon merged 1 commit intomasterfromPGPRO-6448
Jul 11, 2022
Merged
Show file tree
Hide file tree
Changes fromall commits
Commits
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
24 changes: 12 additions & 12 deletionsREADME.md
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -415,7 +415,7 @@ There are also two important restrictions:
Example of using window functions with
VOPS:

selectunnest(t.*) from (select mcount(*) over w,mcount(x) over w,msum(x) over w,mavg(x) over w,mmin(x) over w,mmax(x) over w,x - lag(x) over w
selectvops_unnest(t.*) from (select mcount(*) over w,mcount(x) over w,msum(x) over w,mavg(x) over w,mmin(x) over w,mmax(x) over w,x - lag(x) over w
from v window w as (rows between unbounded preceding and current row)) t;

### <span id="indexes">Using indexes</span>
Expand DownExpand Up@@ -571,16 +571,16 @@ It accepts name of target VOPS table, path to CSV file, optional
separator (default is ',') and number of lines in CSV header (no header
by default). The function returns number of imported rows.

### <span id="unnest">Back to normal tuples</span>
### <span id="vops_unnest">Back to normal tuples</span>

A query from VOPS projection returns set of tiles. Output function of
tile type is able to print content of the tile. But in some cases it is
preferable to transfer result to normal (horizontal) format where each
tuple represents one record. It can be done using `unnest`
tuple represents one record. It can be done using `vops_unnest`
function:

postgres=# selectunnest(l.*) from vops_lineitem l where filter(l_shipdate <= '1998-12-01'::date) limit 3;
unnest
postgres=# selectvops_unnest(l.*) from vops_lineitem l where filter(l_shipdate <= '1998-12-01'::date) limit 3;
vops_unnest
---------------------------------------
(1996-03-13,17,33078.9,0.04,0.02,N,O)
(1996-04-12,36,38306.2,0.09,0.06,N,O)
Expand All@@ -589,18 +589,18 @@ tuple represents one record. It can be done using `unnest`

### <span id="fdw">Back to normal tables</span>

As it was mentioned in previous section, `unnest` function can scatter
records with VOPS types into normal records with scalar types. So it is
possible to use this records in arbitrary SQL queries. But there are two
problems withunnest function:
As it was mentioned in previous section, `vops_unnest` function can
scatterrecords with VOPS types into normal records with scalar types.
So it ispossible to use this records in arbitrary SQL queries. But
there are twoproblems withvops_unnest function:

1. It is not convenient to use. This function has no static knowledge
about the format of output record and this is why programmer has to
specify it manually, if here wants to decompose this record.
2. PostgreSQL optimizer has completely no knowledge on result of
transformation performed byunnest() function. This is why it is not
able to choose optimal query execution plan for data retrieved from
VOPS table.
transformation performed byvops_unnest() function. This is why it
is notable to choose optimal query execution plan for data
retrieved fromVOPS table.

Fortunately Postgres provides solution for both of this problem: foreign
data wrappers (FDW). In our case data is not really "foreign": it is
Expand Down
16 changes: 8 additions & 8 deletionsexpected/test.out
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -9,9 +9,9 @@ select populate(destination:='v'::regclass, source:='s'::regclass);
6
(1 row)

selectunnest(v.*) from v where x > 1;
unnest
--------
selectvops_unnest(v.*) from v where x > 1;
vops_unnest
-------------
(2)
(3)
(4)
Expand DownExpand Up@@ -71,8 +71,8 @@ select count(*) from v where coalesce(x, 0.0::float8::vops_float4) >= 0;
6
(1 row)

selectunnest(t.*) from (select mcount(*) over w,mcount(x) over w,msum(x) over w,mavg(x) over w,mmin(x) over w,mmax(x) over w,x - lag(x) over w from v window w as (rows between unbounded preceding and current row)) t;
unnest
selectvops_unnest(t.*) from (select mcount(*) over w,mcount(x) over w,msum(x) over w,mavg(x) over w,mmin(x) over w,mmax(x) over w,x - lag(x) over w from v window w as (rows between unbounded preceding and current row)) t;
vops_unnest
-------------------
(1,1,1,1,1,1,)
(2,2,3,1.5,1,2,1)
Expand All@@ -92,9 +92,9 @@ select populate(destination:='v2'::regclass, source:='s2'::regclass,sort:='id');
100
(1 row)

selectunnest(t.*) from (select msum(x,10) over (order by first(id)) from v2) t;
unnest
--------
selectvops_unnest(t.*) from (select msum(x,10) over (order by first(id)) from v2) t;
vops_unnest
-------------
(1)
(3)
(6)
Expand Down
6 changes: 3 additions & 3 deletionssql/test.sql
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -4,7 +4,7 @@ create table s(x real);
create table v(x vops_float4);
insert into s values(1.0),(2.0),(null),(3.0),(null),(4.0);
select populate(destination:='v'::regclass, source:='s'::regclass);
selectunnest(v.*) from v where x > 1;
selectvops_unnest(v.*) from v where x > 1;
select countall(*) from v where x is not null;
select count(*) from v where x is null;
select count(*) from v where x is not null;
Expand All@@ -14,14 +14,14 @@ select count(*),count(x),sum(x),avg(x),min(x),max(x),variance(x),var_pop(x),var_
select count(*),count(x),sum(x),avg(x),min(x),max(x),variance(x),var_pop(x),var_samp(x),stddev(x),stddev_pop(x),stddev_samp(x) from s where x > 1.0;
select count(*) from v where ifnull(x, 0) >= 0;
select count(*) from v where coalesce(x, 0.0::float8::vops_float4) >= 0;
selectunnest(t.*) from (select mcount(*) over w,mcount(x) over w,msum(x) over w,mavg(x) over w,mmin(x) over w,mmax(x) over w,x - lag(x) over w from v window w as (rows between unbounded preceding and current row)) t;
selectvops_unnest(t.*) from (select mcount(*) over w,mcount(x) over w,msum(x) over w,mavg(x) over w,mmin(x) over w,mmax(x) over w,x - lag(x) over w from v window w as (rows between unbounded preceding and current row)) t;

create table s2(x float8, id serial);
insert into s2(select generate_series(1,100));
create table v2(x vops_float8, id vops_int4);
select populate(destination:='v2'::regclass, source:='s2'::regclass,sort:='id');

selectunnest(t.*) from (select msum(x,10) over (order by first(id)) from v2) t;
selectvops_unnest(t.*) from (select msum(x,10) over (order by first(id)) from v2) t;
select sum(x) over (order by id rows between 9 preceding and current row) from s2;

set vops.auto_substitute_projections=on;
Expand Down
8 changes: 4 additions & 4 deletionstpch2.sql
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -118,17 +118,17 @@ select
count(*),
sum(l_extendedprice * (1-l_discount)) as revenue
from
(select c.* from vcustomer vc,unnest(vc.*) c(c_custkey int4,c_nationkey int4,c_acctbal real)) c1
(select c.* from vcustomer vc,vops_unnest(vc.*) c(c_custkey int4,c_nationkey int4,c_acctbal real)) c1
join
(select o.* from vorders vo,unnest(vo.*) o(o_orderkey int4,o_custkey int4,o_orderstatus "char",
(select o.* from vorders vo,vops_unnest(vo.*) o(o_orderkey int4,o_custkey int4,o_orderstatus "char",
o_totalprice real,o_orderdate date,o_shippriority int4)
where vo.o_orderdate >= '1996-01-01'::date and vo.o_orderdate < '1997-01-01'::date) o1
on c_custkey = o_custkey
join
(select l.* from vlineitem vl,unnest(vl.*) l(l_suppkey int4,l_orderkey int4,l_partkey int4,l_shipdate date,l_quantity float4,
(select l.* from vlineitem vl,vops_unnest(vl.*) l(l_suppkey int4,l_orderkey int4,l_partkey int4,l_shipdate date,l_quantity float4,
l_extendedprice float4,l_discount float4,l_tax float4,l_returnflag "char",l_linestatus "char")) l1 on l_orderkey = o_orderkey
join
(select s.* from vsupplier vs,unnest(vs.*) s(s_suppkey int4,s_nationkey int4,s_acctbal real)) s1 on l_suppkey = s_suppkey
(select s.* from vsupplier vs,vops_unnest(vs.*) s(s_suppkey int4,s_nationkey int4,s_acctbal real)) s1 on l_suppkey = s_suppkey
join nation on c_nationkey = n_nationkey
join region on n_regionkey = r_regionkey
where
Expand Down
4 changes: 2 additions & 2 deletionsvops--1.0.sql
View file
Open in desktop
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
/* contrib/vops/vops.sql */
/* contrib/vops/vops--1.0.sql */

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "create extension vops" to load this file. \quit
Expand DownExpand Up@@ -3453,7 +3453,7 @@ create function import(destination regclass, csv_path cstring, separator cstring
create type vops_aggregates as(group_by int8, count int8, aggs float8[]);
create function reduce(bigint) returns setof vops_aggregates as 'MODULE_PATHNAME','vops_reduce' language C parallel safe strict immutable;

create functionunnest(anyelement) returns setof record as 'MODULE_PATHNAME','vops_unnest' language C parallel safe strict immutable;
create functionvops_unnest(anyelement) returns setof record as 'MODULE_PATHNAME','vops_unnest' language C parallel safe strict immutable;

create cast (vops_bool as bool) with function filter(vops_bool) AS IMPLICIT;

Expand Down
18 changes: 9 additions & 9 deletionsvops.html
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -15,7 +15,7 @@ <h1>Vectorized Operations (VOPS)</h1>
<li><a href="#window">Vector window functions</a></li>
<li><a href="#indexes">Using indexes</a></li>
<li><a href="#populating">Preparing data for VOPS</a></li>
<li><a href="#unnest">Back to normal tuples</a></li>
<li><a href="#vops_unnest">Back to normal tuples</a></li>
<li><a href="#fdw">Back to normal tables</a></li>
</ul>
<li><a href="#transform">Standard SQL query transformation</a></li>
Expand DownExpand Up@@ -389,7 +389,7 @@ <h3><a name="window">Vector window functions</a></h3>
Example of using window functions with VOPS:
</p>
<pre>
selectunnest(t.*) from (select mcount(*) over w,mcount(x) over w,msum(x) over w,mavg(x) over w,mmin(x) over w,mmax(x) over w,x - lag(x) over w
selectvops_unnest(t.*) from (select mcount(*) over w,mcount(x) over w,msum(x) over w,mavg(x) over w,mmin(x) over w,mmax(x) over w,x - lag(x) over w
from v window w as (rows between unbounded preceding and current row)) t;
</pre>

Expand DownExpand Up@@ -562,15 +562,15 @@ <h3><a name="populating">Preparing data for VOPS</a></h3>
(no header by default). The function returns number of imported rows.
</p>

<h3><a name="unnest">Back to normal tuples</a></h3>
<h3><a name="vops_unnest">Back to normal tuples</a></h3>
<p>
A query from VOPS projection returns set of tiles. Output function of tile type is able to print content of the tile.
But in some cases it is preferable to transfer result to normal (horizontal) format where each tuple represents one record.
It can be done using <code>unnest</code> function:
It can be done using <code>vops_unnest</code> function:
</p>
<pre>
postgres=# selectunnest(l.*) from vops_lineitem l where filter(l_shipdate &lt;= '1998-12-01'::date) limit 3;
unnest
postgres=# selectvops_unnest(l.*) from vops_lineitem l where filter(l_shipdate &lt;= '1998-12-01'::date) limit 3;
vops_unnest
---------------------------------------
(1996-03-13,17,33078.9,0.04,0.02,N,O)
(1996-04-12,36,38306.2,0.09,0.06,N,O)
Expand All@@ -581,14 +581,14 @@ <h3><a name="unnest">Back to normal tuples</a></h3>

<h3><a name="fdw">Back to normal tables</a></h3>
<p>
As it was mentioned in previous section, <code>unnest</code> function can scatter records with VOPS types into normal records with scalar types.
As it was mentioned in previous section, <code>vops_unnest</code> function can scatter records with VOPS types into normal records with scalar types.
So it is possible to use this records in arbitrary SQL queries.
But there are two problems withunnest function:
But there are two problems withvops_unnest function:
</p>
<ol>
<li>It is not convenient to use. This function has no static knowledge about the format of output record and this is why programmer has to specify it manually,
if here wants to decompose this record.</li>
<li>PostgreSQL optimizer has completely no knowledge on result of transformation performed byunnest() function.
<li>PostgreSQL optimizer has completely no knowledge on result of transformation performed byvops_unnest() function.
This is why it is not able to choose optimal query execution plan for data retrieved from VOPS table.</li>
</ol>
<p>
Expand Down
2 changes: 1 addition & 1 deletionvops_fdw.c
View file
Open in desktop
Original file line numberDiff line numberDiff line change
Expand Up@@ -1509,7 +1509,7 @@ postgresAcquireSampleRowsFunc(Relation relation, int elevel,
}
appendStringInfoString(&sql, " FROM ");
vopsDeparseRelation(&sql, relation);
appendStringInfo(&sql, " t,unnest(t) r(%s)", record.data);
appendStringInfo(&sql, " t,vops_unnest(t) r(%s)", record.data);

portal = SPI_cursor_open_with_args(NULL, sql.data, 0, NULL, NULL, NULL, true, 0);

Expand Down
[8]ページ先頭
©2009-2025 Movatter.jp