I wonder if some of the other pushdowns are negatively affecting the speed. That's really the only difference here.
> On Feb 1, 2022, at 10:36 AM, Daniel Clark <[email protected]> wrote: > > Hi James, > > The amended query runs, however, over 2x slower than Drill 1.19. Thanks. > > > On Tue, Feb 1, 2022 at 4:24 AM James Turton <[email protected]> wrote: > >> If you feel like playing around you can try out this variation. >> >> SELECT `Elements_Efforts`.`EffortTypeName` AS `EffortTypeName`, >> `Elements`.`ElementSubTypeName` AS `ElementSubTypeName`, >> `Elements`.`ElementTypeName` AS `ElementTypeName`, >> `Elements`.`PlanID` AS `PlanID` >> FROM `mongo.grounds`.`Elements` `Elements` >> INNER JOIN `mongo.grounds`.`Elements_Efforts` `Elements_Efforts` ON >> convert_to(`Elements`.`_id`, >> 'JSON') = convert_to(`Elements_Efforts`.`_id`, 'JSON') >> WHERE (`Elements`.`PlanID` = '1623263140') >> GROUP BY `Elements_Efforts`.`EffortTypeName`, >> `Elements`.`ElementSubTypeName`, >> `Elements`.`ElementTypeName`, >> `Elements`.`PlanID` >> >> >> On 2022/02/01 11:08, James Turton wrote: >> >> I think luoc is exactly right in his diagnosis. So Daniel, does this >> query run fine on Drill 1.19 with store.mongo.all_text_mode = true? >> >> Thanks >> James >> >> On 2022/02/01 05:39, luoc wrote: >> >> Hi Daniel, >> I should guess the reason for this case, but I need to verify it first. >> Basically, under the full text, the ObjectId exists as a key-value pair >> (Map structure), not as a character or number value. >> >> On Jan 31, 2022, at 23:45, Daniel Clark<[email protected]> >> <[email protected]> wrote: >> >> >> Here it is. Please see the attached file. >> >> On Mon, Jan 31, 2022 at 4:22 AM James Turton<[email protected]> >> <[email protected]> wrote: >> Please also attach the query profile if you can. >> >> Thanks >> James >> >> >> >>
