This query has so many joins and i have to apply conditions on joined table columns to get the desired results but query becomes slow when i apply condition on a datetime
column.
Here is the query
select
distinct v0_.id as id_0,
MIN(v4_.price) as sclr_4
from
venue v0_
left join facility f5_ on
v0_.id = f5_.venue_id
and (f5_.deleted_at is null)
left join sport_facility_types s6_ on
f5_.id = s6_.facility_id
left join taxonomy_term t7_ on
s6_.sport_id = t7_.id
and (t7_.deleted_at is null)
left join term t8_ on
t7_.term_id = t8_.id
left join sport_facility_types_taxonomy_term s10_ on
s6_.id = s10_.sport_facility_types_id
left join taxonomy_term t9_ on
t9_.id = s10_.taxonomy_term_id
and (t9_.deleted_at is null)
left join term t11_ on
t9_.term_id = t11_.id
left join facility_venue_item_price f12_ on
f5_.id = f12_.facility_id
left join venue_item_price v4_ on
f12_.venue_item_price_id = v4_.id
left join calendar_entry c13_ on
v4_.calendar_entry_id = c13_.id
where
(v0_.status = 'active'
and f5_.status = 'active')
and (v0_.deleted_at is null)
and c13_.start_at >= '2022-10-21 19:00:00' --- this slows down the query
group by
v0_.id
And here is the query plan https://explain.dalibo.com/plan/46h0fb3343e246a5.
The query plan is so big that i cannot paste it here
Plain query plan https://explain.depesz.com/s/7qnD
Plain query plan without where condition https://explain.depesz.com/s/3sK3
The query shouldn’t take much time as there are not many rows in tables.
- calendar_entry table has ~350000 rows
- venue_item_price table has also ~320000 rows
2
Answers
Your
WHERE
condition turns all the outer joins into inner joins (becausec13_.start_at
cannot be NULL any more), which changes the whole game. Usually that is an advantage, because it gives the optimizer more freedom, but that seems to have worked in the wrong direction in your case. One reason for that may be that you join more than 8 tables, and the default value forjoin_collapse_limit
is 8.That’s pretty much all I can say without a readable execution plan.
When you don’t reference c13_ anywhere in the output or WHERE clause, it can just skip doing that join altogether, because it can’t change anything. That is, the join is (presumably) on a unique key, so it can’t cause multiple rows to be returned; and it is a left join, so it can’t cause zero rows to be returned.
Once you reference it in the WHERE, however, the join has to be executed. That join only contributes about half the time, so it isn’t like the query was blazingly fast to start with.