New in PostgreSQL 9.3

Database Tuning

More Memory

Up to and including PostgreSQL 9.2, it was impossible to use more than 2GB effectively for the maintenance_work_mem or work_mem settings to sort data in RAM because of a limitation hard-coded into the database. This restriction is very significant, especially for DDL commands that use these parameters. For example, the CREATE INDEX command now effectively benefits from a high setting in maintenance_work_mem if very large indexes need to be generated. Sorting to build the index can then ideally be handled completely by Quicksort in the database server's RAM, without having to resort to the storage system.

Legacy systems have always set this option to a high value (even if it was never actually used), but the value needs to be checked during migration; otherwise, there is a risk that the system will suddenly making correspondingly high memory allocations to reflect the setting.

Indexable Regular Expressions

Previous versions of PostgreSQL were able to index regular expressions using expression indexes. However, this only worked for static regular expressions, and if more words had to be indexed, it quickly became ineffective because of the number of required indexes. In version 9.3, PostgreSQL now has the ability to accelerate dynamic regular expressions using a special index.

The pg_trgm contrib module was extended in PostgreSQL 9.3 so that it can respond to arbitrary regular expressions via an index. Because it is also in the contrib branch of the database, you must install it retroactively using CREATE EXTENSION. Listing 2 shows an example with the mail table that creates an index and compares the execution schedules with and without an index. The differences in costs and execution times with and without the index show a significant boost to the query speed.

Listing 2

Create Extension

01 CREATE EXTENSION pg_trgm;
02 CREATE INDEX ON mail USING gin(msg gin_trgm_ops);
03
04 EXPLAIN ANALYZE SELECT * FROM mail WHERE msg ~ '(updatable|views)';
05                                                          QUERY PLAN
06 ----------------------------------------------------------------------------------------------------
07  Bitmap Heap Scan on mail (cost=128.03..143.74 rows=4 width=961) (actual
08  time=35.454..175.184 rows=672 loops=1)
09    Recheck Cond: (msg ~ '(updatable|views)'::text)
10    Rows Removed by Index Recheck: 978
11    ->  Bitmap Index Scan on mail_msg_idx  (cost=0.00..128.03 rows=4 width=0)
12    (actual time=34.925..34.925 rows=1650 loops=1)
13          Index Cond: (msg ~ '(updatable|views)'::text)
14  Total runtime: 175.403 ms
15 (6 rows)
16
17 EXPLAIN ANALYZE SELECT * FROM mail WHERE msg ~ '(updatable|views)';
18                                                QUERY PLAN
19 ----------------------------------------------------------------------------------------------------
20  Seq Scan on mail (cost=0.00..5628.25 rows=4 width=961) (actual
21  time=2.401..1519.809 rows=672 loops=1)
22    Filter: (msg ~ '(updatable|views)'::text)
23    Rows Removed by Filter: 40148
24  Total runtime: 1.519.991 ms
25 (4 rows)

LATERAL Statement

Version 9.3 of PostgreSQL now supports LATERAL as defined in the SQL standard. This keyword allows the developer to use sub-selects to reference other columns or values of the join operation from within joins.

A simple example will illustrate this: In general, it has so far not been possible in PostgreSQL to use the result of a join partner as a function argument in a function.

However, PostgreSQL 9.3, as Listing 3 shows, provides a simplified example of a set-returning function (SRF).

Listing 3

Set-Returning Function

01 CREATE OR REPLACE FUNCTION get_book_by_authorid(IN integer)
02 RETURNS SETOF text
03 STRICT
04 LANGUAGE SQL
05 WP
06 $$
07         SELECT b.title FROM book b WHERE author_id = $1;
08 $$;
09
10 book =# SELECT * FROM author a, get_book_by_authorid(a.id);
11 ERROR:  function expression in FROM cannot refer to other relations of same
12 query level
13 LINE 1: SELECT * FROM author a, get_book_by_authorid(a.id);
14
15 SELECT * FROM author a, LATERAL get_book_by_authorid(a.id);
16
17  id |       name       |         get_book_by_authorid
18 ----+------------------+--------------------------------------
19   1 | Bernd Helmle     | PostgreSQL Administration
20   2 | Andreas Eschbach | One Trillion Dollars
21   3 | Mario Puzo       | The Godfather
22   4 | Peter Eisentraut | PostgreSQL Administration
23   4 | Peter Eisentraut | PostgreSQL - The Official Guide
24 (5 rows)

LATERAL is particularly interesting for join partners such as sub-selects. The same rules apply: Previous link partners can be referenced directly with LATERAL in the sub-select definition. The LATERAL keyword is mandatory here, as shown in Listing 4.

Listing 4

LATERAL

01 SELECT
02    a.id, a.name, t.title
03 FROM author a,
04      (SELECT author_id, title FROM book b WHERE b.author_id = a.id AND b.title LIKE '%PostgreSQL%') AS t;
05 ERROR:  invalid reference to FROM-clause entry for table "a"
06 LINE 1: ...CT author_id, title FROM book b WHERE b.author_id = a.id) AS t...
07                                                              ^
08 HINT:  There is an entry for table "a", but it cannot be referenced from this part of the query.
09 </box>
10
11 # But LATERAL makes this join correct:
12
13 <box>
14 SELECT
15    a.id, a.name, t.title
16 FROM author a,
17      LATERAL (SELECT author_id, title FROM book b WHERE b.author_id = a.id AND b.title LIKE '%PostgreSQL%') AS t;
18  id |       name       |                title
19 ----+------------------+--------------------------------------
20   1 | Bernd Helmle     | PostgreSQL Administration
21   4 | Peter Eisentraut | PostgreSQL Administration
22   4 | Peter Eisentraut | PostgreSQL - The Official Guide
23 (3 rows)

For complex subqueries within joins, this is a significant improvement; however, it should not lead programmers always to formulate join subqueries in this way. The previous example can easily be rewritten as a conventional JOIN.

« Previous 1 2 3 4 Next »