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.
