Debug complex SQL query

Hi,

How can I debug a complex SQL query that has group by and joins. I am using pymapd to run SQL queries.

Thanks,
Dipanwita.

Hi @dipanwita2019,

I’m not sure what do you mean when you talk about debugging a SQL, query, but if you meant to see what the optimizer decided and the executor is doing you have to enable some parameter in the server and take a looks to the logs, but if you need just a logical calcite plan, you just need to add explain calcite, before the query.

Im’m using the omnisql command because using python the result wouldn’t be so readable

e.g.

omnisql> explain calcite select airport_state,avg(arrdelay),avg(depdelay),count(*) from flights join airports on origin=iata where dep_timestamp between '2005-06-01 00:00:00' and '2006-03-20 00:00:00' group by airport_state ;
Explanation
LogicalAggregate(group=[{0}], EXPR$1=[AVG($1)], EXPR$2=[AVG($2)], EXPR$3=[COUNT()])
  LogicalProject(airport_state=[$56], arrdelay=[$14], depdelay=[$15])
    LogicalFilter(condition=[AND(>=($29, CAST('2005-06-01 00:00:00'):TIMESTAMP(0) NOT NULL), <=($29, CAST('2006-03-20 00:00:00'):TIMESTAMP(0) NOT NULL))])
      LogicalJoin(condition=[=($16, $53)], joinType=[inner])
        EnumerableTableScan(table=[[omnisci, flights]])
        EnumerableTableScan(table=[[omnisci, airports]])

so the optimizer is doing the join before filter your data and finally is doing the group by;
you cant get the time that the software takes for the individual steps, because the query is an llvm program b itself and everything is inlined an run using tens of thousands of threads.

If you want more detailed info about how the joins or the group by are performed you can set to true the verbose parameter , and look to the omnisci_server.INFO logfile in the server

the join is an hash join that’s using a perfect hash algo, and the time spnt to build the hash table is 8 ms

2019-06-30T13:54:33.940425 1 22093 JoinHashTableInterface.cpp:504 Trying to build perfect hash table:
2019-06-30T13:54:33.940431 1 22093 JoinHashTable.cpp:331 Building perfect hash table OneToOne for qual: (= (CAST TEXT(0,0) NONE(0) (ColumnVar table: 9 column: 17 rte: 0 TEXT) ) (CAST TEXT(0,0) NONE(0) (ColumnVar table: 10 column: 1 rte: 1 TEXT) ) )
2019-06-30T13:54:33.949001 1 22093 JoinHashTable.cpp:422 Built perfect hash table OneToOne in 8 ms

the group by is also a perfect hash

Query Type: Perfect Hash
Keyless Hash: True, target index for key: 5
Effective key width: 8
Number of group columns: 1
Col Slot Context State
N | P , L
0 | 8 , 4
1 | 8 , 8
2 | 8 , 8
3 | 8 , 8
4 | 8 , 8
5 | 8 , 4
Allow Multifrag: True
Interleaved Bins on GPU: True
Blocks Share Memory: False
Threads Share Memory: True
Uses Fast Group Values: True
Lazy Init Groups (GPU): True
Entry Count: 54
Min Val (perfect hash only): 0
Max Val (perfect hash only): 52
Bucket Val (perfect hash only): 0
Sort on GPU: False
Use Streaming Top N: False
Output Columnar: False
Render Output: False
Use Baseline Sort: False

to have other infos about query execution you can turn on the debug timer with enable-debug-timer parameter; this is the summary output of the debug time

75ms total duration for sql_execute
  55ms start(19ms) executeRelAlgQuery RelAlgExecutor.cpp:158
    55ms start(19ms) executeRelAlgQueryNoRetry RelAlgExecutor.cpp:181
      0ms start(19ms) Query pre-execution steps RelAlgExecutor.cpp:182
      55ms start(19ms) executeRelAlgSeq RelAlgExecutor.cpp:424
        55ms start(19ms) executeRelAlgStep RelAlgExecutor.cpp:503
          55ms start(19ms) executeCompound RelAlgExecutor.cpp:1491
            55ms start(19ms) executeWorkUnit RelAlgExecutor.cpp:2574
              10ms start(19ms) compileWorkUnit NativeCodegen.cpp:1647
                8ms start(20ms) getInstance JoinHashTableInterface.cpp:481
                  8ms start(20ms) reify JoinHashTable.cpp:477
                      New thread(9)
                        4ms start(0ms) initOneToOneHashTable JoinHashTable.cpp:879
                          0ms start(0ms) initHashTableOnCpuFromCache JoinHashTable.cpp:1146
                          0ms start(0ms) initOneToOneHashTableOnCpu JoinHashTable.cpp:700
                      End thread(9)
                      New thread(10)
                        3ms start(0ms) initOneToOneHashTable JoinHashTable.cpp:879
                          0ms start(0ms) initHashTableOnCpuFromCache JoinHashTable.cpp:1146
                          0ms start(0ms) initOneToOneHashTableOnCpu JoinHashTable.cpp:700
                      End thread(10)
                New thread(11)
                  0ms start(0ms) fetchChunks Execute.cpp:2089
                  1ms start(0ms) getQueryExecutionContext QueryMemoryDescriptor.cpp:766
                  40ms start(1ms) executePlanWithGroupBy Execute.cpp:2629
                    10ms start(1ms) launchGpuCode QueryExecutionContext.cpp:195
                    0ms start(41ms) reduceMultiDeviceResultSets Execute.cpp:881
                End thread(11)
                New thread(12)
                  0ms start(0ms) fetchChunks Execute.cpp:2089
                  1ms start(0ms) getQueryExecutionContext QueryMemoryDescriptor.cpp:766
                  39ms start(1ms) executePlanWithGroupBy Execute.cpp:2629
                    10ms start(1ms) launchGpuCode QueryExecutionContext.cpp:195
                    0ms start(41ms) reduceMultiDeviceResultSets Execute.cpp:881
                End thread(12)
              0ms start(75ms) collectAllDeviceResults Execute.cpp:1572
                0ms start(75ms) reduceMultiDeviceResultSets Execute.cpp:881