Skip to content

Cannot calculate buckets for pk = 1::int, a common pattern in pgproto

In pgproto, parameter types must be specified, either by the client or via SQL using CAST. However, using casts can prevent bucket calculation for a query:

picodata> explain UPDATE pgbench_accounts SET abalance = cast(2 as int) WHERE aid = cast(1 as int);
---
- - update "pgbench_accounts"
  - '"abalance" = "col_0"'
  - '    motion [policy: local]'
  - '        projection (2::unsigned::int -> "col_0", "pgbench_accounts"."aid"::integer
    -> "col_1")'
  - '            selection ROW("pgbench_accounts"."aid"::integer) = ROW(1::unsigned::int)'
  - '                scan "pgbench_accounts"'
  - 'execution options:'
  - '    vdbe_max_steps = 45000'
  - '    vtable_max_rows = 5000'
  - buckets = [1-3000]
...

picodata> explain UPDATE pgbench_accounts SET abalance = 2 WHERE aid = 1;
---
- - update "pgbench_accounts"
  - '"abalance" = "col_0"'
  - '    motion [policy: local]'
  - '        projection (2::unsigned -> "col_0", "pgbench_accounts"."aid"::integer
    -> "col_1")'
  - '            selection ROW("pgbench_accounts"."aid"::integer) = ROW(1::unsigned)'
  - '                scan "pgbench_accounts"'
  - 'execution options:'
  - '    vdbe_max_steps = 45000'
  - '    vtable_max_rows = 5000'
  - buckets = [1934]
...
Edited by Maksim Kaitmazian
To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information