Author: depesz

Waiting for PostgreSQL 11 – Improve run-time partition pruning to handle any stable expression.

On 10th of June 2018, Tom Lane committed patch:

Improve run-time partition pruning to handle any stable expression.
 
 
The initial coding of the run-time-pruning feature only coped with cases
where the partition key(s) are compared to Params.  That is a bit silly;
we can allow it to work with any non-Var-containing stable expression, as
long as we take special care with expressions containing PARAM_EXEC Params.
The code is hardly any longer this way, and it's considerably clearer
(IMO at least).  Per gripe from Pavel Stehule.
 
David Rowley, whacked around a bit by me
 
Discussion: https://postgr.es/m/CAFj8pRBjrufA3ocDm8o4LPGNye9Y+pm1b9kCwode4X04CULG3g@mail.gmail.com

Continue reading Waiting for PostgreSQL 11 – Improve run-time partition pruning to handle any stable expression.

Waiting for PostgreSQL 11 – Support partition pruning at execution time

On 7th of April 2018, Alvaro Herrera committed patch:

Support partition pruning at execution time
 
 
Existing partition pruning is only able to work at plan time, for query
quals that appear in the parsed query.  This is good but limiting, as
there can be parameters that appear later that can be usefully used to
further prune partitions.
 
This commit adds support for pruning subnodes of Append which cannot
possibly contain any matching tuples, during execution, by evaluating
Params to determine the minimum set of subnodes that can possibly match.
We support more than just simple Params in WHERE clauses. Support
additionally includes:
 
1. Parameterized Nested Loop Joins: The parameter from the outer side of the
   join can be used to determine the minimum set of inner side partitions to
   scan.
 
2. Initplans: Once an initplan has been executed we can then determine which
   partitions match the value from the initplan.
 
Partition pruning is performed in two ways.  When Params external to the plan
are found to match the partition key we attempt to prune away unneeded Append
subplans during the initialization of the executor.  This allows us to bypass
the initialization of non-matching subplans meaning they won't appear in the
EXPLAIN or EXPLAIN ANALYZE output.
 
For parameters whose value is only known during the actual execution
then the pruning of these subplans must wait.  Subplans which are
eliminated during this stage of pruning are still visible in the EXPLAIN
output.  In order to determine if pruning has actually taken place, the
EXPLAIN ANALYZE must be viewed.  If a certain Append subplan was never
executed due to the elimination of the partition then the execution
timing area will state "(never executed)".  Whereas, if, for example in
the case of parameterized nested loops, the number of loops stated in
the EXPLAIN ANALYZE output for certain subplans may appear lower than
others due to the subplan having been scanned fewer times.  This is due
to the list of matching subnodes having to be evaluated whenever a
parameter which was found to match the partition key changes.
 
This commit required some additional infrastructure that permits the
building of a data structure which is able to perform the translation of
the matching partition IDs, as returned by get_matching_partitions, into
the list index of a subpaths list, as exist in node types such as
Append, MergeAppend and ModifyTable.  This allows us to translate a list
of clauses into a Bitmapset of all the subpath indexes which must be
included to satisfy the clause list.
 
Author: David Rowley, based on an earlier effort by Beena Emerson
Reviewers: Amit Langote, Robert Haas, Amul Sul, Rajkumar Raghuwanshi,
Jesper Pedersen
Discussion: https://postgr.es/m/CAOG9ApE16ac-_VVZVvv0gePSgkg_BwYEV1NBqZFqDR2bBE0X0A@mail.gmail.com

Continue reading Waiting for PostgreSQL 11 – Support partition pruning at execution time

Waiting for PostgreSQL 11 – Indexes with INCLUDE columns and their support in B-tree

On 7th of April 2018, Teodor Sigaev committed patch:

Indexes with INCLUDE columns and their support in B-tree
 
This patch introduces INCLUDE clause to index definition.  This clause
specifies a list of columns which will be included as a non-key part in
the index.  The INCLUDE columns exist solely to allow more queries to
benefit from index-only scans.  Also, such columns don't need to have
appropriate operator classes.  Expressions are not supported as INCLUDE
columns since they cannot be used in index-only scans.
 
Index access methods supporting INCLUDE are indicated by amcaninclude flag
in IndexAmRoutine.  For now, only B-tree indexes support INCLUDE clause.
 
In B-tree indexes INCLUDE columns are truncated from pivot index tuples
(tuples located in non-leaf pages and high keys).  Therefore, B-tree indexes
now might have variable number of attributes.  This patch also provides
generic facility to support that: pivot tuples contain number of their
attributes in t_tid.ip_posid.  Free 13th bit of t_info is used for indicating
that.  This facility will simplify further support of index suffix truncation.
The changes of above are backward-compatible, pg_upgrade doesn't need special
handling of B-tree indexes for that.
 
Bump catalog version
 
Author: Anastasia Lubennikova with contribition by Alexander Korotkov and me
Reviewed by: Peter Geoghegan, Tomas Vondra, Antonin Houska, Jeff Janes,
             David Rowley, Alexander Korotkov
Discussion: https://www.postgresql.org/message-id/flat/.4010101@postgrespro.ru

Continue reading Waiting for PostgreSQL 11 – Indexes with INCLUDE columns and their support in B-tree

Waiting for PostgreSQL 11 – Add json(b)_to_tsvector function

On 7th of April 2018, Teodor Sigaev committed patch:

Add json(b)_to_tsvector function
 
Jsonb has a complex nature so there isn't best-for-everything way to convert it
to tsvector for full text search. Current to_tsvector(json(b)) suggests to
convert only string values, but it's possible to index keys, numerics and even
booleans value. To solve that json(b)_to_tsvector has a second required
argument contained a list of desired types of json fields. Second argument is
a jsonb scalar or array right now with possibility to add new options in a
future.
 
Bump catalog version
 
Author: Dmitry Dolgov with some editorization by me
Reviewed by: Teodor Sigaev
Discussion: https://www.postgresql.org/message-id/CA+q6zcXJQbS1b4kJ_HeAOoOc=unfnOrUEL=KGgE32QKDww7d8g@mail.gmail.com

Continue reading Waiting for PostgreSQL 11 – Add json(b)_to_tsvector function

Waiting for PostgreSQL 11 – Transforms for jsonb to PL/Python and to PL/Perl

On 28th of March 2018, Peter Eisentraut committed patch:

Transforms for jsonb to PL/Python
 
Add a new contrib module jsonb_plpython that provide a transform between
jsonb and PL/Python.  jsonb values are converted to appropriate Python
types such as dicts and lists, and vice versa.
 
Author: Anthony Bykov <a.bykov@postgrespro.ru>

and then, on 3rd of April 2018, he also committed patch:

Transforms for jsonb to PL/Perl
 
Add a new contrib module jsonb_plperl that provides a transform between
jsonb and PL/Perl.  jsonb values are converted to appropriate Perl types
such as arrays and hashes, and vice versa.
 
Author: Anthony Bykov <a.bykov@postgrespro.ru>

Continue reading Waiting for PostgreSQL 11 – Transforms for jsonb to PL/Python and to PL/Perl

Waiting for PostgreSQL 11 – MERGE SQL Command following SQL:2016

update!

It looks that this has been reverted

On 2nd of April 2018, Simon Riggs committed patch:

MERGE SQL Command following SQL:2016
 
MERGE performs actions that modify rows in the target table
using a source table or query. MERGE provides a single SQL
statement that can conditionally INSERT/UPDATE/DELETE rows
a task that would other require multiple PL statements.
e.g.
 
MERGE INTO target AS t
USING source AS s
ON t.tid = s.sid
WHEN MATCHED AND t.balance > s.delta THEN
  UPDATE SET balance = t.balance - s.delta
WHEN MATCHED THEN
  DELETE
WHEN NOT MATCHED AND s.delta > 0 THEN
  INSERT VALUES (s.sid, s.delta)
WHEN NOT MATCHED THEN
  DO NOTHING;
 
MERGE works with regular and partitioned tables, including
column and row security enforcement, as well as support for
row, statement and transition triggers.
 
MERGE is optimized for OLTP and is parameterizable, though
also useful for large scale ETL/ELT. MERGE is not intended
to be used in preference to existing single SQL commands
for INSERT, UPDATE or DELETE since there is some overhead.
MERGE can be used statically from PL/pgSQL.
 
MERGE does not yet support inheritance, write rules,
RETURNING clauses, updatable views or foreign tables.
MERGE follows SQL Standard per the most recent SQL:2016.
 
Includes full tests and documentation, including full
isolation tests to demonstrate the concurrent behavior.
 
This version written from scratch in 2017 by Simon Riggs,
using docs and tests originally written in 2009. Later work
from Pavan Deolasee has been both complex and deep, leaving
the lead author credit now in his hands.
Extensive discussion of concurrency from Peter Geoghegan,
with thanks for the time and effort contributed.
 
Various issues reported via sqlsmith by Andreas Seltenreich
 
Authors: Pavan Deolasee, Simon Riggs
Reviewers: Peter Geoghegan, Amit Langote, Tomas Vondra, Simon Riggs
 
Discussion:
https://postgr.es/m/CANP8+jKitBSrB7oTgT9CY2i1ObfOt36z0XMraQc+Xrz8QB0nXA@mail.gmail.com
https://postgr.es/m/CAH2-WzkJdBuxj9PO=2QaO9-3h3xGbQPZ34kJH=HukRekwM-GZg@mail.gmail.com

Continue reading Waiting for PostgreSQL 11 – MERGE SQL Command following SQL:2016

Waiting for PostgreSQL 11 – Fast ALTER TABLE ADD COLUMN with a non-NULL default

On 28th of March 2018, Andrew Dunstan committed patch:

Fast ALTER TABLE ADD COLUMN with a non-NULL default
 
 
Currently adding a column to a table with a non-NULL default results in
a rewrite of the table. For large tables this can be both expensive and
disruptive. This patch removes the need for the rewrite as long as the
default value is not volatile. The default expression is evaluated at
the time of the ALTER TABLE and the result stored in a new column
(attmissingval) in pg_attribute, and a new column (atthasmissing) is set
to true. Any existing row when fetched will be supplied with the
attmissingval. New rows will have the supplied value or the default and
so will never need the attmissingval.
 
Any time the table is rewritten all the atthasmissing and attmissingval
settings for the attributes are cleared, as they are no longer needed.
 
The most visible code change from this is in heap_attisnull, which
acquires a third TupleDesc argument, allowing it to detect a missing
value if there is one. In many cases where it is known that there will
not be any (e.g.  catalog relations) NULL can be passed for this
argument.
 
Andrew Dunstan, heavily modified from an original patch from Serge
Rielau.
Reviewed by Tom Lane, Andres Freund, Tomas Vondra and David Rowley.
 
Discussion: https://postgr.es/m/-7002-4c27-59f5-@2ndQuadrant.com

Continue reading Waiting for PostgreSQL 11 – Fast ALTER TABLE ADD COLUMN with a non-NULL default

paste.depesz.com is no more

Some time ago I wrote a site to paste SQL queries with reformatting/pretty-printing using pgFormatter library.

Today, I figured out that I should update the library since it has quite some changes recently, so it would be good to incorporate its fixes to paste site.

Unfortunately – new version is not backward compatible, and I currently have no time to figure out what has changed and how to work around it.

So – until I will have time to work on it, paste.depesz.com is no longer working. Sorry.