Acceleration is disabled. No substitutions

dacopan · October 10, 2024, 2:18am

Hello friends, please I need your help, we has a strange case that reflection are not used whern run this query:

SELECT ID_PAGE,
                  MAX(TOTAL_FOLLOWERS) aa
           FROM lake.prod.basics
           WHERE "ID_PAGE" IN
               (SELECT ID_PAGE
                FROM lake.prod.spaces
                WHERE ID_SPACE = '66540a999f94cf328c8bf693' )
                group by ID_PAGE

I already check profile, logs, and all I can posibble check but I cannot find the cause.

Reflection definition

Profile:
67524106-a6c2-4e8a-b8da-b2e2376de06a.zip (34,9 KB)

DEBUG log of acceleration (as mentioned here Dremio 23 not match any reflections with MINIOS3 - #3 by Benny_Chow)

2024-10-10 02:13:02,960 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Eliminate extra starflake tables
2024-10-10 02:13:02,960 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Generated newMapping: [-1, 0, -1, -1, 1, -1, -1, 2, -1], reverseMapping: [1, 4, 7] for rel#60704392:LogicalAggregate.NONE.ANY([]).[](input=HepRelVertex#60704391,group={1, 4},max-created_date=MAX($4),count-created_date=COUNT($4),max-total_followers=MAX($7),count-total_followers=COUNT($7),agg-sum0-0=$SUM0($8),agg-count1-0=COUNT($8))
2024-10-10 02:13:02,960 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - NewBottomProjectWithLiteralOne: [$1, $4, $7, 1]
2024-10-10 02:13:02,960 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - NewAggregate: [$0, $1], [MAX($1), MAX($2), COUNT($2), COUNT($3)]
2024-10-10 02:13:02,960 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - NewTopProject: [$0, $1, $2, CASE(IS NULL($1), 0:BIGINT, $5), $3, $4, $5, $5]
2024-10-10 02:13:02,960 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Transforming to LogicalProject#60704396
2024-10-10 02:13:02,960 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,960 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,960 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,961 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Reflection d5a48910-ccaa-42c5-a2e7-045b6e4fbbf1.b026c19e-effb-4a63-8229-34872732211f
Normalized:
LogicalProject(ID_PAGE=[$0], CREATED_DATE=[$1], max-created_date=[$3], max-total_followers=[$4], count-total_followers=[$5], CONVERT_COUNT_STAR=[$6])
  LogicalAggregate(group=[{0, 1, 4}], max-created_date=[MAX($1)], max-total_followers=[MAX($2)], count-total_followers=[COUNT($2)], CONVERT_COUNT_STAR=[COUNT($3)])
    LogicalProject(ID_PAGE=[$1], CREATED_DATE=[$4], TOTAL_FOLLOWERS=[$7], $f3=[1], $_dremio_$_dummy_$=[null:NULL])
      ScanCrel(table=[lake.prod.basics], snapshot=[3553615674448220692], columns=[`ID`, `ID_PAGE`, `RED`, `RED_NAME`, `CREATED_DATE`, `USERNAME`, `NAME`, `TOTAL_FOLLOWERS`], splits=[1])

stripped
2024-10-10 02:13:02,961 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - RelNode - appendedTable:
LogicalProject(ID_PAGE=[$0], CREATED_DATE=[$1], max-created_date=[$2], count-created_date=[CASE(IS NULL($1), 0:BIGINT, $5)], max-total_followers=[$3], count-total_followers=[$4], agg-sum0-0=[$5], agg-count1-0=[$5])
  LogicalAggregate(group=[{0, 1}], max-created_date=[MAX($2)], max-total_followers=[MAX($3)], count-total_followers=[$SUM0($4)], CONVERT_COUNT_STAR=[$SUM0($5)])
    LogicalProject(ID_PAGE=[$0], CREATED_DATE=[$1], max-created_date=[$2], max-total_followers=[$3], count-total_followers=[CAST($4):BIGINT NOT NULL], CONVERT_COUNT_STAR=[CAST($5):BIGINT NOT NULL])
      ScanCrel(table=["__accelerator"."d5a48910-ccaa-42c5-a2e7-045b6e4fbbf1"."b026c19e-effb-4a63-8229-34872732211f"], snapshot=[2934187645415682489], columns=[`ID_PAGE`, `CREATED_DATE`, `max-created_date`, `max-total_followers`, `count-total_followers`, `CONVERT_COUNT_STAR`], splits=[1])

2024-10-10 02:13:02,961 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - 1 unexpanded materializations
2024-10-10 02:13:02,961 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Finding expansion replacements for 1 materializations
2024-10-10 02:13:02,961 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Found 0 expansion nodes
2024-10-10 02:13:02,961 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Referenced expansions: []
2024-10-10 02:13:02,961 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,961 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Normalized target:
LogicalProject(ID_PAGE=[$0], CREATED_DATE=[$1], max-created_date=[$3], max-total_followers=[$4], count-total_followers=[$5], CONVERT_COUNT_STAR=[$6])
  LogicalAggregate(group=[{0, 1, 4}], max-created_date=[MAX($1)], max-total_followers=[MAX($2)], count-total_followers=[COUNT($2)], CONVERT_COUNT_STAR=[COUNT($3)])
    LogicalProject(ID_PAGE=[$1], CREATED_DATE=[$4], TOTAL_FOLLOWERS=[$7], $f3=[1], $_dremio_$_dummy_$=[null:NULL])
      ScanCrel(table=[lake.prod.basics], snapshot=[3553615674448220692], columns=[`ID`, `ID_PAGE`, `RED`, `RED_NAME`, `CREATED_DATE`, `USERNAME`, `NAME`, `TOTAL_FOLLOWERS`], splits=[1])

2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.n.rules.ConvertToCountOne - Did not find any aggregate calls that are equivalent to count(1)
2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Finding bup replacements
2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Reporting normalized materialization targets
2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Reporting materialization: d5a48910-ccaa-42c5-a2e7-045b6e4fbbf1.b026c19e-effb-4a63-8229-34872732211f
2024-10-10 02:13:02,962 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Prepare stream to generateSubtreeMatches method
2024-10-10 02:13:02,963 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Initial user query variation:
LogicalProject(ID_PAGE=[$0], aa=[$1])
  LogicalAggregate(group=[{0}], aa=[MAX($1)])
    LogicalProject(ID_PAGE=[$1], TOTAL_FOLLOWERS=[$7])
      LogicalJoin(condition=[=($1, $8)], joinType=[inner])
        ScanCrel(table=[lake.prod.basics], snapshot=[3553615674448220692], columns=[`ID`, `ID_PAGE`, `RED`, `RED_NAME`, `CREATED_DATE`, `USERNAME`, `NAME`, `TOTAL_FOLLOWERS`], splits=[1])
        LogicalAggregate(group=[{0}])
          LogicalProject(ID_PAGE=[$1])
            LogicalFilter(condition=[=($0, '66540a999f94cf328c8bf693')])
              ScanCrel(table=[lake.prod.spaces], snapshot=[5027336214320522386], columns=[`ID_SPACE`, `ID_PAGE`, `RED`], splits=[1])

2024-10-10 02:13:02,963 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Processed query variation:
LogicalAggregate(group=[{0}], aa=[MAX($1)])
  LogicalProject(ID_PAGE=[$1], TOTAL_FOLLOWERS=[$7])
    LogicalJoin(condition=[=($1, $8)], joinType=[inner])
      ScanCrel(table=[lake.prod.basics], snapshot=[3553615674448220692], columns=[`ID`, `ID_PAGE`, `RED`, `RED_NAME`, `CREATED_DATE`, `USERNAME`, `NAME`, `TOTAL_FOLLOWERS`], splits=[1])
      LogicalAggregate(group=[{0}])
        LogicalProject(ID_PAGE=[$1])
          LogicalFilter(condition=[=($0, '66540a999f94cf328c8bf693')])
            ScanCrel(table=[lake.prod.spaces], snapshot=[5027336214320522386], columns=[`ID_SPACE`, `ID_PAGE`, `RED`], splits=[1])

2024-10-10 02:13:02,964 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Initial user query variation:
LogicalProject(ID_PAGE=[$0], aa=[$1])
  LogicalAggregate(group=[{0}], aa=[MAX($1)])
    LogicalProject(ID_PAGE=[$0], TOTAL_FOLLOWERS=[$1])
      LogicalJoin(condition=[=($0, $2)], joinType=[inner])
        ScanCrel(table=[lake.prod.basics], snapshot=[3553615674448220692], columns=[`ID_PAGE`, `TOTAL_FOLLOWERS`], splits=[1])
        LogicalAggregate(group=[{0}])
          LogicalProject(ID_PAGE=[$1])
            LogicalFilter(condition=[=($0, '66540a999f94cf328c8bf693')])
              ScanCrel(table=[lake.prod.spaces], snapshot=[5027336214320522386], columns=[`ID_SPACE`, `ID_PAGE`], splits=[1])

2024-10-10 02:13:02,964 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.DremioSubstitutionProvider - Processed query variation:
LogicalAggregate(group=[{0}], aa=[MAX($1)])
  LogicalProject(ID_PAGE=[$0], TOTAL_FOLLOWERS=[$1])
    LogicalJoin(condition=[=($0, $2)], joinType=[inner])
      ScanCrel(table=[lake.prod.basics], snapshot=[3553615674448220692], columns=[`ID_PAGE`, `TOTAL_FOLLOWERS`], splits=[1])
      LogicalAggregate(group=[{0}])
        LogicalProject(ID_PAGE=[$1])
          LogicalFilter(condition=[=($0, '66540a999f94cf328c8bf693')])
            ScanCrel(table=[lake.prod.spaces], snapshot=[5027336214320522386], columns=[`ID_SPACE`, `ID_PAGE`], splits=[1])

2024-10-10 02:13:02,973 [18f8c7d0-a2e2-d4b8-7d28-39c469c2d900/0:foreman-planning] DEBUG c.d.e.p.a.s.AccelerationAwareSubstitutionProvider - Acceleration is disabled. No substitutions...

balaji.ramaswamy · October 10, 2024, 4:20am

@dacopan The reflection is not matched, I see you have a FILTER on ID_SPACE but not selected as a dimension?

dacopan · October 10, 2024, 4:22am

Hello thank by your reply, the reflection are in basics table that is filter on id_page

balaji.ramaswamy · October 10, 2024, 4:30am

Sorry @dacopan missed that, What happens if you run the below 2 queries?

SELECT ID_PAGE, MAX(TOTAL_FOLLOWERS) aa
FROM lake.prod.basics b
WHERE "ID_PAGE" = <direct-value>
GROUP BY 2

SELECT b.ID_PAGE, MAX(TOTAL_FOLLOWERS) aa
FROM lake.prod.basics b INNER JOIN lake.prod.spaces s ON b.ID_PAGE = s.ID_PAGE
WHERE ID_SPACE = '66540a999f94cf328c8bf693'
group by b.ID_PAGE

dacopan · October 11, 2024, 3:29am

hello, in first case reflection was used
d78c23b8-44fe-4b52-9e94-91b3903d4777.zip (21,6 KB)

In second case, reflection not used

4385ff10-a8ec-47bd-b250-df281510e7dd.zip (35,2 KB)

balaji.ramaswamy · October 16, 2024, 5:53am

@dacopan Query is accelerated on my localrepro, let me see if this is specific to CE edition

dacopan · October 19, 2024, 11:17am

Hello, thank by your help, the error was caused because a colleague was ran this config

alter system set accelerator.enable_multijoin = FALSE

balaji.ramaswamy · October 23, 2024, 3:53pm

Thanks for the update @dacopan

Topic		Replies	Views
Raw reflection not used to accelerate select star query	10	1639	February 15, 2023
Query was NOT accelerated - Reflections Not Used	2	812	February 2, 2023
Query was NOT accelerated	14	1758	January 12, 2020
Reflection not working Dremio University	26	4084	July 1, 2020
Can't create any reflections - The option 'reflections.planning.exclude.file_based_incremental.iceberg.accelerations' does not exist	22	53	September 3, 2024

Acceleration is disabled. No substitutions

Related Topics