A query sent to a Neki router has two plans: the router's plan, which decides which shards to call and how to combine their results, and the Postgres plan each shard runs. This guide shows which EXPLAIN form returns which, uses EXPLAIN (NEKI_PG_PLAN) to read every shard's plan for a scatter query, and uses those plans to find and fix a missing index.
What you will build
EXPLAIN (NEKI_PLAN) -> router plan: Route, Collapse, Aggregate
EXPLAIN (NEKI_PLAN, ANALYZE) -> router plan with RemoteCalls, NetworkRows, one Shard node per shard
EXPLAIN -> the shard's Postgres plan, single-shard queries only
EXPLAIN (NEKI_PG_PLAN) -> router plan plus "All shard plans": each shard's Postgres plan
AI Prompt
Using the PlanetScale CLI (pscale 0.344.0) and psql, read the Postgres plan each Neki shard runs for a query sent through the router.
Requirements:
- Create the Neki database with --engine neki, --cluster-size PS-10-AWS-ARM-NEKI and --replicas 2, add two shards, and wait until every shard answers a direct SELECT 1 with SET __neki.shard. Use one database role for every step.
- EXPLAIN (NEKI_PLAN) shows the router's plan. Add ANALYZE to execute and see RemoteCalls, NetworkRows and one Shard node per shard called.
- Plain EXPLAIN returns the shard's Postgres plan only when the statement goes to one shard. For a multi-shard statement it fails with a hint to use EXPLAIN (NEKI_PG_PLAN).
- EXPLAIN (NEKI_PG_PLAN) prints the router plan and an "All shard plans" section with each shard's Postgres plan, labelled with the shard uid and name. Add ANALYZE for actual rows and buffers.
- Run the verification step below before finishing.
Prerequisites
A PlanetScale organization with access to the Neki Platform Preview. Create an account if you do not have one, and see Neki availability and access for how an organization administrator joins the preview.
pscale0.344.0 with a service token that can create databases,psql18.6,jq, andpython3:
export PLANETSCALE_SERVICE_TOKEN_ID="<token id>"
export PLANETSCALE_SERVICE_TOKEN="<token>"
export PLANETSCALE_ORG="<organization name>"
pscale version
- A Neki database with three shards and an
order_itemstable with 150,000 rows, sharded bycustomer_id. Create them with these commands:
pscale database create plansdb --engine neki --org "$PLANETSCALE_ORG" \
--region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait
pscale role create plansdb main admin --inherited-roles postgres --org "$PLANETSCALE_ORG" --format json > role.json
export PGHOST=$(jq -r .access_host_url role.json) PGUSER=$(jq -r .username role.json) \
PGPASSWORD=$(jq -r .password role.json) PGPORT=5432 PGDATABASE=postgres PGSSLMODE=require
rm role.json
until psql -At -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done
pscale branch shard create plansdb main --config-profile default --count 2 --org "$PLANETSCALE_ORG"
until [ "$(psql -At -c 'SELECT count(*) FROM __neki.list_shards()')" = "3" ]; do sleep 10; done
for u in $(psql -At -c "SELECT uid FROM __neki.list_shards()"); do
until psql -At -c "SET __neki.shard='$u';" -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done
done
psql -q -v ON_ERROR_STOP=1 \
-c "CREATE TABLE order_items (customer_id bigint NOT NULL, order_id bigint NOT NULL, line_no int NOT NULL, sku text NOT NULL, PRIMARY KEY (customer_id, order_id, line_no))" \
2>&1 | grep -v NOTICE
SHARD_1=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh1'")
SHARD_2=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh2'")
SHARD_3=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh3'")
cat > data-topology.json <<JSON
{
"shard_indexes": { "xxhash_customer_id": { "type": "xxhash", "columns": ["customer_id"] } },
"shard_groups": [
{ "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
{ "uid": "by_customer", "default_shard_index": "xxhash_customer_id",
"key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
],
"databases": { "postgres": { "schemas": { "public": { "tables": {
"order_items": { "shard_group": "by_customer" }
} } } } },
"default_shard_group": "$SHARD_1",
"authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update plansdb main --org "$PLANETSCALE_ORG" --format json < data-topology.json > /dev/null
sleep 10
python3 -c "
for g in range(1, 50001):
for l in range(1, 4): print(f'{g % 500 + 1},{g},{l},sku{(g * 7 + l) % 2000}')" > items.csv
psql -c "\copy order_items FROM 'items.csv' WITH (FORMAT csv)"
psql -At -c "ANALYZE order_items" 2>&1 | grep -v NOTICE
The COPY prints COPY 150000.
Note: Output samples are trimmed to the relevant lines. Each shard bills as a three-node cluster.
PS-10-AWS-ARM-NEKIlists at $30 per month per shard, prorated to the millisecond. Current rates are on the Neki pricing page. Delete the database when you finish withpscale database delete plansdb --force --org "$PLANETSCALE_ORG".
1. Read the router's plan
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT count(*) FROM order_items WHERE customer_id = 42"
psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) SELECT count(*) FROM order_items WHERE sku = 'sku42'"
Real output:
Route [EqualUnique]
Query: SELECT count(*) FROM public.order_items WHERE customer_id = $1
ShardGroup: by_customer
Values: $1
Aggregate [Ordered]
-> Collapse
-> Route [Scatter]
Query: SELECT count(*) FROM public.order_items WHERE sku = $1
ShardGroup: by_customer
The first query goes to one shard. The second has no shard key, so it scatters to both data shards, and the router adds the per-shard counts.
2. Ask a single shard for its plan
psql -At -c "EXPLAIN SELECT * FROM order_items WHERE customer_id = 42 AND sku = 'sku42'"
psql -At -c "EXPLAIN SELECT count(*) FROM order_items WHERE sku = 'sku42'" 2>&1 | head -2
Real output:
Bitmap Heap Scan on order_items (cost=5.97..257.97 rows=1 width=27)
Recheck Cond: (customer_id = 42)
Filter: (sku = 'sku42'::text)
-> Bitmap Index Scan on order_items_pkey (cost=0.00..5.97 rows=300 width=0)
Index Cond: (customer_id = 42)
ERROR: This statement requires Neki router processing
HINT: Use `EXPLAIN (NEKI_PG_PLAN)` (with ANALYZE to execute) to see Neki's plan
Plain EXPLAIN passes a single-shard statement through and returns that shard's Postgres plan. A scatter statement has no single Postgres plan, so the router refuses and names the option to use.
3. Read every shard's plan
psql -At -c "EXPLAIN (NEKI_PG_PLAN) SELECT count(*) FROM order_items WHERE sku = 'sku42'"
Real output:
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.order_items WHERE sku = $1
ShardGroup: by_customer
All shard plans
-> Shard sh4xkzh52dh14d (sh2)
-> Aggregate (cost=1479.85..1479.86 rows=1 width=8)
-> Seq Scan on order_items (cost=0.00..1479.75 rows=40 width=0)
Filter: (sku = 'sku42'::text)
-> Shard shfue0ggqbv1nb (sh3)
-> Aggregate (cost=1547.35..1547.36 rows=1 width=8)
-> Seq Scan on order_items (cost=0.00..1547.25 rows=41 width=0)
Filter: (sku = 'sku42'::text)
Each shard runs a sequential scan over its share of the table, because no index covers sku.
4. Measure what the shards did
psql -At -c "EXPLAIN (NEKI_PLAN, ANALYZE) SELECT count(*) FROM order_items WHERE sku = 'sku42'" | grep -E "Route|Shard |RemoteCalls:|NetworkRows"
psql -At -c "EXPLAIN (NEKI_PG_PLAN, ANALYZE) SELECT count(*) FROM order_items WHERE sku = 'sku42'" | grep -E "Shard |Scan|Rows Removed|Execution Time"
Real output:
CostAtActualRows: Total=2002 RemoteCalls=2 RowsProcessed=2 NetworkRows=2
CostAtActualRows: Total=2002 RemoteCalls=2 NetworkRows=2
-> Route [Scatter] (cost=0.00..2002.00 rows=2 width=8) (actual time=7.516..10.087 rows=2 loops=1)
RemoteCalls: 2
CostAtActualRows: Total=2002 RemoteCalls=2 NetworkRows=2
-> Shard sh4xkzh52dh14d (actual time=10.053..10.100 rows=1.00 loops=1)
-> Shard shfue0ggqbv1nb (actual time=7.450..7.527 rows=1.00 loops=1)
-> Shard sh4xkzh52dh14d (sh2) (actual time=7.361..7.422 rows=1.00 loops=1)
-> Seq Scan on order_items (cost=0.00..1479.75 rows=40 width=0) (actual time=0.116..6.307 rows=25.00 loops=1)
Rows Removed by Filter: 72275
Execution Time: 6.341 ms
-> Shard shfue0ggqbv1nb (sh3) (actual time=7.529..7.557 rows=1.00 loops=1)
-> Seq Scan on order_items (cost=0.00..1547.25 rows=41 width=0) (actual time=0.087..6.702 rows=50.00 loops=1)
Rows Removed by Filter: 77650
Execution Time: 6.741 ms
Execution Time: 7.604 ms
NEKI_PLAN, ANALYZE shows the router's side: two remote calls and their timings. NEKI_PG_PLAN, ANALYZE shows each shard's side: the sequential scans threw away 72,275 and 77,650 rows to find 25 and 50 matches.
5. Add the index and read the plans again
psql -At -c "CREATE INDEX order_items_sku_idx ON order_items (sku)" 2>&1 | grep -v NOTICE
psql -At -c "EXPLAIN (NEKI_PG_PLAN, ANALYZE) SELECT count(*) FROM order_items WHERE sku = 'sku42'" | grep -E "Shard |Scan|Execution Time"
Real output:
CREATE INDEX
-> Shard sh4xkzh52dh14d (sh2) (actual time=3.312..3.366 rows=1.00 loops=1)
-> Bitmap Heap Scan on order_items (cost=1.70..44.09 rows=40 width=0) (actual time=0.021..0.041 rows=25.00 loops=1)
-> Bitmap Index Scan on order_items_sku_idx (cost=0.00..1.69 rows=40 width=0) (actual time=0.012..0.012 rows=25.00 loops=1)
Execution Time: 0.066 ms
-> Shard shfue0ggqbv1nb (sh3) (actual time=3.458..3.481 rows=1.00 loops=1)
-> Bitmap Heap Scan on order_items (cost=1.71..45.17 rows=41 width=0) (actual time=0.026..0.091 rows=50.00 loops=1)
-> Bitmap Index Scan on order_items_sku_idx (cost=0.00..1.70 rows=41 width=0) (actual time=0.014..0.014 rows=50.00 loops=1)
Execution Time: 0.123 ms
Execution Time: 3.548 ms
Both shards now use order_items_sku_idx, and each shard's execution time fell from about 6.5 milliseconds to under 0.2 milliseconds.
Verify the result
psql -At -c "EXPLAIN (NEKI_PG_PLAN) SELECT count(*) FROM order_items WHERE sku = 'sku42'" | grep -c "order_items_sku_idx"
psql -At -c "SELECT count(*) FROM order_items WHERE sku = 'sku42'"
Expected: 2 or more (the index appears in every data shard's plan), then the same count the query returned before the index (75 in this run).
How it works
The router parses each statement, works out which shards it must reach from the shard key and the data topology, sends each shard a query, and combines the results. NEKI_PLAN describes that routing layer. Each shard is an ordinary Postgres server that plans the query it receives with its own statistics and indexes, and NEKI_PG_PLAN collects those plans so you can tune the shards the way you would tune a single Postgres server. A slow scatter query is usually slow on every shard, so a fix that shows up in one shard's plan normally applies to all of them. The router is described on the Neki architecture page.
Common issues
Generating test data with INSERT ... SELECT
Loading these 150,000 rows with one INSERT ... SELECT over generate_series failed with memory budget exceeded: account "insert_input" requested 65536 bytes, monitor "query" already holds 6684672 bytes, limit is 6710886 bytes, because the router builds the rows before sending them to the shards. COPY from a file, as in the prerequisites, has no such limit.
Unsupported EXPLAIN options
EXPLAIN (NEKI_PLAN, SHARD_PLANS) and EXPLAIN (NEKI_PLAN, REMOTE) fail with not implemented: [32] unsupported EXPLAIN option. Use NEKI_PG_PLAN for shard plans.
EXPLAIN on a session pinned to one shard
With SET __neki.shard in effect, EXPLAIN (NEKI_PLAN) fails with describes the router's plan over every shard the statement targets, which a session with __neki.shard set cannot answer. Plain EXPLAIN on that session returns the pinned shard's plan.
The ANALYZE join strategy differs from EXPLAIN
For a join across shard groups, EXPLAIN (NEKI_PLAN) showed Join [Hash] while EXPLAIN (NEKI_PLAN, ANALYZE) of the same query ran Join [BatchedNestedLoop Inner]. Read the ANALYZE form when the join strategy matters.
Next steps
- Delete the database with
pscale database delete plansdb --force --org "$PLANETSCALE_ORG". - Read the Neki architecture page for how routers and shards fit together.
verification:
status: verified
tested_at: "2026-10-28"
product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
command: "EXPLAIN (NEKI_PG_PLAN) SELECT count(*) FROM order_items WHERE sku = 'sku42'"
expected_result: "Every data shard's plan uses order_items_sku_idx, and the count is unchanged."