Last time I wrote about what explain output shows. Now I'd like to talk more about various types of “nodes" / operations that you might see in explain plans.
Author: depesz
Win a book contest – PostgreSQL Backup and Restore How-to
Around the time that Xzilla wrote about the book, Packt contacted me and asked for a review.
Since I generally don't really read technical books, I declined the offer, but Sandy from Packt was very persistent, and asked if I could inform about book giveaway contest.
The book is definitely PostgreSQL related, and Xzilla suggested to check it out, so here it goes:
Continue reading Win a book contest – PostgreSQL Backup and Restore How-to
Explaining the unexplainable
One of the first things new DBA hears is “Use the EXPLAIN". And upon first try he/she is greeted with incomprehensible:
QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Sort (cost=146.63..148.65 ROWS=808 width=138) (actual TIME=55.009..55.012 ROWS=71 loops=1) Sort KEY: n.nspname, p.proname, (pg_get_function_arguments(p.oid)) Sort Method: quicksort Memory: 43kB -> Hash JOIN (cost=1.14..107.61 ROWS=808 width=138) (actual TIME=42.495..54.854 ROWS=71 loops=1) Hash Cond: (p.pronamespace = n.oid) -> Seq Scan ON pg_proc p (cost=0.00..89.30 ROWS=808 width=78) (actual TIME=0.052..53.465 ROWS=2402 loops=1) FILTER: pg_function_is_visible(oid) -> Hash (cost=1.09..1.09 ROWS=4 width=68) (actual TIME=0.011..0.011 ROWS=4 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan ON pg_namespace n (cost=0.00..1.09 ROWS=4 width=68) (actual TIME=0.005..0.007 ROWS=4 loops=1) FILTER: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))
What does it even mean?
Waiting for 9.3 – Support indexing of regular-expression searches in contrib/pg_trgm.
On 9th of April, Tom Lane committed patch:
Support indexing of regular-expression searches in contrib/pg_trgm. This works by extracting trigrams from the given regular expression, in generally the same spirit as the previously-existing support for LIKE searches, though of course the details are far more complicated. Currently, only GIN indexes are supported. We might be able to make it work with GiST indexes later. The implementation includes adding API functions to backend/regex/ to provide a view of the search NFA created from a regular expression. These functions are meant to be generic enough to be supportable in a standalone version of the regex library, should that ever happen. Alexander Korotkov, reviewed by Heikki Linnakangas and Tom Lane
One day later Tom Lane added support for the same operations using GiST indexes (original patch was working only with GIN).
Waiting for 9.3 – Add \watch [SEC] command to psql.
On 4th of April, Tom Lane committed patch:
Add \watch [SEC] command to psql. This allows convenient re-execution of commands. Will Leinweber, reviewed by Peter Eisentraut, Daniel Farina, and Tom Lane
Continue reading Waiting for 9.3 – Add \watch [SEC] command to psql.
New OmniPITR release
Changes on explain.depesz.com
So, I made some changes to explain.depesz.com, and while informing about it, I'd like to brag a bit about numbers, again 🙂
Waiting for 9.3 – Add new JSON processing functions and parser API.
On 29th of March, Andrew Dunstan committed patch:
Add new JSON processing functions and parser API. The JSON parser is converted into a recursive descent parser, and exposed for use by other modules such as extensions. The API provides hooks for all the significant parser event such as the beginning and end of objects and arrays, and providing functions to handle these hooks allows for fairly simple construction of a wide variety of JSON processing functions. A set of new basic processing functions and operators is also added, which use this API, including operations to extract array elements, object fields, get the length of arrays and the set of keys of a field, deconstruct an object into a set of key/value pairs, and create records from JSON objects and arrays of objects. Catalog version bumped. Andrew Dunstan, with some documentation assistance from Merlin Moncure.
Continue reading Waiting for 9.3 – Add new JSON processing functions and parser API.
Waiting for 9.3 – Add parallel pg_dump option.
On 24th of March, Andrew Dunstan committed patch:
Add parallel pg_dump option. New infrastructure is added which creates a set number of workers (threads on Windows, forked processes on Unix). Jobs are then handed out to these workers by the master process as needed. pg_restore is adjusted to use this new infrastructure in place of the old setup which created a new worker for each step on the fly. Parallel dumps acquire a snapshot clone in order to stay consistent, if available. The parallel option is selected by the -j / --jobs command line parameter of pg_dump. Joachim Wieland, lightly editorialized by Andrew Dunstan.
Continue reading Waiting for 9.3 – Add parallel pg_dump option.
Waiting for 9.3 – Support writable foreign tables.
On 10th of March, Tom Lane committed patch:
Support writable foreign tables. This patch adds the core-system infrastructure needed to support updates on foreign tables, and extends contrib/postgres_fdw to allow updates against remote Postgres servers. There's still a great deal of room for improvement in optimization of remote updates, but at least there's basic functionality there now. KaiGai Kohei, reviewed by Alexander Korotkov and Laurenz Albe, and rather heavily revised by Tom Lane.
Continue reading Waiting for 9.3 – Support writable foreign tables.