add_count_estimate_function.exs

defmodule Repo.Migrations.AddCountEstimateFunction do
  use Ecto.Migration

  def up do
    create_function()
  end

  def down do
    drop_function()
  end

  defp create_function() do
    execute """
    CREATE FUNCTION count_estimate(query text) RETURNS INTEGER AS
    $$
    DECLARE
        rec   record;
        ROWS  INTEGER;
    BEGIN
        FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
            ROWS := SUBSTRING(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
            EXIT WHEN ROWS IS NOT NULL;
        END LOOP;

        RETURN ROWS;
    END
    $$
    LANGUAGE plpgsql;
    """
  end

  defp drop_function() do
    execute "DROP FUNCTION count_estimate(query text);"
  end
end

repo.ex

defmodule Repo do
...
  def count_estimate(query) do
    {iodata, params} = to_sql(:all, query)

    %{rows: [[estimate]]} =
      query!(IO.iodata_to_binary(["select count_estimate('#{iodata}')"]), params)

    # I think maybe better UX since should only be used for "large numbers"
    # and many ridiculous queries with 0 present return estimte of 1
    if estimate == 1, do: 0, else: estimate
  end
...
end