Hi, "1=1" is normal thing in the plan provided that index is used (see "/* PropertyCache.PROPERTY_CITY_ID_IDX: CITY_ID = 59053 */"). Is it possible to attach a reproducer?
Vladimir. On Wed, Jun 14, 2017 at 5:10 AM, djardine <[email protected]> wrote: > Hi, > I'm attempting to run a query that does a join between 2 large tables (one > with 6m rows, another with 80m rows). In the query plan I see a join "on > 1=1" and separately i see filters for my join under the "where" clause. I'm > not sure if this is standard output in the query plan or if it's doing a > ridiculously expensive join where it's combining every possible permutation > between the tables and then filtering it down. The query basically runs > forever, never returning and eventually will kill the server node that it's > running on (maybe OOM?). I've tried this on both PARTITIONED and REPLICATED > clusters. "property_id" fields are indexed. > > The query is: > > SELECT p.property_id, sum(cm.days_r)::real/(sum(cm.days_r)+sum(cm.days_a)) > as occ_rate, p.bedrooms, cm.period, p.room_type > FROM calendar_metric as cm > JOIN "PropertyCache".property as p on > p.property_id = cm.property_id > WHERE > cm.days_r > 0 AND p.bedrooms is not null AND ( > p.room_type = 'Entire > home/apt' ) > AND cm.period BETWEEN '2016-1-1' and '2016-8-1' > AND p.city_id = 59053 > GROUP BY cm.period, p.room_type, p.bedrooms, p.property_id > > The query plan shows: > > SELECT > P__Z1.PROPERTY_ID AS __C0_0, > SUM(CM__Z0.DAYS_R) AS __C0_1, > P__Z1.BEDROOMS AS __C0_2, > CM__Z0.PERIOD AS __C0_3, > P__Z1.ROOM_TYPE AS __C0_4, > SUM(CM__Z0.DAYS_R) AS __C0_5, > SUM(CM__Z0.DAYS_A) AS __C0_6 > FROM CalendarMetricCache.CALENDAR_METRIC CM__Z0 > /* CalendarMetricCache.CALENDAR_METRIC_PERIOD_DAYS_R_IDX: PERIOD >= > DATE > '2016-01-01' > AND PERIOD <= DATE '2016-08-01' > AND DAYS_R > 0 > */ > /* WHERE (CM__Z0.DAYS_R > 0) > AND ((CM__Z0.PERIOD >= DATE '2016-01-01') > AND (CM__Z0.PERIOD <= DATE '2016-08-01')) > */ > INNER JOIN PropertyCache.PROPERTY P__Z1 > /* PropertyCache.PROPERTY_CITY_ID_IDX: CITY_ID = 59053 */ > ON 1=1 > WHERE (P__Z1.PROPERTY_ID = CM__Z0.PROPERTY_ID) > AND ((P__Z1.CITY_ID = 59053) > AND (((CM__Z0.PERIOD >= DATE '2016-01-01') > AND (CM__Z0.PERIOD <= DATE '2016-08-01')) > AND ((P__Z1.ROOM_TYPE = 'Entire home/apt') > AND ((CM__Z0.DAYS_R > 0) > AND (P__Z1.BEDROOMS IS NOT NULL))))) > GROUP BY CM__Z0.PERIOD, P__Z1.ROOM_TYPE, P__Z1.BEDROOMS, P__Z1.PROPERTY_ID > > SELECT > __C0_0 AS PROPERTY_ID, > (CAST(SUM(__C0_1) AS REAL) / (SUM(__C0_5) + SUM(__C0_6))) AS OCC_RATE, > __C0_2 AS BEDROOMS, > __C0_3 AS PERIOD, > __C0_4 AS ROOM_TYPE > FROM PUBLIC.__T0 > /* CalendarMetricCache.merge_scan */ > GROUP BY __C0_3, __C0_4, __C0_2, __C0_0 > > > > -- > View this message in context: http://apache-ignite-users. > 70518.x6.nabble.com/odd-query-plan-with-joins-tp13680.html > Sent from the Apache Ignite Users mailing list archive at Nabble.com. >
