Hi All, I'm testing starrocks 4.0 version shared data cluster, I tried creating partitioned table like

create table default_catalog.gate.society_resident_parties_mv partition by (society_id) as 
SELECT 
    p.id,
    p.name,
    p.type,
    p.status as p_status,
    p.email,
    p.phone_raw,
    s.society_id,
    s.apartment_id,
    s.relation,
    s.sub_relation,
    s.status as s_status
FROM <http://gate.party|gate.party> AS p
INNER JOIN gate.apartment_society_person AS s
    ON p.id = s.person_id where p.type = 'PERSON';

this is taking lot of time and not even completing even after 30mins, joined result has 75lakh records with 16k distinct societies. If I run without partition, it is getting completed in a minute. I tried the same way with async materialized view as well, it is also not syncing(taking lot of time) couldn't see full output in MV. Is there something I am doing wrong and anything to consider?

why partition by society_id?

Thats because, party table is too large and most queries will want data for a specific society

Could this potentially result in generating a large number of partitions? @Raghu Koratagere

Yes @jeff.ding -- This could be up to 50-60k partitions. But if we don't do that, and have only an index on society_id, performance would be sub optimal

maybe you should use distributed by society_id rather than partition by society_id, just like this

CREATE TABLE default_catalog.gate.society_resident_parties_mv
  DISTRIBUTED BY HASH(society_id) BUCKETS 32
  AS
  SELECT
      p.id,
      p.name,
      p.type,
      p.status as p_status,
      p.email,
      p.phone_raw,
      s.society_id,
      s.apartment_id,
      s.relation,
      s.sub_relation,
      s.status as s_status
  FROM <http://gate.party|gate.party> AS p
  INNER JOIN gate.apartment_society_person AS s
      ON p.id = s.person_id
  WHERE p.type = 'PERSON';

@Mohan — Pls check this option