PostgreSQL queries

This page defines the syntax of the SQL SELECT statement supported for PostgreSQL databases in the preview release of the PostgreSQL interface feature.

Notations used in the syntax

  • Square brackets [ ] indicate optional clauses.
  • Curly braces { } enclose a set of options.
  • The vertical bar | indicates a logical OR.
  • A comma followed by an ellipsis indicates that the preceding item can repeat in a comma-separated list.
    • item [, ...] indicates one or more items, and
    • [item, ...] indicates zero or more items.
  • Purple-colored text, such as item, marks Cloud Spanner extensions to open-source PostgreSQL.
  • Parentheses ( ) indicate literal parentheses.
  • A comma , indicates the literal comma.
  • Angle brackets <> indicate literal angle brackets.
  • Uppercase words, such as INSERT, are keywords.

SELECT

Use the SELECT statement to retrieve data from a database.

[ /*@ hint_expression [, ...] */ ] SELECT select-list
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT count ]
    [ OFFSET start ]


where select-list is:

    [ ALL ] [ * | expression [ [ AS ] output_name ] [, ...] ]


and from_item is one of:

    table_name [ /*@ table_hint_expression [, ...] */ ]
         [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    from_item join_type [ /*@ join_hint_expression [, ...] */ ]
        from_item [ ON join_condition | USING ( join_column [, ...] ) ]


and join_type is one of:

    [ INNER ] JOIN
    LEFT [ OUTER ] JOIN
    RIGHT [ OUTER ] JOIN
    FULL [ OUTER ] JOIN
    CROSS JOIN


and grouping_element is one of:

    ( )
    expression
    ( expression [, ...] )


and hint_expression is one of:

    statement_hint_key = statement_hint_value
    table_hint_key = table_hint_value
    join_hint_key = join_hint_value


and table_hint_expression is:

    table_hint_key = table_hint_value


and join_hint_expression is:

    join_hint_key = join_hint_value


and statement_hint_key is:

    USE_ADDITIONAL_PARALLELISM | LOCK_SCANNED_RANGES


and table_hint_key is:

    FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION


and join_hint_key is:

    FORCE_JOIN_ORDER | JOIN_METHOD | HASH_JOIN_BUILD_SIDE | BATCH_MODE

Cloud Spanner extensions to open-source PostgreSQL

[ /*@ statement_hint_key = statement_hint_value [, ...] */ ]

where statement_hint_key is:

    USE_ADDITIONAL_PARALLELISM | LOCK_SCANNED_RANGES

Cloud Spanner supports the following statement hints as extensions to open-source PostgreSQL.

Hint keyPossible valuesDescription
USE_ADDITIONAL_PARALLELISM TRUE |
FALSE (default)
If TRUE, the execution engine favors using more parallelism when possible.

Because this can reduce resources available to other operations, you may want to avoid this hint if you run latency-sensitive operations on the same instance.

LOCK_SCANNED_RANGES exclusive |
shared (default)
Use this hint to request an exclusive lock on a set of ranges scanned by a transaction. Acquiring an exclusive lock helps in scenarios when you observe high write contention, that is, you notice that multiple transactions are concurrently trying to read and write to the same data, resulting in a large number of aborts.

Without the hint, it's possible that multiple simultaneous transactions will acquire shared locks, and then try to upgrade to exclusive locks. This will cause a deadlock, because each transaction's shared lock is preventing the other transaction(s) from upgrading to exclusive. Cloud Spanner aborts all but one of the transactions.

When requesting an exclusive lock using this hint, one transaction acquires the lock and proceeds to execute, while other transactions wait their turn for the lock. Throughput is still limited because the conflicting transactions can only be performed one at a time, but in this case Cloud Spanner is always making progress on one transaction, saving time that would otherwise be spent aborting and retrying transactions.

This hint is supported on all statement types, both query and DML.

Cloud Spanner always enforces serializability. Lock mode hints can affect which transactions wait or abort in contended workloads, but don't change the isolation level.

Because this is just a hint, it should not be considered equivalent to a mutex. In other words, you should not use Cloud Spanner exclusive locks as a mutual exclusion mechanism for the execution of code outside of Cloud Spanner.

For more information, see Locking.

[ /*@ table_hint_key = table_hint_value [, ...] */ ]

where table_hint_key is:

    FORCE_INDEX | GROUPBY_SCAN_OPTIMIZATION

Cloud Spanner supports the following table hints as extensions to open-source PostgreSQL.

Hint keyPossible valuesDescription
FORCE_INDEX String. The name of an existing index in the database or _BASE_TABLE to use the base table rather than an index.
  • If set to the name of an index, use that index instead of the base table. If the index cannot provide all needed columns, perform a back join with the base table.
  • If set to the string _BASE_TABLE, use the base table for the index strategy instead of an index. Note that this is the only valid value when FORCE_INDEX is used in a statement hint expression.

Note: FORCE_INDEX is actually a directive, not a hint, which means an error is raised if the index does not exist.

GROUPBY_SCAN_OPTIMIZATION TRUE |
FALSE

The group by scan optimization can make queries faster if they use GROUP BY or SELECT DISTINCT. It can be applied if the grouping keys can form a prefix of the underlying table or index key, and if the query requires only the first row from each group.

The optimization is applied if the optimizer estimates that it will make the query more efficient. The hint overrides that decision. If the hint is set to FALSE, the optimization is not considered. If the hint is set to TRUE, the optimization will be applied as long as it is legal to do so.

[ /*@ join_hint_key = join_hint_value [, ...] */ ]

where join_hint_key is:

    FORCE_JOIN_ORDER | JOIN_METHOD | HASH_JOIN_BUILD_SIDE | BATCH_MODE

Cloud Spanner supports the following join hints as extensions to open-source PostgreSQL.

Hint keyPossible valuesDescription
FORCE_JOIN_ORDER TRUE |
FALSE (default)
If set to true, use the join order that's specified in the query.
JOIN_METHOD HASH_JOIN |
APPLY_JOIN |
MERGE_JOIN |
PUSH_BROADCAST_HASH_JOIN
When implementing a logical join, choose a specific alternative to use for the underlying join method. Learn more in Join methods.
HASH_JOIN_BUILD_SIDE BUILD_LEFT |
BUILD_RIGHT
Specifies which side of the hash join is used as the build side. Can only be used with JOIN_METHOD=HASH_JOIN
BATCH_MODE TRUE (default) |
FALSE
Used to disable batched apply join in favor of row-at-a-time apply join. Can only be used with JOIN_METHOD=APPLY_JOIN.

Join methods

Join methods are specific implementations of the various logical join types. Some join methods are available only for certain join types. The choice of which join method to use depends on the specifics of your query and of the data being queried. The best way to figure out if a particular join method helps with the performance of your query is to try the method and view the resulting query execution plan. See Query Execution Operators for more details.

Join Method Description Operands
HASH_JOIN The hash join operator builds a hash table out of one side (the build side), and probes in the hash table for all the elements in the other side (the probe side). Different variants are used for various join types. View the query execution plan for your query to see which variant is used. Read more about the Hash join operator.
APPLY_JOIN The apply join operator gets each item from one side (the input side), and evaluates the subquery on other side (the map side) using the values of the item from the input side. Different variants are used for various join types. Cross apply is used for inner join, and outer apply is used for left joins. Read more about the Cross apply and Outer apply operators.
MERGE_JOIN The merge join operator joins two streams of sorted data. The optimizer will add Sort operators to the plan if the data is not already providing the required sort property for the given join condition. The engine provides a distributed merge sort by default, which when coupled with merge join may allow for larger joins, potentially avoiding disk spilling and improving scale and latency. Different variants are used for various join types. View the query execution plan for your query to see which variant is used. Read more about the Merge join operator.
PUSH_BROADCAST_HASH_JOIN The push broadcast hash join operator builds a batch of data from the build side of the join. The batch is then sent in parallel to all the local splits of the probe side of the join. On each of the local servers, a hash join is executed between the batch and the local data. This join is most likely to be beneficial when the input can fit within one batch, but is not strict. Another potential area of benefit is when operations can be distributed to the local servers, such as an aggregation that occurs after a join. A push broadcast hash join can distribute some aggregation where a traditional hash join cannot. Different variants are used for various join types. View the query execution plan for your query to see which variant is used. Read more about the Push broadcast hash join operator.