Originally from the User Slack
@Bhardwaj_Thummar: Hey guys, I am getting “Query 1 ERROR at Line 1: : clustering-key cartesian product size 236 is greater than maximum 100” from scylladb when using below query, my scylladb server version is 5.4.3-0.20240211.cf42ca0c2a65
I have read scylla - Scylladb : clustering key cartesian product size 600 is greater than maximum 100 - Stack Overflow and applied max_clustering_key_restrictions_per_query in scylla.yaml although I could not find the same flag in the scylla.yaml.example.
What are your thoughts ?
Thanks in Advance !!!
select zpid from forsaleproperty where property_type IN ('single-family home','apartment','condo','coops','townhouse','multi-family','mobile/manufactured','lot/land','other') AND state = 'New York' AND city = 'New York' AND neighborhood IN ('','Washington Heights','Inwood','Hamilton Heights','Highbridge','Harlem','Upper East Side','Upper West Side','East Harlem','Sutton Place','Roosevelt Island','Astoria','Battery Park','Financial District','Chelsea','Hell''s Kitchen','Hudson Yards','Flatiron District','Turtle Bay','Kips Bay','Murray Hill','Midtown','Midtown South','Greenwich Village','West Village','SoHo','Gramercy','East Village','Greenpoint','Long Island City','Sunnyside','Lower East Side','Nolita','Tribeca','Civic Center','Little Italy','Chinatown','Brooklyn Heights','DUMBO','Fort Greene','Downtown','Williamsburg','Bedford-Stuyvesant','Bushwick','Clinton Hill','Edenwald','Woodlawn','Wakefield','Riverdale','Van Cortlandt Park','Kingsbridge','University Heights','Fordham','Bedford Park','Norwood','Marble Hill','Morningside Heights','West Harlem','Concourse','Williamsbridge','Laconia','Bronxwood','Pelham Gardens','Baychester','East Tremont','Crotona Park East','Morrisania','Tremont','Belmont','Morris Heights','Soundview','Van Nest','Parkchester','Pelham Parkway','Morris Park','Castle Hill','Westchester Village','Schuyerville','Throggs Neck','City Island','Country Club','Pelham Bay','Eastchester','Pelham Bay Park','Jackson Heights','College Point','Woodstock','Melrose','East Elmhurst','Mott Haven','Hunts Point','Longwood','Whitestone','Flushing','Bayside','Auburndale','Douglaston','Woodside','Elmhurst','Maspeth','Corona','North Corona','Forest Hills','Rego Park','Middle Village','Ridgewood','Glendale','Richmond Hill','Woodhaven','Forest Park','Kew Gardens','Kew Gardens Hills','Fresh Meadows','Oakland Gardens','Queens Village','Jamaica Estates','Briarwood','Jamaica','Jamaica Hills','Hollis','St. Albans','Bellerose','Glen Oaks','Little Neck','Floral park','Cambria Heights','New Dorp Beach','Port Richmond','Westerleigh','New Dorp','Grant City','Mariner''s Harbor','Castleton Corners','West Brighton','Lighthouse Hill','Graniteville','New Springville','Meiers Corners','Oakwood','Arlington','Todt Hill','Greenridge','Silver Lake','Richmond Town','Arden Heights','Bulls Head','Willowbrook','New Brighton','Travis','Dongan Hills','Great Kills','Egbertsville','Elm Park','Bay Terrace','Manor Heights','St. George','Bay Ridge','Sunset Park','Tompkinsville','Dyker Heights','Red Hook','Stapleton','Grymes Hill','Boerum Hill','Park Slope','Greenwood','Gowanus','Columbia Street Waterfront District','Prospect Heights','Cobble Hill','Carroll Gardens','Crown Heights','Wingate','Brownsville','Prospect Lefferts Gardens','Kensington','Windsor Terrace','Borough Park','Midwood','Prospect Park South','East Flatbush','Flatbush','Ditmas Park','South Beach','Bath Beach','Rosebank','Midland Beach','Clifton','Park Hill','Arrochar','Shore Acres','Grasmere','Emerson Hill','Gravesend','Bensonhurst','Marine Park','Flatlands','Sheepshead Bay','Gerritsen Beach','Coney Island','Seagate','Brighton Beach','Manhattan Beach','Tottenville','Eltingville','Prince''s Bay','Huguenot','Rossville','Annadale','Charleston','Pleasant Plains','Woodrow','Richmond Valley','East New York','Ozone Park','Howard Beach','South Ozone Park','South Richmond Hill','Highland Park','Canarsie','Bergen Beach','Brookville','Springfield Gardens','South Jamaica','Laurelton','Old Mill Basin','Mill Basin','Belle Harbor','Neponsit','Rockaway Park','Far Rockaway','Rockaway Beach','Broad Channel','Arverne','Rosedale','Central Park South') limit 1000000;
@Bhardwaj_Thummar: The possible solutions i have looked up is
• use multiple smaller queries
• make better database schema
max_clustering_key_restrictions_per_query: 5000
max_partition_key_restrictions_per_query: 5000
set above flags in scylla.yaml, its working though not sure its recommended.
@avi: It’s not recommended, but can be used while you work on a better solution
@Bhardwaj_Thummar: Thanks for the reply @avi.
so restricting the search by 3 partition keys (table has 4 partition keys) and not restrict this 4th key (which has lots of params for IN option) with allow filtering enabled, is better than using below flags maxed beyond recommendation?
max_clustering_key_restrictions_per_query: 5000
max_partition_key_restrictions_per_query: 5000
CREATE TABLE IF NOT EXISTS ForSaleProperty (
zpid text,
img text,
url text,
price float,
address text,
street text,
city text,
state text,
zipcode text,
beds float,
baths float,
area float,
lat float,
lng float,
availability_date text,
carousel_photos list<text>,
tracking map<text, text>,
property_type text,
neighborhood text,
year_built text,
created_at timestamp,
hoa_fee text,
pool text,
parking text,
PRIMARY KEY ((state, city, property_type, neighborhood), created_at, zpid)
) WITH CLUSTERING ORDER BY (created_at DESC);
here is the table schema for reference
@avi: Better to use individual queries for each partition key combination