How SQL/PGQ Rewrites to Joins on PostgreSQL 19

Graph Queries Are Just Joins Under the Hood

When you write GRAPH_TABLE syntax in PostgreSQL 19, the database doesn't execute your query as a graph at all. Instead, it rewrites the graph pattern into ordinary joins against the underlying tables, then hands the result to the standard query planner. The practical upshot: graph query plans look exactly like join plans, performance follows from your index choices, and EXPLAIN shows you the full join tree with nothing hidden behind graph-specific plan nodes.

The numbers tell the story. On a synthetic dataset of 10,000 vertices and roughly 60,000 random directed edges, a single-hop forward traversal costs almost nothing — three index-only scans. But reverse traversal, looking for who points at a given vertex, triggers a full sequential scan over all 60,000 edges unless you add an index on the destination column. That's the difference between 3.1 ms and 0.16 ms, and between 287 and 29 buffer hits.

The Rewrite in Action

Consider the simplest graph query: find every directed knows edge. The plan reads top-down as a scan of the big_knows edge table, joined to big_person on the source column, then joined again to a second alias of big_person on the destination column. This is precisely the join tree you'd write by hand:

GRAPH_TABLE is a parser-level rewriter. After parsing, your graph query becomes a regular SELECT with joins inferred from your CREATE PROPERTY GRAPH statement. The shape grows linearly: a two-hop pattern yields a five-way join, three hops yield seven. The planner then applies the same cost model, statistics, and join-order selection it uses for every other query.

Traversal Direction Dictates Your Index Strategy

The primary key on big_knows is (a, b). That gives you an index on the source column a but not on the destination column b. Forward traversal — "who does person 42 know?" — hits the primary key perfectly:

The plan shows three index-only scans, no sequential scan. Reverse traversal — "who knows person 42?" — looks up by b, which has no index. The result is a sequential scan through 60,000 rows to find just six matches. Your primary key indexed the source side well, but incoming edges need a destination-side index.

Adding the missing index changes everything:

The same query drops from 3.1 ms to 0.16 ms, and buffer hits fall from 287 to 29 — roughly 20× faster with one-tenth the I/O. PostgreSQL picks up the new index automatically. No configuration changes, no hints, no special syntax. The rule of thumb matches what you already know for foreign-key columns: if you'll filter on it, index it. Graph traversal references edge-table columns. Index the destination side if you plan reverse traversal.

Two-Hop Patterns Plan as Nested Loops

Counting two-hop neighbors of person 42 produces 38 paths in about 0.2 ms. The plan is a nested-loop expansion: each hop is an index-only scan keyed on a, joined to a primary-key lookup on person. The planner chose this strategy because the statistics say each vertex has only about six outbound edges on average — small nested loops dominate at this scale.

What PostgreSQL 19 Doesn't Do Yet

The first release covers a useful slice of the SQL/PGQ standard, but several gaps remain:

• ANY SHORTEST and ALL SHORTEST are not implemented. A WITH RECURSIVE query with ORDER BY depth and LIMIT 1 serves as a workaround.
• Multiple comma-separated patterns in one MATCH clause are unsupported. Join two GRAPH_TABLE invocations on their projected vertex IDs instead.
• Edge isomorphism isn't enforced by default. Add WHERE a.id <> b.id or WHERE e1 <> e2 to avoid matching the same edge twice.
• Path variable bindings like p = (a)->(b) aren't available. Project endpoint IDs out of COLUMNS instead.

Variable-length patterns and shortest path queries are the most noticeable omissions. For reachability over one to three hops, the WITH RECURSIVE workaround handles the job:

It works correctly, though it lacks the elegance of the quantified pattern syntax that should arrive eventually.

Operational Impact Is Minimal

Because SQL/PGQ compiles to ordinary joins, your operational story doesn't change. Backups, replication, monitoring, and EXPLAIN all work exactly as they do for join-heavy SQL workloads. Property graphs replicate as catalog rows; the underlying data stays in the tables you already have. Performance diagnosis follows the established pattern: spot an unexpected sequential scan, add the missing index, re-check.

CREATE PROPERTY GRAPH is a metadata-only operation. You can overlay a graph definition on existing tables without any data migration, which makes experimentation straightforward on current schemas.

For fixed-shape patterns with multiple hops or mixed vertex and edge types — friend-of-friend queries, coworker lookups, organizational hierarchies — GRAPH_TABLE reads clearly and plans predictably. Pure aggregations that don't traverse anything work fine in either form. The missing features, quantified patterns, shortest path, multi-pattern MATCH, all compose with WITH RECURSIVE and JOIN, so existing techniques bridge the gaps.

Trying It On Your Own Data

Pick a many-to-many relationship in your schema, add a CREATE PROPERTY GRAPH overlay, and rewrite one multi-join query as GRAPH_TABLE. Compare the EXPLAIN output for both forms. The plans should be near-identical, with differences usually limited to the extra vertex-table joins the graph form introduces for type checking. Parent-child trees work well as a starting point — walking one or two hops up or down exercises the basic syntax without much setup.