Query planning

A query plan defines where each part of a SQL statement runs. It contains routes that send work to shards and, when needed, router operations that combine shard results.

How a statement becomes a plan

Incoming queries to a Neki database cluster are planned after being parsed at the router. The planner builds the plan from four inputs:

The SQL statement. The statement defines the tables, predicates, and operations that the plan must cover.
The database schema. The schema defines the tables, columns, types, and indexes that the statement refers to.
The data topology. The data topology defines which shards hold the rows of each table.
Statistics. Statistics collected from the shards give the planner row estimates to compare alternative plans. See Cost model, cardinality estimation, and statistics.

Query plan cache and protocols

The query protocol determines whether two statements can share a plan. With Postgres’s simple query protocol, Neki replaces eligible literal values with parameters before looking up a plan. user_id = 1337 and user_id = 999, for example, both become user_id = $1 and can share a plan. The value for $1 is bound at execution, when it can direct the route to a different shard.

Preparing a statement does not pin its plan. The router looks it up again using the current search_path and session role, and may rebuild it if the cached copy was evicted or invalidated by a schema, topology, or statistics change.

EXPLAIN in Neki

A Neki plan has two levels. At the router level, Neki routes work to shards and combines the results. At the shard level, each shard’s Postgres plans the SQL that Neki sends to it. The three EXPLAIN modes differ in which levels they show:

Mode	Shows
`EXPLAIN`	Only the shard level: a Postgres plan from one shard
`EXPLAIN (NEKI_PLAN)`	Only the router level: Neki’s routes and router operations
`EXPLAIN (NEKI_PG_PLAN)`	Both levels: Neki’s plan with the Postgres plan from each shard that a route involves

COSTS works in all three modes. COSTS OFF removes the cost estimates from Neki’s operators and from the shard-level Postgres plans. VERBOSE, BUFFERS, SETTINGS, WAL, and MEMORY have no effect on Neki’s own operators. EXPLAIN and EXPLAIN (NEKI_PG_PLAN) pass them as written to the shard-level Postgres EXPLAIN. EXPLAIN (NEKI_PLAN) accepts them and ignores them. All four output formats (TEXT, JSON, XML, and YAML) are supported in all three modes. The default format is TEXT. The examples in this section use the example schema, where public.orders belongs to the user_data shard group and uses user_id as its primary shard index.

`EXPLAIN`

A plain Postgres EXPLAIN works when the whole statement runs on the shards with no router operations. The plan is then a single route, which can also be a route that scatters to every shard in a group, because every shard runs the same SQL. Neki sends the EXPLAIN to one of the route’s shards and returns that shard’s output unchanged. The output is the plan of a real Postgres, with no Neki-specific content:

EXPLAIN
SELECT order_id
FROM public.orders
WHERE user_id = 1337;

Seq Scan on orders  (cost=0.00..2.00 rows=1 width=8)
  Filter: (user_id = 1337)

When the plan has router operations, Neki rejects the statement with SQLSTATE NK017:

ERROR:  This statement requires Neki router processing
HINT:  Use `EXPLAIN (NEKI_PG_PLAN)` (with ANALYZE to execute) to see Neki's plan

EXPLAIN ANALYZE executes the statement. Neki accepts it with a plain EXPLAIN only when the plan sends the whole statement to exactly one shard.

`EXPLAIN (NEKI_PLAN)`

EXPLAIN (NEKI_PLAN) shows Neki’s plan: the routes that send work to shards, and the router operations that combine their results. Without ANALYZE, it does not contact the shards. The output shows only router-level plan nodes. The route node is the boundary: it shows the SQL that runs on the shards, but not the shard-level plans. An equality predicate on orders.user_id produces one route:

EXPLAIN (NEKI_PLAN)
SELECT order_id
FROM public.orders
WHERE user_id = 1337;

Route [EqualUnique]  (cost=0.00..1100.00 rows=100 width=8)
  Query: SELECT order_id FROM public.orders WHERE user_id = $1
  ShardGroup: user_data
  Values: $1

The Query field uses a reusable parameterized form of the SQL, so the literal 1337 appears as $1. Values: $1 is the parameter the route resolves, and ShardGroup: user_data names the topology group that receives the route. Route [EqualUnique] is the single-shard routing choice. An IN predicate produces a route for the selected values:

EXPLAIN (NEKI_PLAN)
SELECT order_id
FROM public.orders
WHERE user_id IN (42, 999);

Collapse  (cost=0.00..2500.00 rows=500 width=8)
  ->  Route [IN]  (cost=0.00..2500.00 rows=500 width=8)
        Query: SELECT order_id FROM public.orders WHERE user_id = ANY($1)
        ShardGroup: user_data
        Values: $1

Route [IN] narrows the query to the shards holding the listed values. It has multi fanout. Collapse indicates where Neki turns those responses into one result. An aggregate without a usable shard-key predicate scatters across the group:

EXPLAIN (NEKI_PLAN)
SELECT count(*)
FROM public.orders;

Aggregate [Ordered]  (cost=0.00..2002.00 rows=1 width=8)
  ->  Collapse  (cost=0.00..2002.00 rows=2 width=8)
        ->  Route [Scatter]  (cost=0.00..2002.00 rows=2 width=8)
              Query: SELECT count(*) FROM public.orders
              ShardGroup: user_data

Route [Scatter] indicates this is a scatter-gather query, which requires contacting all shards in the shard group to complete the query. Each shard computes a partial count, and the router-side Aggregate adds the partial results before returning the final count. As a shard group grows, the number of shards used by scatter queries increases. This may be acceptable for occasional full-group aggregates and other whole-table operations, but scatter-gather execution of frequent queries should be avoided because it increases utilization of database and network resources. If you expect a query to be single-shard but are seeing Route [Scatter] in the query plan, check for a suitable shard-key predicate or consider using a global secondary index (GSI) lookup.

`EXPLAIN (NEKI_PG_PLAN)`

EXPLAIN (NEKI_PG_PLAN) shows Neki’s plan together with the Postgres plan from each shard that a route involves. A Postgres plan shows how a shard plans to run the SQL that Neki sends to it. The output shows both router-level and shard-level plan nodes, and a scatter route shows a plan for each shard in the group. Neki designs this mode for maximal compatibility with Postgres, so tools that read Postgres EXPLAIN output can also read Neki plans. Without ANALYZE, Neki does not execute the statement. For each route, Neki sends a plain Postgres EXPLAIN to every shard that the route involves:

Route	Shards that receive the `EXPLAIN`
Scatter	Every shard in the shard group
Equality or `IN`	The shards that hold the listed values
Any shard, such as a read from a reference table	Every candidate shard

Each route shows its shard plans under one of two labels. All shard plans ("ShardPlans": {"All": [...]} in the structured formats) means that every listed shard runs the route’s SQL. Any one shard plan ("Any") means that the route runs on one of the listed shards, selected arbitrarily. A scatter read from users in the example schema shows the plan of both shards in the group:

EXPLAIN (NEKI_PG_PLAN)
SELECT user_id, name
FROM users;

Collapse  (cost=0.00..3000.00 rows=1000 width=151)
  ->  Route [Scatter]  (cost=0.00..3000.00 rows=1000 width=151)
        Query: SELECT user_id, name FROM public.users
        ShardGroup: user_data
        All shard plans
        ->  Shard shard-a  (cost=0.00..0.00 rows=0 width=0)
              ->  Seq Scan on users  (cost=0.00..1.20 rows=20 width=15)
        ->  Shard shard-b  (cost=0.00..0.00 rows=0 width=0)
              ->  Seq Scan on users  (cost=0.00..1.30 rows=30 width=15)

When the whole plan is one route on one shard, Neki omits the route node. The output names the shard on the first line, followed by the shard’s Postgres plan, similar to the output of a plain EXPLAIN:

EXPLAIN (NEKI_PG_PLAN)
SELECT user_id, name
FROM users
WHERE user_id = 42;

Shard shard-a
  Seq Scan on users  (cost=0.00..1.25 rows=1 width=15)
    Filter: (user_id = 42)

Some routes take values that exist only after the statement starts to run, for example the inner side of a correlated subquery. Without ANALYZE, Neki rejects NEKI_PG_PLAN for such a plan. Use ANALYZE, or drop NEKI_PG_PLAN to see only Neki’s plan. With ANALYZE, Neki executes the statement. The output adds Neki’s actual row counts and timings, and per-shard statistics for each route. A route whose rows go directly to the client shows each shard’s EXPLAIN ANALYZE output. A route whose rows feed a router operation, such as one input of a join, shows a plain Postgres EXPLAIN from each shard that the route used. In the following example, the route’s rows feed the router-side Aggregate, so each shard shows a plain EXPLAIN with its statistics:

EXPLAIN (NEKI_PG_PLAN, ANALYZE)
SELECT sum(total_amount)
FROM orders
WHERE created_at > '2026-09-09';

Aggregate [Ordered]  (cost=0.00..2002.00 rows=1 width=32) (actual time=0.236..0.238 rows=1 loops=1)
  RowsTotal: 1
  CostAtActualRows: Total=2002 RemoteCalls=2 RowsProcessed=2 NetworkRows=2
  ->  Collapse  (cost=0.00..2002.00 rows=2 width=32) (actual time=0.217..0.220 rows=2 loops=1)
        RowsTotal: 2
        CostAtActualRows: Total=2002 RemoteCalls=2 NetworkRows=2
        ->  Route [Scatter]  (cost=0.00..2002.00 rows=2 width=32) (actual time=0.216..0.217 rows=2 loops=1)
              RowsTotal: 2
              RemoteCalls: 2
              NetworkBytesIn: 102
              AvgWidth: 51
              CostAtActualRows: Total=2002 RemoteCalls=2 NetworkRows=2
              Query: SELECT sum(total_amount) FROM public.orders WHERE created_at > $1::timestamp without time zone
              ShardGroup: user_data
              ->  Shard shard-a  (cost=0.00..0.00 rows=0 width=0)
                    Shard Stats: Executions=1 TimeMs=0.21 RowsOut=1 BytesIn=51
                    ->  Aggregate  (cost=2.45..2.46 rows=1 width=32)
                          ->  Seq Scan on orders  (cost=0.00..2.40 rows=18 width=5)
                                Filter: (created_at > ('2026-09-09'::cstring)::timestamp without time zone)
              ->  Shard shard-b  (cost=0.00..0.00 rows=0 width=0)
                    Shard Stats: Executions=1 TimeMs=0.215 RowsOut=1 BytesIn=51
                    ->  Aggregate  (cost=4.17..4.18 rows=1 width=32)
                          ->  Seq Scan on orders  (cost=0.00..4.10 rows=26 width=5)
                                Filter: (created_at > ('2026-09-09'::cstring)::timestamp without time zone)
Planning Time: 0.192 ms
Execution Time: 0.239 ms

ANALYZE executes the statement being explained. This is especially risky for data-changing statements: an INSERT, UPDATE, DELETE, or MERGE under ANALYZE changes rows on every shard that the plan reaches, and the changes stay.

EXPLAIN output reference shows the complete NEKI_PLAN and NEKI_PG_PLAN output of a query with router operations, in every format.

Cost model, cardinality estimation, and statistics

A perfect search over bad estimates is just a very efficient way to find the wrong plan.

Neki’s query planner uses statistics collected from the shards. The statistics must exist on the shards before Neki can collect them, and keeping them current is your responsibility. Postgres has many kinds of statistics. The Postgres documentation on planner statistics describes them. The planner estimates the number of rows that each part of a plan produces. These cardinality estimates come from the collected statistics. The cost model then uses the estimates to compare the alternative plans and choose the cheapest one. Neki collects the following statistics from the catalog of each shard’s Postgres. This list grows as Neki collects more kinds of statistics.

The estimated row count of each table.
The number of distinct values (n_distinct) and the fraction of NULL values (null_frac) of each column that is part of an index, or of a primary key, foreign key, or unique constraint.

A collector on each shard reads the catalog periodically and sends changes to the routers. A significant change invalidates the cached plans that depend on the changed statistics. Postgres writes statistics when ANALYZE, autoanalyze, VACUUM, or CREATE INDEX runs. A freshly loaded table has no statistics until one of these runs. Until then, the planner uses default estimates. ANALYZE and VACUUM sent through a router run on every shard. Data topology recommends a Postgres index on the shard-key columns, and Best practices recommends that you keep their statistics current. Indexes and current statistics give the planner the estimates it needs to find a good plan. An index also adds its columns to the set whose statistics Neki collects. EXPLAIN (NEKI_PG_PLAN) shows what the planner does with its inputs. Creating and dropping indexes, and running ANALYZE, change what the planner has to work with. Repeat the two steps to keep your queries fast:

Inspect the plan with EXPLAIN (NEKI_PG_PLAN).
Change the indexes or refresh the statistics, then inspect the plan again.

Insights helps in this process. Use Query Insights to observe the workload, and schema recommendations for DDL suggestions based on production telemetry.

Statement types

Neki plans read queries (SELECT) and data-changing statements (INSERT, UPDATE, DELETE, and MERGE) the standard way, which EXPLAIN in Neki describes. When a plan reaches several shards, Neki sends the shard requests in parallel and combines the responses into one Postgres result. Some results require more than concatenating rows from each shard. Neki can combine partial aggregates, apply ordering and limits across shards, or complete joins that cannot be pushed into the shards. Neki plans a statement the same way inside and outside an explicit transaction. The transaction limits only how the plan runs, which Transactions describes. The statement types in the following sections have their own planning.

DDL

Neki keeps the same schema on every shard, so data definition language (DDL) sent through a router runs on every managed shard. Multi-shard DDL is not atomic across the deployment. A failure can leave the change applied on some shards but not others. Managed schema-change workflows add readiness checks and coordinate completion through online or direct execution paths.

COPY

Neki plans COPY on its own path because the rows arrive as a data stream after the statement, not in the statement text. For COPY FROM into a sharded table, Neki routes each incoming row to a shard. COPY FROM can route rows into a sharded table when the input includes the columns needed by its primary shard indexes. It is rejected when that table has an enabled GSI because COPY does not maintain GSI lookup rows. Keep the GSI disabled during the copy, then backfill and verify the lookup table before enabling it. Client COPY TO STDOUT works for a sharded table, a reference table, and a materialized view, including binary. File COPY remains unsharded-only.

Cursors

Neki plans the query of a DECLARE statement the standard way, then builds the cursor from the plan:

When the plan is a single route, each shard that the route resolves to opens its own Postgres cursor.
When the plan has router operations, the router serves the cursor. It runs the plan and returns rows as FETCH statements ask for them.

Need help?

Get help from the PlanetScale Support team, or join our Discord community to see how others are using PlanetScale.

Architecture

Pricing

Platform

Guides

How a statement becomes a plan

Query plan cache and protocols

EXPLAIN in Neki

`EXPLAIN`

`EXPLAIN (NEKI_PLAN)`

`EXPLAIN (NEKI_PG_PLAN)`

Cost model, cardinality estimation, and statistics

Statement types

DDL

COPY

Cursors

Need help?

​How a statement becomes a plan

​Query plan cache and protocols

​EXPLAIN in Neki

​EXPLAIN

​EXPLAIN (NEKI_PLAN)

​EXPLAIN (NEKI_PG_PLAN)

​Cost model, cardinality estimation, and statistics

​Statement types

​DDL

​COPY

​Cursors

​Need help?

How a statement becomes a plan

Query plan cache and protocols

EXPLAIN in Neki

`EXPLAIN`

`EXPLAIN (NEKI_PLAN)`

`EXPLAIN (NEKI_PG_PLAN)`

Cost model, cardinality estimation, and statistics

Statement types

DDL

COPY

Cursors

Need help?