add batch remove batch split batch show hidden batches hide batch
db<>fiddle
feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
version()
8.0.27
EXPLAIN
-> Filter: ((notification.`status` = 0) and (<in_optimizer>(notification.notif_id,notification.notif_id in (select #2)) or <in_optimizer>(notification.notif_id,notification.notif_id in (select #3)))) (cost=0.75 rows=1) (actual time=0.064..0.101 rows=4 loops=1)
    -> Table scan on notification (cost=0.75 rows=5) (actual time=0.017..0.020 rows=5 loops=1)
    -> Select #2 (subquery in condition; run only once)
        -> Filter: ((notification.notif_id = `<materialized_subquery>`.notif_id)) (cost=0.95..0.95 rows=1) (actual time=0.009..0.009 rows=1 loops=5)
            -> Limit: 1 row(s) (actual time=0.008..0.008 rows=1 loops=5)
                -> Index lookup on <materialized_subquery> using <auto_distinct_key> (notif_id=notification.notif_id) (actual time=0.001..0.001 rows=1 loops=5)
                    -> Materialize with deduplication (cost=0.95..0.95 rows=1) (actual time=0.040..0.040 rows=3 loops=1)
                        -> Filter: (notif_user.user_id = 1) (cost=0.85 rows=1) (actual time=0.008..0.019 rows=3 loops=1)
                            -> Table scan on notif_user (cost=0.85 rows=6) (actual time=0.005..0.014 rows=6 loops=1)
    -> Select #3 (subquery in condition; run only once)
        -> Filter: ((notification.notif_id = `<materialized_subquery>`.notif_id)) (cost=0.85..0.85 rows=1) (actual time=0.014..0.014 rows=0 loops=2)
            -> Limit: 1 row(s) (actual time=0.013..0.013 rows=0 loops=2)
                -> Index lookup on <materialized_subquery> using <auto_distinct_key> (notif_id=notification.notif_id) (actual time=0.001..0.001 rows=0 loops=2)
                    -> Materialize with deduplication (cost=0.85..0.85 rows=1) (actual time=0.026..0.026 rows=3 loops=1)
                        -> Filter: (notif_group.group_id = 1) (cost=0.75 rows=1) (actual time=0.007..0.015 rows=3 loops=1)
                            -> Table scan on notif_group (cost=0.75 rows=5) (actual time=0.006..0.013 rows=5 loops=1)
EXPLAIN
-> Filter: ((notification.`status` = 0) and <in_optimizer>(notification.notif_id,<exists>(select #2))) (cost=0.75 rows=1) (actual time=0.026..0.067 rows=4 loops=1)
    -> Table scan on notification (cost=0.75 rows=5) (actual time=0.011..0.014 rows=5 loops=1)
    -> Select #2 (subquery in condition; dependent)
        -> Limit: 1 row(s) (actual time=0.010..0.010 rows=1 loops=4)
            -> Append (actual time=0.010..0.010 rows=1 loops=4)
                -> Stream results (cost=0.85 rows=1) (actual time=0.008..0.008 rows=1 loops=4)
                    -> Limit: 1 row(s) (cost=0.85 rows=1) (actual time=0.007..0.007 rows=1 loops=4)
                        -> Filter: ((notif_user.user_id = 1) and (<cache>(notification.notif_id) = notif_user.notif_id)) (cost=0.85 rows=1) (actual time=0.007..0.007 rows=1 loops=4)
                            -> Table scan on notif_user (cost=0.85 rows=6) (actual time=0.003..0.005 rows=4 loops=4)
                -> Stream results (cost=0.75 rows=1) (actual time=0.008..0.008 rows=1 loops=1)
                    -> Limit: 1 row(s) (cost=0.75 rows=1) (actual time=0.007..0.007 rows=1 loops=1)
                        -> Filter: ((notif_group.group_id = 1) and (<cache>(notification.notif_id) = notif_group.notif_id)) (cost=0.75 rows=1) (actual time=0.007..0.007 rows=1 loops=1)
                            -> Table scan on notif_group (cost=0.75 rows=5) (actual time=0.002..0.005 rows=5 loops=1)
EXPLAIN
-> Filter: ((n.`status` = 0) and exists(select #2)) (cost=0.75 rows=1) (actual time=0.022..0.059 rows=4 loops=1)
    -> Table scan on n (cost=0.75 rows=5) (actual time=0.011..0.014 rows=5 loops=1)
    -> Select #2 (subquery in condition; dependent)
        -> Limit: 1 row(s) (actual time=0.009..0.009 rows=1 loops=4)
            -> Append (actual time=0.009..0.009 rows=1 loops=4)
                -> Stream results (cost=0.85 rows=1) (actual time=0.007..0.007 rows=1 loops=4)
                    -> Filter: ((nu.user_id = 1) and (nu.notif_id = n.notif_id)) (cost=0.85 rows=1) (actual time=0.006..0.006 rows=1 loops=4)
                        -> Table scan on nu (cost=0.85 rows=6) (actual time=0.003..0.005 rows=4 loops=4)
                -> Stream results (cost=0.75 rows=1) (actual time=0.007..0.007 rows=1 loops=1)
                    -> Filter: ((ng.group_id = 1) and (ng.notif_id = n.notif_id)) (cost=0.75 rows=1) (actual time=0.007..0.007 rows=1 loops=1)
                        -> Table scan on ng (cost=0.75 rows=5) (actual time=0.002..0.005 rows=5 loops=1)
EXPLAIN
-> Filter: ((n.`status` = 0) and (exists(select #2) or exists(select #3))) (cost=0.75 rows=1) (actual time=0.019..0.054 rows=4 loops=1)
    -> Table scan on n (cost=0.75 rows=5) (actual time=0.011..0.013 rows=5 loops=1)
    -> Select #2 (subquery in condition; dependent)
        -> Limit: 1 row(s) (cost=0.85 rows=1) (actual time=0.006..0.006 rows=1 loops=4)
            -> Filter: ((nu.user_id = 1) and (nu.notif_id = n.notif_id)) (cost=0.85 rows=1) (actual time=0.006..0.006 rows=1 loops=4)
                -> Table scan on nu (cost=0.85 rows=6) (actual time=0.002..0.005 rows=4 loops=4)
    -> Select #3 (subquery in condition; dependent)
        -> Limit: 1 row(s) (cost=0.75 rows=1) (actual time=0.007..0.007 rows=1 loops=1)
            -> Filter: ((ng.group_id = 1) and (ng.notif_id = n.notif_id)) (cost=0.75 rows=1) (actual time=0.007..0.007 rows=1 loops=1)
                -> Table scan on ng (cost=0.75 rows=5) (actual time=0.002..0.005 rows=5 loops=1)