DirectScan and fallback modes
=============================

DirectScan
----------

Postgres Lakehouse is fastest when it can “push down” your entire query
to DataFusion, the vectorized query used for handling queries when
possible. (In the future, you will have more fine-grained control as we
add support for partial pushdowns.)

Postgres Lakehouse can execute your query in two modes. First, it
attempts to run the entire query using Seafowl, a dedicated columnar
database based on DataFusion. If Seafowl can’t run the entire query, for
example, because it uses PostgreSQL-specific operations like JSON, then
Postgres Lakehouse falls back to using the PostgreSQL executor, with
Seafowl streaming full table contents to it.

If your query is extremely slow, it’s possible that’s what’s happening.

Using EXPLAIN to check the query plan
-------------------------------------

You can check which mode is being used by running ``EXPLAIN`` on the
query and making sure that the top-most query node is ``DirectScan`` .
For example:

::

   explain select count from (select count(*) from tpch_sf_1.lineitem);
                                                                                                                                  QUERY PLAN
   - -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Aggregate  (cost=167.52..167.55 rows=1 width=8)
      ->  Append  (cost=0.00..165.01 rows=1001 width=0)
            ->  CompatScan on "16529" lineitem_2  (cost=100.00..150.00 rows=1000 width=0)
                  SeafowlPlan: logical_plan
                    TableScan:  tpch_sf_1.lineitem projection=[l_orderkey, l_partkey, l_suppkey, l_linenumber, l_quantity, l_extendedprice, l_discount, l_tax, l_returnflag, l_linestatus, l_shipdate, l_commitdate, l_receiptdate, l_shipinstruct, l_shipmode, l_comment]
   (6 rows)

In this case, the query is executed by PostgreSQL, and Seafowl is
involved only when scanning the table (see ``CompatScan`` at the
bottom). The fix in this case is to explicitly name the inner
``COUNT(*)`` column, since Seafowl gives it an implicit name
``count(*)`` , whereas PostgreSQL calls it ``count`` :

.. code:: console

   edb_admin=> explain select count from (select count(*) as count from tpch_sf_1.lineitem);
                                QUERY PLAN
   - -------------------------------------------------------------------
    DirectScan: logical_plan
      Projection:  COUNT(*) AS count
        Aggregate:  groupBy=[[]], aggr=[[COUNT(UInt8(1)) AS COUNT(*)]]
          TableScan:  tpch_sf_1.lineitem projection=[]
   (4 rows)

Here, you can see the ``DirectScan`` at the top, which means that
Seafowl is running the entire query.

If you’re having trouble rewording your query to make it run fully on
Seafowl, open a support ticket.