Hello! It would help making group by column an affinity key. Then you can use collocated=true setting to make this faster.
Regards, -- Ilya Kasnacheev вт, 4 июн. 2019 г. в 15:15, David <[email protected]>: > Hi all, > > I need to analyze a performance bottleneck in a complex environment. > So I decided to split down each component and split down the query to most > simple level. > > *scenario:* > - only 1 table "person" with 2 columns > id (long), CURRENTCITYID(long) (both indexed) > 10_000_000 rows > > *Task: * > - make a group by of one indexed field. > select CURRENTCITYID, count(CURRENTCITYID) from > "worldPerson_AFF_CCID".person group by CURRENTCITYID; > > Result: > CURRENTCITYID; COUNT(CURRENTCITYID) > 1; 3000 > 2; 4000 > ... > 12,799 rows selected (14.986 seconds) > > Issue: a group by within a single table is very slow > > Question: how to analyse and find bottleneck? any performance tuning hints? > > Currently using 1 server/1 node > Also did same test with 10 servers/ 10 nodes, which imporved the > performance, but I would need 16 servers to reach the speed of one PG > server > > So I would like to understand better how ignite works for group > opperations. > And what can be done on tuning side? > > > 0: jdbc:ignite:thin://127.0.0.1/> explain select CURRENTCITYID, > count(CURRENTCITYID) from "worldPerson_AFF_CCID".person group by > CURRENTCITYID; > +--------------------------------+ > | PLAN | > +--------------------------------+ > | SELECT > __Z0.CURRENTCITYID AS __C0_0, > COUNT(__Z0.CURRENTCITYID) AS __C0_1 > FROM "worldPerson_AFF_CCID".PERSON __Z0 > /* "worldPerson_AFF_CCID".PERSON_CURRENTCITYID_IDX */ > GROUP BY __Z0.CURRE | > | SELECT > __C0_0 AS CURRENTCITYID, > CAST(SUM(__C0_1) AS BIGINT) AS __C0_1 > FROM PUBLIC.__T0 > /* PUBLIC."merge_scan" */ > GROUP BY __C0_0 | > +--------------------------------+ > 2 rows selected (0.008 seconds) > > > > > -- > Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >
