More detailed query plans, part 2 (textual output)

Posted by Dimitry to the Firebird Developer List

Getting back to this subject, I'd like to discuss possible textual formats for the structured query plans.

Below is a sample query used to demonstrate the idea (taken from the TPC-R suite):

select first 10
l_orderkey,
sum(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate,
o_shippriority
from
customer,
orders,
lineitem
where
c_mktsegment = 'BUILDING'
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < date '1995-03-15'
and l_shipdate > date '1995-03-15'
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
2 desc,
o_orderdate;

This is a detailed plan output that could be shown for this query. It's based on the current binary access path format:

SELECT
-> FIRST N
-> SORT
-> AGGREGATE
-> SORT
-> LOOP JOIN (INNER)
-> FILTER
-> TABLE [ORDERS] ACCESS BY DBKEY
-> BITMAP
-> INDEX [ORDERS_ORDERDATE] SCAN
-> FILTER
-> TABLE [CUSTOMER] ACCESS BY DBKEY
-> BITMAP
-> INDEX [CUSTOMER_PK] SCAN
-> FILTER
-> TABLE [LINEITEM] ACCESS BY DBKEY
-> BITMAP
-> INDEX [LINEITEM_PK] SCAN

In fact, it's the real output which works in my private tree for a couple of months already, but I don't insist on the representation (inspired by Oracle and PGSQL), so feel free to criticize.

As soon as we have the new binary access path format (discussed in part 1) implemented, the output could look like this (just an example):

SELECT
[cost: 360000, rows: 10]
-> FIRST N (100)
[cost: 360000, rows: 10]
-> SORT ( DESC, o_orderdate ASC)
[cost: 360000, rows: 100]
-> AGGREGATE (SUM)
[cost: 350000, rows: 100]
-> SORT (l_orderkey ASC, o_orderdate ASC, o_shippriority ASC)
[cost: 300000, rows: 75000]
-> LOOP JOIN (INNER)
[cost: 150100, rows: 75000]
-> FILTER (o_orderdate < date '1995-03-15')
[cost: 75050, rows: 75000]
-> TABLE [ORDERS] ACCESS BY DBKEY
[cost: 75050, rows: 75000]
-> BITMAP
[cost: 50]
-> INDEX [ORDERS_ORDERDATE] RANGE SCAN
[cost: 50, used segments: 1]
etc

Another question I'd like to raise is the API to get the textual plan representation. fb_info_sql_access_path is expected to return the binary access path. We could have another tag e.g. fb_info_sql_access_path_as_text which works similar the current isc_info_sql_plan, i.e. performs the transformation on the server.

Another option could be to follow the fb_interpret() way and offer a client-side (actually, Y-valve) API call which would perform the binary-to-text conversion. The latter approach may look unreliable in the case of client/server version mismatch, but the worst possible thing for the client would be to get a reduced plan with unknown items printed as e.g. .

Comments?