feat: `explain` or similar API for inspecting plans

[NickCrews]

Is your feature request related to a problem?

This is a bit broad, IDK if this should be a discussion.

I want to be able to profile queries. Specifically, which parts of the query are slow. This is tricky, because in ibis computation happens in two stages:

1. expression building: This is always fast, since we aren't actually processing any data. But this is where I want to be able to assign blame, ie the table.distinct(on="my_id") call is responsible for 90% of the query runtime. Using timeit or other python profiling is useless here.
2. runtime: This is where the big data is actually processed, and where the time actually happens.

I can currently do A/B testing, by just timing the whole run, but this is annoying to have to think about what to change, and then writing the change.

What is the motivation behind your request?

I am motived both as an end user, but also as a library author.

Describe the solution you'd like

Ideally every Operation could get associated with a run time, so for instance if you plot an expression graph you could see the runtime next to each node.

That seems very difficult though. Still very usable would be if we just got back the raw, non-machine readable output from eg EXPLAIN ANALYZE <QUERY>. This solution I don't think would even need to be implemented in ibis, but it would be ergonomic if it was a simple expr.profile() or ibis.profile(expr).

I'm not sure about backend support. I don't know, but I imagine most of the SQL backends support some sort of profiling. SQlite profiling looks very different from duckdb profiling.

What version of ibis are you running?

main

What backend(s) are you using, if any?

No response

Code of Conduct

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

[cpcloud]

Hey Nick!

This is a super interesting problem, but I think it's not feasible for us to correlate an ibis expression with a particular duration (or some proxy of that like instructions, or even database plan node) because the thing that the database executes often looks very different from the thing it was asked to execute.

It's one of the trade-offs associated with using SQL and/or a database: you generally get better performance at the cost of debugability.

That said, what do you think about a backend.explain(expr) method? We had this a while ago but it was removed due to lack of implementation across our backends.

This would at least allow you to get some insight into what the database thinks is happening.

[NickCrews]

what do you think about a backend.explain(expr) method?

I think that sounds nice, even if it returns teh raw string. However, to avoid needing to do a private method on a non-explicit-backend expression

e = ibis.read_csv().<sdfgdsfg>
e._find_backend().explain(e)

I would love to either (1 is my favorite)

1. have ibis.explain(expr) in addition to backend.explain(expr)
2. just have expr.explain() (though we don't want to fill up that namespace TOO much)
3. have expr.explain() in addition to backend.explain(expr)

Possible things to support (duckdb for inspiration)

• (need) show physical plan
• (need) show profiling/timing results
• (nice to have) show logical plan
• (nice to have) show optimized logical plan

[cboettig]

Would it be possible to support the duckdb explain mechanism (i.e. have an ibis.duckdb.explain() at least as an intermediate? Maybe this already exists (or there's some way to trigger this), but I'm only seeing a .explain method for the impala backend I think?

[dangotbanned]

My motivation is pretty different (typing), but I was hoping to find an ibis.Table.explain method.

Survey

In narwhals, the equivalent to ibis.Table in all other lazy backends we support has an explain method:

• polars.LazyFrame.explain
• duckdb.DuckDBPyRelation.explain
• pyspark.sql.DataFrame.explain
• sqlframe.base.dataframe.DataFrame.explain
• dask.dataframe.DataFrame.explain
• ibis.Table.explain 😔

Additionally, a user-requested backend daft:

• daft.DataFrame.explain

I thought this detail might be more relevant to ibis than my use-case

---

Background

Note

TL;DR: explain would help us avoid depending on ibis internals (for typing)

Downstream, I've been working on some typing which can be used for matching objects from optional dependencies (like ibis.Table) in @overload definitions:

Show related PRs

• fix(typing): Narrow NativeSeries Protocol narwhals-dev/narwhals#2159
• fix(typing): Narrow TypeVar(s) used in (Data|Lazy)Frame narwhals-dev/narwhals#2356
• feat: Adds private Namespace class narwhals-dev/narwhals#2324
• fix(typing): Avoid overlapping DataFrame, LazyFrame narwhals-dev/narwhals#2944
• feat(typing): Make Implementation less opaque narwhals-dev/narwhals#3016
• refactor(typing): Add _native.py narwhals-dev/narwhals#3086
• fix(typing): Overhaul @overloads in nw.from_native narwhals-dev/narwhals#3125

That's a lot of content 😅, but here's a more condensed repro of the original problem:

Show repro (problem)

from __future__ import annotations

from typing import TYPE_CHECKING, Any, Literal, overload

from typing_extensions import reveal_type

if TYPE_CHECKING:
    import collections

    import cudf
    import dask.dataframe as dd
    import ibis
    import pandas as pd
    import polars as pl
    import pyspark.sql
    from typing_extensions import TypeAlias, TypeIs

Known: TypeAlias = Literal["polars", "dask", "pandas", "ibis", "pyspark", "cudf"]

KNOWN_PACKAGES: tuple[Known, ...] = (
    "polars",
    "dask",
    "pandas",
    "ibis",
    "pyspark",
    "cudf",
)


def is_known(package_name: str) -> TypeIs[Known]:
    return package_name in KNOWN_PACKAGES


@overload
def package(native: pl.DataFrame) -> Literal["polars"]: ...
@overload
def package(native: pd.DataFrame) -> Literal["pandas"]: ...
@overload
def package(native: ibis.Table) -> Literal["ibis"]: ...  # mypy-only issue (1)
@overload
def package(native: cudf.DataFrame) -> Literal["cudf"]: ...  # not installed (1)
@overload
def package(native: dd.DataFrame) -> Literal["dask"]: ...  # mypy-only issue (2)
@overload
def package(native: pyspark.sql.DataFrame) -> Literal["pyspark"]: ...  # not installed (2)
@overload
def package(native: Any) -> Literal["unknown"]: ...


def package(native: Any) -> Literal[Known, "unknown"]:
    package_name = type(native).__module__.split(".")[0]
    if is_known(package_name):
        return package_name
    return "unknown"


def test_package(
    a: pl.DataFrame,
    b: pd.DataFrame,
    c: ibis.Table,
    d: cudf.DataFrame,
    e: dd.DataFrame,
    f: pyspark.sql.DataFrame,
    g: collections.deque[Any],
) -> None:
    r_polars = package(a)
    r_pandas = package(b)
    r_ibis = package(c)
    r_cudf = package(d)
    r_dask = package(e)
    r_pyspark = package(f)
    r_unknown = package(g)

    reveal_type(r_polars)
    reveal_type(r_pandas)
    reveal_type(r_ibis)
    reveal_type(r_cudf)
    reveal_type(r_dask)
    reveal_type(r_pyspark)
    reveal_type(r_unknown)

These outputs are fully-dependent on which packages are installed. In my case, everything but pyspark and cudf.
This alone is enough to break the typing for anything beyond the first @overload mentioning either package:

Show output pyright (problem)

pyright narwhals/t.py
narwhals/t.py
  narwhals/t.py:74:17 - information: Type of "r_polars" is "Literal['polars']"
  narwhals/t.py:75:17 - information: Type of "r_pandas" is "Literal['pandas']"
  narwhals/t.py:76:17 - information: Type of "r_ibis" is "Literal['ibis']"
  narwhals/t.py:77:17 - information: Type of "r_cudf" is "Unknown"
  narwhals/t.py:78:17 - information: Type of "r_dask" is "Literal['cudf']"
  narwhals/t.py:79:17 - information: Type of "r_pyspark" is "Unknown"
  narwhals/t.py:80:17 - information: Type of "r_unknown" is "Literal['cudf']"
0 errors, 0 warnings, 7 informations

mypy further complicates things, for the reasons discussed in (#6844):

Show output mypy (problem)

mypy narwhals/t.py
narwhals/t.py:41: error: Overloaded function signature 4 will never be matched: signature 3's parameter type(s) are the same or broader  [overload-cannot-match]
    def package(native: cudf.DataFrame) -> Literal["cudf"]: ...  # not installed (1)
    ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
narwhals/t.py:43: error: Overloaded function signature 5 will never be matched: signature 3's parameter type(s) are the same or broader  [overload-cannot-match]
    def package(native: dd.DataFrame) -> Literal["dask"]: ...  # mypy-only issue (2)
    ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
narwhals/t.py:43: error: Overloaded function signature 5 will never be matched: signature 4's parameter type(s) are the same or broader  [overload-cannot-match]
    def package(native: dd.DataFrame) -> Literal["dask"]: ...  # mypy-only issue (2)
    ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
narwhals/t.py:45: error: Overloaded function signature 6 will never be matched: signature 3's parameter type(s) are the same or broader  [overload-cannot-match]
    def package(native: pyspark.sql.DataFrame) -> Literal["pyspark"]: ...  # not installed (2)
    ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
narwhals/t.py:45: error: Overloaded function signature 6 will never be matched: signature 4's parameter type(s) are the same or broader  [overload-cannot-match]
    def package(native: pyspark.sql.DataFrame) -> Literal["pyspark"]: ...  # not installed (2)
    ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
narwhals/t.py:45: error: Overloaded function signature 6 will never be matched: signature 5's parameter type(s) are the same or broader  [overload-cannot-match]
    def package(native: pyspark.sql.DataFrame) -> Literal["pyspark"]: ...  # not installed (2)
    ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
narwhals/t.py:47: error: Overloaded function signature 7 will never be matched: signature 3's parameter type(s) are the same or broader  [overload-cannot-match]
    def package(native: Any) -> Literal["unknown"]: ...
    ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
narwhals/t.py:47: error: Overloaded function signature 7 will never be matched: signature 4's parameter type(s) are the same or broader  [overload-cannot-match]
    def package(native: Any) -> Literal["unknown"]: ...
    ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
narwhals/t.py:47: error: Overloaded function signature 7 will never be matched: signature 5's parameter type(s) are the same or broader  [overload-cannot-match]
    def package(native: Any) -> Literal["unknown"]: ...
    ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
narwhals/t.py:47: error: Overloaded function signature 7 will never be matched: signature 6's parameter type(s) are the same or broader  [overload-cannot-match]
    def package(native: Any) -> Literal["unknown"]: ...
    ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
narwhals/t.py:74: note: Revealed type is "Literal['polars']"
narwhals/t.py:75: note: Revealed type is "Literal['pandas']"
narwhals/t.py:76: note: Revealed type is "Any"
narwhals/t.py:77: note: Revealed type is "Any"
narwhals/t.py:78: note: Revealed type is "Any"
narwhals/t.py:79: note: Revealed type is "Any"
narwhals/t.py:80: note: Revealed type is "Literal['ibis']"
Found 10 errors in 1 file (checked 1 source file)

Current solution

Everything besides ibis.Table can match to NativeLazyFrame due to the presence of explain.
Then defining a special protocol for ibis.Table and using a Union w/ what works for the others.

The positive cases here work fine:

Show repro (solution)

from __future__ import annotations

from typing import TYPE_CHECKING

from typing_extensions import reveal_type

import narwhals as nw

if TYPE_CHECKING:
    import ibis
    import pandas as pd
    import polars as pl

    from narwhals.typing import IntoLazyFrameT


def from_lazy(native: IntoLazyFrameT) -> nw.LazyFrame[IntoLazyFrameT]:
    return nw.from_native(native)


def example(good_1: ibis.Table, good_2: pl.LazyFrame, bad: pd.DataFrame) -> None:
    r_good_1 = from_lazy(good_1)
    r_good_2 = from_lazy(good_2)
    r_bad = from_lazy(bad)

    reveal_type(r_good_1)
    reveal_type(r_good_2)
    reveal_type(r_bad)

But the negative cases (e.g. pd.DataFrame isn't lazy) leak this workaround out into the diagnostic messages:

Show output pyright (solution)

pyright narwhals/t.py
narwhals/t.py
  narwhals/t.py:24:23 - error: Argument of type "DataFrame" cannot be assigned to parameter "native" of type "IntoLazyFrameT@from_lazy" in function "from_lazy"
    Type "DataFrame" is not assignable to type "IntoLazyFrame"
      Type "DataFrame" is not assignable to type "IntoLazyFrame"
        "DataFrame" is incompatible with protocol "NativeLazyFrame"
          "explain" is not present
            "NativeFrame" is not assignable to "DataFrame"
        "DataFrame" is incompatible with protocol "_NativeIbis"
          "sql" is not present
          "__pyarrow_result__" is not present
    ... (reportArgumentType)
  narwhals/t.py:26:17 - information: Type of "r_good_1" is "LazyFrame[Table]"
  narwhals/t.py:27:17 - information: Type of "r_good_2" is "narwhals.dataframe.LazyFrame[polars.lazyframe.frame.LazyFrame]"
  narwhals/t.py:28:17 - information: Type of "r_bad" is "LazyFrame[Unknown]"
1 error, 0 warnings, 3 informations