VOPS-2.0 - Mailing list pgsql-announce
From | Konstantin Knizhnik |
---|---|
Subject | VOPS-2.0 |
Date | |
Msg-id | 4fb855c3-22b9-444f-21bf-114fa23ccc3c@postgrespro.ru Whole thread Raw |
List | pgsql-announce |
Hi, I want to introduce new version of VOPS extension for Postgres (vectorized operations) providing new, more convenient way of usage: auto-substitution of projections. The idea of VOPS was to use special vector (tile) types instead of standard scalar types, i.e. vops_float8 instead of float8. Such vector types implement all standard arithmetic operators and make it possible to use them in queries almost in the same way as scalar types. But each such operator or aggregate function proceed hundred of values, minimizing interpretation overhead of Postgres executor. This is why VOPS provides more than ten times performance improvement for analytic queries performing filtering and aggregation (like Q1/Q6 TPC-H queries). It was possible to create VOPS tables explicitly but now VOPS provides much more convenient way: VOPS projections. It is assumed that data is stored in normal Postgres table and user can define one or more projections of this table (as in Vertica). Each projection consists of some subset of attributes of original table and some of this attributes preserve the attribute type of original table (them are referred as "scalar attributes"), while other use instead correspondent VOPS types ("vector attributes"). Scalar attributes of projection can be used for sorting, grouping or joining. Vector attributes can be used in where clause and in target list (including aggregate functions). The main main advantage of new version of VOPS is that it allows to automatically substitute queries to original table with queries to one of its projections. So client should not know about presence of VOPS projection and somehow change used queries. VOPS projections are created using create_projection() function which specifies list of vector and scalar attributes: select create_projection('vops_lineitem','lineitem',array['l_shipdate','l_quantity','l_extendedprice','l_discount','l_tax'],array['l_returnflag','l_linestatus']); In future may be "CREATE PROJECTION..." clause will be supported, but it requires changes in PostgreSQL core and can't be implemented at extension level. If "vops.auto_substitute_projections" option is set to true, then parse hook installed by VOPS tries all projections defined for the table and if original table can be substituted by one of this projections, then alternative query is executed. By default "vops.auto_substitute_projections" is switched off, because VOPS doesn't enforce consistency of projections with original table. As with materialized view, refresh of projections should by done manually by user. Certainly it is possible to do it using triggers, but it will be very inefficient: only bulk updates can provide good update and select performance. create_projection(XXX) generates XXX_refresh() function which can be used to periodically update the projection. VOPS supports all version of Postgres starting from 10.0 and can be downloaded from github repository: https://github.com/postgrespro/vops.git -- Konstantin Knizhnik Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
pgsql-announce by date: