RalfJL commented on issue #12949:
URL: https://github.com/apache/pinot/issues/12949#issuecomment-2079376295
This might have the same root cause and may be easier to debug.
I splitted the freeradius requests into 3 tables. radius_start,
radius_update, radius_stop which obviously contain the Start Stop and
Interim-Update records.
Now the query:
```
select a.acctuniquesessionid, a.acctstatustype, a.ts, b.acctstatustype,
b.ts, (cast(b.ts as BIGINT)-cast(a.ts as BIGINT))/1000 as Zeit from
radius_start a
join radius_stop b
on a.acctuniquesessionid = b.acctuniquesessionid
order by Zeit desc
```
returns 185096 records
The query having a where clause with a subselect:
```
select a.acctuniquesessionid, a.acctstatustype, a.ts, b.acctstatustype,
b.ts, (cast(b.ts as BIGINT)-cast(a.ts as BIGINT))/1000 as Zeit from
radius_start a
join radius_stop b
on a.acctuniquesessionid = b.acctuniquesessionid
where not exists ( select 1 from radius_start c where c.acctuniquesessionid
= a.acctuniquesessionid and c.ts < a.ts)
order by Zeit desc
```
returns 466790 records.
I would have expected less records with the filter than without, right?
And the query plan is interesting:
The first one:
```
Execution Plan
LogicalSort(sort0=[$5], dir0=[DESC])
PinotLogicalSortExchange(distribution=[hash], collation=[[5 DESC]],
isSortOnSender=[false], isSortOnReceiver=[true])
LogicalProject(acctuniquesessionid=[$1], acctstatustype=[$0], ts=[$2],
acctstatustype0=[$4], ts0=[$6], Zeit=[/(-($7, $3), 1000)])
LogicalJoin(condition=[=($1, $5)], joinType=[inner])
PinotLogicalExchange(distribution=[hash[1]])
LogicalProject(acctstatustype=[$12], acctuniquesessionid=[$14],
ts=[$18], EXPR$0=[CAST($18):BIGINT NOT NULL])
LogicalTableScan(table=[[radius_start]])
PinotLogicalExchange(distribution=[hash[1]])
LogicalProject(acctstatustype=[$12], acctuniquesessionid=[$14],
ts=[$18], EXPR$0=[CAST($18):BIGINT NOT NULL])
LogicalTableScan(table=[[radius_stop]])
```
pretty simple and stright forward.
The second one:
```
Execution Plan
LogicalSort(sort0=[$5], dir0=[DESC])
PinotLogicalSortExchange(distribution=[hash], collation=[[5 DESC]],
isSortOnSender=[false], isSortOnReceiver=[true])
LogicalProject(acctuniquesessionid=[$14], acctstatustype=[$12],
ts=[$18], acctstatustype0=[$31], ts0=[$37], Zeit=[/(-(CAST($37):BIGINT NOT
NULL, CAST($18):BIGINT NOT NULL), 1000)])
LogicalFilter(condition=[IS NULL($40)])
LogicalJoin(condition=[AND(=($14, $38), =($18, $39))],
joinType=[left])
PinotLogicalExchange(distribution=[hash[14, 18]])
LogicalJoin(condition=[=($14, $33)], joinType=[inner])
PinotLogicalExchange(distribution=[hash[14]])
LogicalTableScan(table=[[radius_start]])
PinotLogicalExchange(distribution=[hash[14]])
LogicalTableScan(table=[[radius_stop]])
PinotLogicalExchange(distribution=[hash[0, 1]])
LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
PinotLogicalExchange(distribution=[hash[0, 1]])
LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
LogicalProject(acctuniquesessionid0=[$2], ts0=[$3],
$f0=[true])
LogicalJoin(condition=[AND(=($0, $2), <($1, $3))],
joinType=[inner])
PinotLogicalExchange(distribution=[hash[0]])
LogicalProject(acctuniquesessionid=[$14], ts=[$18])
LogicalTableScan(table=[[radius_start]])
PinotLogicalExchange(distribution=[hash[0]])
LogicalAggregate(group=[{0, 1}])
PinotLogicalExchange(distribution=[hash[0, 1]])
LogicalAggregate(group=[{0, 1}])
LogicalJoin(condition=[=($0, $2)],
joinType=[inner])
PinotLogicalExchange(distribution=[hash[0]])
LogicalProject(acctuniquesessionid=[$14],
ts=[$18])
LogicalTableScan(table=[[radius_start]])
PinotLogicalExchange(distribution=[hash[0]])
LogicalProject(acctuniquesessionid=[$14])
LogicalTableScan(table=[[radius_stop]])
```
has 3 additional joins
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@pinot.apache.org
For additional commands, e-mail: commits-h...@pinot.apache.org
