Is this a bug? A trivial filter is 100x slower after a positional join.

[cottrell]

Example of the issue. I can create some full reproduction script later if needed.

This is some kind of data_raw and pos_join_enriched, parquet files with exactly the same structure. The names of the files are the same. The rows are aligned. These datasets are loaded via pyarrow then con.register. And then a data_enriched via the positional_join query as shown below.

The filter query is quite efficient. The cik column is an ordered in so I think Zonemaps implicitly are used.

However the same query on the positional joined data is 100x slower. I would think it should be at most 2x slower.

In [13]: %time _ = con.query("select * from data_raw where cik = 746631").df()
CPU times: user 158 ms, sys: 102 ms, total: 260 ms
Wall time: 48 ms

In [14]: %time _ = con.query("select * from data_enriched where cik = 746631").df()
100% ▕████████████████████████████████████████████████████████████▏ 
CPU times: user 25.9 s, sys: 2.96 s, total: 28.8 s
Wall time: 4.5 s

In [15]: print(con.query("explain select * from data_raw where cik = 746631").df().explain_value[0])
┌───────────────────────────┐
│        ARROW_SCAN         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            end            │
│            val            │
│             fy            │
│             fp            │
│            form           │
│           filed           │
│          taxonomy         │
│            item           │
│           units           │
│            cik            │
│           start           │
│     __index_level_0__     │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│Filters: cik=746631 AND cik│
│         IS NOT NULL       │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│           EC: 1           │
└───────────────────────────┘                             


In [16]: print(con.query("explain select * from data_enriched where cik = 746631").df().explain_value[0])
┌───────────────────────────┐                             
│           FILTER          │                             
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │                             
│       (cik = 746631)      │                             
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │                             
│           EC: 1           │                             
└─────────────┬─────────────┘                                                          
┌─────────────┴─────────────┐                             
│      POSITIONAL_SCAN      ├──────────────┐              
└─────────────┬─────────────┘              │                                           
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│        ARROW_SCAN         ││        ARROW_SCAN         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            end            ││            name           │
│            val            ││          tickers          │
│             fy            ││         entityName        │
│             fp            ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│            form           ││           EC: 1           │
│           filed           ││                           │
│          taxonomy         ││                           │
│            item           ││                           │
│           units           ││                           │
│            cik            ││                           │
│           start           ││                           │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││                           │
│           EC: 1           ││                           │
└───────────────────────────┘└───────────────────────────┘                             


In [17]: con.query("select * from sqlite_master where name = 'data_enriched'")
Out[17]: 
┌─────────┬───────────────┬───────────────┬──────────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│  type   │     name      │   tbl_name    │ rootpage │                                                                  sql                                                                  │
│ varchar │    varchar    │    varchar    │  int32   │                                                                varchar                                                                │
├─────────┼───────────────┼───────────────┼──────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ view    │ data_enriched │ data_enriched │        0 │ CREATE VIEW data_enriched AS SELECT A.* EXCLUDE (__index_level_0__), B.* FROM data_raw AS A POSITIONAL JOIN pos_join_enriched AS B;\n │
└─────────┴───────────────┴───────────────┴──────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘