Primary key lookups don't use indexes due to CAST operations

[benonions]

Code of Conduct

• I agree to follow this project's Code of Conduct

AI Policy

• I agree to follow this project's AI Policy, or I agree that AI was not used while creating this issue.

Versions

ash_sqlite 0.2.14

Operating system

Mac OS

Current Behavior

I think this is ultimately a similar issue to this one in ash_mysql where CAST operations are being done for every field comparison, meaning SQLite is unable to use any indexes.

I'd love to be helpful and make a PR, but I don't quite have the chops with this language yet, currently working on my first ash project.

Reproduction

Because I don't want to share any of my private application code, here's a contrived example with a 'user' resource to try to demonstrate what I'm observing.

  use Ash.Resource,
    otp_app: :ash_sqlite_performance_demo,
    domain: AshSqlitePerformanceDemo.Accounts,
    data_layer: AshSqlite.DataLayer

  sqlite do
    table "users"
    repo AshSqlitePerformanceDemo.Repo
  end

  actions do
    defaults [:read, :destroy]

    create :create do
      accept [:name, :email, :age]
    end

    update :update do
      accept [:name, :email, :age]
    end
  end

  attributes do
    uuid_primary_key :id

    attribute :name, :string do
      allow_nil? false
    end

    attribute :email, :string do
      allow_nil? false
    end

    attribute :age, :integer

    create_timestamp :created_at
    update_timestamp :updated_at
  end

  
  identities do
    identity :unique_email, [:email]
  end
end

and a snippet from a benchmark script where I compared using against ecto.

    IO.puts("Finding user by ID: #{user_id}")

    # Ecto version 
    {ecto_time, ecto_result} = :timer.tc(fn ->
      Repo.get(EctoUser, user_id)
    end)

    # Ash version     
    {ash_time, ash_result} = :timer.tc(fn ->
      Ash.get!(User, user_id)
    end)

    print_benchmark_results("Get by ID", ecto_time, ash_time, ecto_result != nil, ash_result != nil)
  end

results with 10,000 records in the users table:

=== AshSqlite Performance Benchmark ===

Sample user: User (e459392e-56ef-4106-aa88-079af4cfbdf6)
Total users in database: 10000

=== Test 1: Get User by ID ===
Finding user by ID: e459392e-56ef-4106-aa88-079af4cfbdf6
  Ecto:     3.13ms (result: true)
  AshSqlite: 71.82ms (result: true)

and the query generated by ecto and ash_sqlite

--- Ecto Query (Clean SQL) ---
[debug] QUERY OK source="users" db=0.1ms idle=24.6ms
SELECT u0."id", u0."name", u0."email", u0."age", u0."created_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = ?) ["0000d71e-7d92-480b-911f-4766d2af06ef"]
↳ :elixir_compiler_2.__FILE__/1, at: show_sql.exs:29

--- Ash Query (With CAST operations) ---
[debug] QUERY OK source="users" db=0.6ms idle=72.6ms
SELECT u0."id", u0."name", u0."age", u0."created_at", u0."email", u0."updated_at" FROM "users" AS u0 WHERE (CAST(u0."id" AS TEXT) = CAST(? AS TEXT)) LIMIT ? ["0000d71e-7d92-480b-911f-4766d2af06ef", 2]

I think the CAST operations affect all WHERE clauses, I have a project with a little over a million rows seeded in my development database and am seeing performance consistent with SQLite being forced to do full table scans instead of indexes.

Expected Behavior

To not perform casting on primary keys, ideally not on foreign keys either.

[zachdaniel]

Interesting. We probably need to be much more defensive in the way that we cast types in sqlite in that case. i.e for certain primitive types when being asked to cast them, we should just not if they already match the type. PRs welcome to adjust the type casting logic to do that! There is a Ash.Type.matches_type? callback that can likely be used to this end.