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) |