PostgreSql query becomes slow when adding where condition-CodePudding

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

CodePudding user response：

Your WHERE condition turns all the outer joins into inner joins (because c13_.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 for join_collapse_limit is 8.

That's pretty much all I can say without a readable execution plan.

CodePudding user response：

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.