Skip to content

Instantly share code, notes, and snippets.

@mayel

mayel/explain.sql

Last active October 26, 2024 17:55
Show Gist options
  • Save mayel/2e1f408cf202c47a625e5208def81991 to your computer and use it in GitHub Desktop.
Save mayel/2e1f408cf202c47a625e5208def81991 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
explain.sql
[warning] Slow database query: ok db=10449ms repo=Elixir.Bonfire.Common.Repo
EXPLAIN ( ANALYZE TRUE, VERBOSE FALSE, COSTS TRUE, TIMING TRUE, SUMMARY TRUE, FORMAT TEXT ) SELECT b0."id", b0."feed_id", b1."id", b1."subject_id", b1."verb_id", b1."object_id", b15."id", b15."is_sensitive", b20."id", b20."reply_to_id", b20."thread_id", b20."direct_replies_count", b20."nested_replies_count", b20."total_replies_count", b20."path", p3."id", p3."table_id", p3."deleted_at", b18."id", b18."name", b18."summary", b18."website", b18."location", b18."icon_id", b18."image_id", b19."id", b19."creator_id", b19."path", b19."file", b19."size", b19."media_type", b19."metadata", b19."deleted_at", b17."id", b17."username", b17."username_hash", b17."outbox_id", b17."inbox_id", b17."notifications_id", b10."id", b10."creator_id", p11."id", p11."table_id", p11."deleted_at", b13."id", b13."name", b13."summary", b13."website", b13."location", b13."icon_id", b13."image_id", b14."id", b14."creator_id", b14."path", b14."file", b14."size", b14."media_type", b14."metadata", b14."deleted_at", b12."id", b12."username", b12."username_hash", b12."outbox_id", b12."inbox_id", b12."notifications_id", b7."id", b7."peer_id", b7."canonical_uri", b16."id", b16."name", b16."summary", b16."html_body", p4."id", p4."table_id", p4."deleted_at", b8."id", b8."name", b8."summary", b8."website", b8."location", b8."icon_id", b8."image_id", b9."id", b9."creator_id", b9."path", b9."file", b9."size", b9."media_type", b9."metadata", b9."deleted_at", b5."id", b5."username", b5."username_hash", b5."outbox_id", b5."inbox_id", b5."notifications_id", b6."id", b6."peer_id", b6."canonical_uri" FROM "bonfire_data_social_feed_publish" AS b0 INNER JOIN "bonfire_data_social_activity" AS b1 ON b1."id" = b0."id" INNER JOIN "pointers_pointer" AS p2 ON ((p2."id" = b1."id") AND (p2."deleted_at" IS NULL)) AND NOT (p2."table_id" = ANY('{6R1VATEMESAGEC0MMVN1CAT10N}')) INNER JOIN "pointers_pointer" AS p3 ON ((p3."id" = b1."object_id") AND (p3."deleted_at" IS NULL)) AND ((p3."table_id" IS NULL) OR NOT (p3."table_id" = ANY('{6R1VATEMESAGEC0MMVN1CAT10N}'))) LEFT OUTER JOIN "pointers_pointer" AS p4 ON p4."id" = b1."subject_id" LEFT OUTER JOIN "bonfire_data_identity_character" AS b5 ON b5."id" = p4."id" LEFT OUTER JOIN "bonfire_data_activity_pub_peered" AS b6 ON b6."id" = b5."id" LEFT OUTER JOIN "bonfire_data_activity_pub_peered" AS b7 ON b7."id" = p3."id" LEFT OUTER JOIN "bonfire_data_social_profile" AS b8 ON b8."id" = p4."id" LEFT OUTER JOIN "bonfire_files_media" AS b9 ON b9."id" = b8."icon_id" LEFT OUTER JOIN "bonfire_data_social_created" AS b10 ON b10."id" = p3."id" LEFT OUTER JOIN "pointers_pointer" AS p11 ON (b10."creator_id" != b1."subject_id") AND (b10."creator_id" = p11."id") LEFT OUTER JOIN "bonfire_data_identity_character" AS b12 ON b12."id" = p11."id" LEFT OUTER JOIN "bonfire_data_social_profile" AS b13 ON b13."id" = p11."id" LEFT OUTER JOIN "bonfire_files_media" AS b14 ON b14."id" = b13."icon_id" LEFT OUTER JOIN "bonfire_data_social_sensitive" AS b15 ON b15."id" = b1."object_id" LEFT OUTER JOIN "bonfire_data_social_post_content" AS b16 ON b16."id" = p3."id" LEFT OUTER JOIN "bonfire_data_identity_character" AS b17 ON b17."id" = p3."id" LEFT OUTER JOIN "bonfire_data_social_profile" AS b18 ON b18."id" = p3."id" LEFT OUTER JOIN "bonfire_files_media" AS b19 ON b19."id" = b18."icon_id" LEFT OUTER JOIN "bonfire_data_social_replied" AS b20 ON b20."id" = b1."object_id" WHERE (NOT (b1."verb_id" = ANY('{71AGSPAM0RVNACCEPTAB1E1TEM,20SVBSCR1BET0THE0VTPVT0F1T,11KES1ND1CATEAM11DAPPR0VA1,1P1NN1NNG1S11KEH1GH11GHT1T,40NTACTW1THAPR1VATEMESSAGE,7PDATETHESTATVS0FS0METH1NG}'))) AND (((b0."feed_id" = '3SERSFR0MY0VR10CA11NSTANCE') OR ((b6."id" IS NULL) AND (b7."id" IS NULL))) AND (b1."subject_id" != '1ACT1V1TYPVBREM0TESFETCHER')) AND (exists((SELECT count(ss0."subject_id"), ss0."object_id" FROM (SELECT ssp0."id" AS "subject_id", ssb1."id" AS "object_id", ssb2."id" AS "verb_id", agg_perms(ssb3."value") AS "value" FROM "pointers_pointer" AS ssp0 CROSS JOIN "bonfire_data_access_control_controlled" AS ssb1 CROSS JOIN "bonfire_data_access_control_verb" AS ssb2 LEFT OUTER JOIN "bonfire_data_access_control_grant" AS ssb3 ON (ssb1."acl_id" = ssb3."acl_id") AND (ssb3."verb_id" = ssb2."id") LEFT OUTER JOIN "bonfire_data_access_control_circle" AS ssb4 ON ssb3."subject_id" = ssb4."id" LEFT OUTER JOIN "bonfire_data_access_control_encircle" AS ssb5 ON (ssb5."circle_id" = ssb4."id") AND (ssb5."subject_id" = ssp0."id") WHERE ((ssb3."subject_id" = ssp0."id") OR NOT (ssb5."id" IS NULL)) GROUP BY ssp0."id", ssb1."id", ssb2."id") AS ss0 WHERE (ss0."object_id" = b1."object_id") AND (ss0."subject_id" = ANY('{0AND0MSTRANGERS0FF1NTERNET}') AND ss0."verb_id" = ANY('{0BSERV1NG11ST1NGSEX1STENCE,0EAD1NGSVTTER1YFVNDAMENTA1}')) GROUP BY ss0."object_id" HAVING (agg_perms(ss0."value"))))) ORDER BY b1."id" DESC
Nested Loop Left Join (cost=5.53..14013102.53 rows=20424 width=3801) (actual time=931.066..10406.035 rows=171157 loops=1)
-> Nested Loop Left Join (cost=5.24..14002899.44 rows=20424 width=3685) (actual time=931.042..10220.073 rows=171157 loops=1)
Join Filter: (b19.id = b18.icon_id)
-> Nested Loop Left Join (cost=5.24..13969188.47 rows=20424 width=3029) (actual time=931.039..10187.643 rows=171157 loops=1)
-> Nested Loop Left Join (cost=4.97..13963092.11 rows=20424 width=2730) (actual time=931.034..10087.148 rows=171157 loops=1)
-> Nested Loop Left Join (cost=4.69..13956993.98 rows=20424 width=2604) (actual time=931.030..9979.222 rows=171157 loops=1)
-> Nested Loop Left Join (cost=4.40..13950357.83 rows=20424 width=2555) (actual time=931.023..9811.572 rows=171157 loops=1)
-> Nested Loop Left Join (cost=4.11..13942148.63 rows=20424 width=2538) (actual time=931.005..9642.909 rows=171157 loops=1)
Join Filter: (b14.id = b13.icon_id)
-> Nested Loop Left Join (cost=4.11..13908437.66 rows=20424 width=1882) (actual time=931.002..9610.874 rows=171157 loops=1)
-> Nested Loop Left Join (cost=3.83..13902341.31 rows=20424 width=1583) (actual time=930.998..9565.341 rows=171157 loops=1)
-> Nested Loop Left Join (cost=3.55..13896243.18 rows=20424 width=1457) (actual time=930.995..9519.698 rows=171157 loops=1)
Join Filter: (b10.creator_id <> b1.subject_id)
Rows Removed by Join Filter: 171157
-> Nested Loop Left Join (cost=3.13..13882881.94 rows=20424 width=1417) (actual time=930.982..9325.585 rows=171157 loops=1)
-> Nested Loop Left Join (cost=2.84..13876296.87 rows=20424 width=1385) (actual time=930.970..9158.047 rows=171157 loops=1)
Join Filter: (b9.id = b8.icon_id)
-> Nested Loop Left Join (cost=2.84..13842585.90 rows=20424 width=729) (actual time=930.965..9125.640 rows=171157 loops=1)
-> Merge Join (cost=2.57..13836489.55 rows=20424 width=430) (actual time=930.955..8970.595 rows=171157 loops=1)
Merge Cond: (b1.id = b0.id)
Join Filter: ((b0.feed_id = '797632fc-029e-06f0-1031-410d73a5558e'::uuid) OR ((b6.id IS NULL) AND (b7.id IS NULL)))
-> Nested Loop Left Join (cost=2.15..13828102.20 rows=20158 width=414) (actual time=930.940..8901.991 rows=42876 loops=1)
-> Nested Loop Left Join (cost=1.99..13827362.43 rows=20158 width=350) (actual time=930.927..8884.070 rows=42876 loops=1)
-> Nested Loop Left Join (cost=1.84..13823979.52 rows=20158 width=286) (actual time=930.922..8860.636 rows=42876 loops=1)
-> Nested Loop Left Join (cost=1.56..13817960.81 rows=20158 width=160) (actual time=930.910..8810.652 rows=42876 loops=1)
-> Nested Loop (cost=1.13..13817271.70 rows=20158 width=120) (actual time=930.892..8792.031 rows=42876 loops=1)
-> Nested Loop (cost=0.71..13800431.34 rows=20159 width=80) (actual time=930.887..8745.333 rows=42876 loops=1)
-> Index Scan Backward using bonfire_data_social_activity_pkey on bonfire_data_social_activity b1 (cost=0.29..13783590.14 rows=20160 width=64) (actual time=930.875..8673.042 rows=42876 loops=1)
Filter: ((subject_id <> '2a6683b0-ebd6-daf0-ea03-4ecbdda645d8'::uuid) AND (verb_id <> ALL ('{e1543365-5018-dd54-c63a-da52c2e0e9d4,40ced796-602b-7681-a8b8-1bd5b7a03c3a,219bb21a-b42c-569c-aa04-2d55ad806d41,360d6a1a-d601-c843-3744-30884308e83a,80ae94cd-703a-8aad-80ed-5a751d9caa0e,f66ab4ed-45d9-d2b5-bc81-f9051da886b0}'::uuid[])) AND EXISTS(SubPlan 1))
Rows Removed by Filter: 4
SubPlan 1
-> GroupAggregate (cost=335.27..341.49 rows=1 width=24) (actual time=0.180..0.180 rows=1 loops=42876)
Filter: agg_perms((agg_perms(ssb3.value)))
-> GroupAggregate (cost=335.27..340.95 rows=2 width=49) (actual time=0.178..0.179 rows=2 loops=42876)
Group Key: ssp0.id, ssb2.id
-> Sort (cost=335.27..335.32 rows=22 width=49) (actual time=0.177..0.177 rows=2 loops=42876)
Sort Key: ssp0.id, ssb2.id
Sort Method: quicksort Memory: 25kB
-> Hash Right Join (cost=228.69..334.78 rows=22 width=49) (actual time=0.175..0.176 rows=2 loops=42876)
Hash Cond: ((ssb5.circle_id = pointers_pointer.id) AND (ssb5.subject_id = ssp0.id))
Filter: ((ssb3.subject_id = ssp0.id) OR (ssb5.id IS NOT NULL))
Rows Removed by Filter: 18
-> Seq Scan on bonfire_data_access_control_encircle ssb5 (cost=0.00..97.47 rows=1147 width=48) (actual time=0.001..0.060 rows=1147 loops=42876)
-> Hash (cost=228.36..228.36 rows=22 width=81) (actual time=0.048..0.048 rows=20 loops=42876)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> Nested Loop Left Join (cost=5.85..228.36 rows=22 width=81) (actual time=0.007..0.045 rows=20 loops=42876)
-> Nested Loop (cost=5.43..216.43 rows=22 width=65) (actual time=0.006..0.031 rows=20 loops=42876)
-> Index Only Scan using pointers_pointer_pkey on pointers_pointer ssp0 (cost=0.42..8.44 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=42876)
Index Cond: (id = ANY ('{0aab414c-eb0a-ac1d-8c81-ef0d74ec55da}'::uuid[]))
Heap Fetches: 0
-> Nested Loop Left Join (cost=5.01..207.77 rows=22 width=49) (actual time=0.005..0.029 rows=20 loops=42876)
-> Nested Loop (cost=4.59..18.40 rows=22 width=48) (actual time=0.002..0.006 rows=20 loops=42876)
-> Index Only Scan using bonfire_data_access_control_controlled_pkey on bonfire_data_access_control_controlled ssb1 (cost=0.42..8.61 rows=11 width=32) (actual time=0.002..0.003 rows=10 loops=42876)
Index Cond: (id = b1.object_id)
Heap Fetches: 3230
-> Materialize (cost=4.17..9.52 rows=2 width=16) (actual time=0.000..0.000 rows=2 loops=428911)
-> Bitmap Heap Scan on bonfire_data_access_control_verb ssb2 (cost=4.17..9.51 rows=2 width=16) (actual time=0.036..0.037 rows=2 loops=1)
Recheck Cond: (id = ANY ('{0bcbb1b0-d601-0e74-1ac3-2ee873a7558e,0e534358-677a-d3b0-1f3f-756aa8eae941}'::uuid[]))
Heap Blocks: exact=1
-> Bitmap Index Scan on bonfire_data_access_control_verb_pkey (cost=0.00..4.17 rows=2 width=0) (actual time=0.022..0.022 rows=2 loops=1)
Index Cond: (id = ANY ('{0bcbb1b0-d601-0e74-1ac3-2ee873a7558e,0e534358-677a-d3b0-1f3f-756aa8eae941}'::uuid[]))
-> Index Scan using bonfire_data_access_control_grant_acl_id_subject_id_verb_id_ind on bonfire_data_access_control_grant ssb3 (cost=0.42..8.60 rows=1 width=49) (actual time=0.001..0.001 rows=1 loops=857822)
Index Cond: ((acl_id = ssb1.acl_id) AND (verb_id = ssb2.id))
-> Index Scan using pointers_pointer_pkey on pointers_pointer (cost=0.42..0.54 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=857822)
Index Cond: (id = ssb3.subject_id)
Filter: ((deleted_at IS NULL) AND (table_id = '81c302ec-ab0e-56c3-90ac-210ebd9602ce'::uuid))
Rows Removed by Filter: 0
-> Index Scan using pointers_pointer_pkey on pointers_pointer p2 (cost=0.42..0.84 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=42876)
Index Cond: (id = b1.id)
Filter: ((deleted_at IS NULL) AND (table_id <> ALL ('{d80ed5a7-51d9-541c-c052-9ba858ad0415}'::uuid[])))
-> Index Scan using pointers_pointer_pkey on pointers_pointer p3 (cost=0.42..0.84 rows=1 width=40) (actual time=0.001..0.001 rows=1 loops=42876)
Index Cond: (id = b1.object_id)
Filter: ((deleted_at IS NULL) AND ((table_id IS NULL) OR (table_id <> ALL ('{d80ed5a7-51d9-541c-c052-9ba858ad0415}'::uuid[]))))
-> Memoize (cost=0.43..0.84 rows=1 width=40) (actual time=0.000..0.000 rows=1 loops=42876)
Cache Key: b1.subject_id
Cache Mode: logical
Hits: 42655 Misses: 221 Evictions: 0 Overflows: 0 Memory Usage: 32kB
-> Index Scan using pointers_pointer_pkey on pointers_pointer p4 (cost=0.42..0.83 rows=1 width=40) (actual time=0.001..0.001 rows=1 loops=221)
Index Cond: (id = b1.subject_id)
-> Index Scan using bonfire_data_identity_character_pkey on bonfire_data_identity_character b5 (cost=0.28..0.30 rows=1 width=126) (actual time=0.001..0.001 rows=1 loops=42876)
Index Cond: (id = p4.id)
-> Index Scan using bonfire_data_activity_pub_peered_pkey on bonfire_data_activity_pub_peered b7 (cost=0.15..0.17 rows=1 width=64) (actual time=0.000..0.000 rows=0 loops=42876)
Index Cond: (id = p3.id)
-> Memoize (cost=0.16..0.22 rows=1 width=64) (actual time=0.000..0.000 rows=0 loops=42876)
Cache Key: b5.id
Cache Mode: logical
Hits: 42655 Misses: 221 Evictions: 0 Overflows: 0 Memory Usage: 18kB
-> Index Scan using bonfire_data_activity_pub_peered_pkey on bonfire_data_activity_pub_peered b6 (cost=0.15..0.21 rows=1 width=64) (actual time=0.000..0.000 rows=0 loops=221)
Index Cond: (id = b5.id)
-> Index Only Scan Backward using bonfire_data_social_feed_publish_pkey on bonfire_data_social_feed_publish b0 (cost=0.42..6891.57 rows=169693 width=32) (actual time=0.009..36.743 rows=171165 loops=1)
Heap Fetches: 13269
-> Index Scan using bonfire_data_social_profile_pkey on bonfire_data_social_profile b8 (cost=0.28..0.30 rows=1 width=299) (actual time=0.001..0.001 rows=1 loops=171157)
Index Cond: (id = p4.id)
-> Materialize (cost=0.00..11.65 rows=110 width=656) (actual time=0.000..0.000 rows=0 loops=171157)
-> Seq Scan on bonfire_files_media b9 (cost=0.00..11.10 rows=110 width=656) (actual time=0.003..0.003 rows=0 loops=1)
-> Index Scan using bonfire_data_social_created_pkey on bonfire_data_social_created b10 (cost=0.29..0.32 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=171157)
Index Cond: (id = p3.id)
-> Index Scan using pointers_pointer_pkey on pointers_pointer p11 (cost=0.42..0.64 rows=1 width=40) (actual time=0.001..0.001 rows=1 loops=171157)
Index Cond: (id = b10.creator_id)
-> Index Scan using bonfire_data_identity_character_pkey on bonfire_data_identity_character b12 (cost=0.28..0.30 rows=1 width=126) (actual time=0.000..0.000 rows=0 loops=171157)
Index Cond: (id = p11.id)
-> Index Scan using bonfire_data_social_profile_pkey on bonfire_data_social_profile b13 (cost=0.28..0.30 rows=1 width=299) (actual time=0.000..0.000 rows=0 loops=171157)
Index Cond: (id = p11.id)
-> Materialize (cost=0.00..11.65 rows=110 width=656) (actual time=0.000..0.000 rows=0 loops=171157)
-> Seq Scan on bonfire_files_media b14 (cost=0.00..11.10 rows=110 width=656) (actual time=0.001..0.003 rows=0 loops=1)
-> Index Scan using bonfire_data_social_sensitive_pkey on bonfire_data_social_sensitive b15 (cost=0.29..0.40 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=171157)
Index Cond: (id = b1.object_id)
-> Index Scan using bonfire_data_social_post_content_pkey on bonfire_data_social_post_content b16 (cost=0.29..0.32 rows=1 width=49) (actual time=0.001..0.001 rows=1 loops=171157)
Index Cond: (id = p3.id)
-> Index Scan using bonfire_data_identity_character_pkey on bonfire_data_identity_character b17 (cost=0.28..0.30 rows=1 width=126) (actual time=0.000..0.000 rows=0 loops=171157)
Index Cond: (id = p3.id)
-> Index Scan using bonfire_data_social_profile_pkey on bonfire_data_social_profile b18 (cost=0.28..0.30 rows=1 width=299) (actual time=0.000..0.000 rows=0 loops=171157)
Index Cond: (id = p3.id)
-> Materialize (cost=0.00..11.65 rows=110 width=656) (actual time=0.000..0.000 rows=0 loops=171157)
-> Seq Scan on bonfire_files_media b19 (cost=0.00..11.10 rows=110 width=656) (actual time=0.000..0.001 rows=0 loops=1)
-> Index Scan using bonfire_data_social_replied_pkey on bonfire_data_social_replied b20 (cost=0.29..0.50 rows=1 width=116) (actual time=0.001..0.001 rows=1 loops=171157)
Index Cond: (id = b1.object_id)
Planning Time: 5.813 ms
JIT:
Functions: 157
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 5.198 ms (Deform 1.811 ms), Inlining 50.826 ms, Optimization 288.764 ms, Emission 589.825 ms, Total 934.614 ms
Execution Time: 10440.878 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment