Skip to content

Instantly share code, notes, and snippets.

@grindtildeath

grindtildeath/get_recipient_data_query_plans.txt

Last active June 1, 2022 08:55
Show Gist options
  • Save grindtildeath/cad3b1f863d10f9aca9df603a4d28248 to your computer and use it in GitHub Desktop.
Save grindtildeath/cad3b1f863d10f9aca9df603a4d28248 to your computer and use it in GitHub Desktop.
Download ZIP
Odoo mail.followers._get_recipient_data query plans
Raw
get_recipient_data_query_plans.txt
Before:
|QUERY PLAN |
|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Unique (cost=54330961.69..54429026.37 rows=40000 width=106) (actual time=7771.241..7771.241 rows=1 loops=1) |
| -> Sort (cost=54330961.69..54363649.92 rows=13075291 width=106) (actual time=7771.240..7771.240 rows=1 loops=1) |
| Sort Key: partner.id, (NULL::integer), users.notification_type |
| Sort Method: quicksort Memory: 25kB |
| -> Unique (cost=50113935.86..50375441.68 rows=13075291 width=106) (actual time=7771.232..7771.233 rows=1 loops=1) |
| CTE sub_followers |
| -> Nested Loop (cost=1.13..17.00 rows=1 width=13) (actual time=0.680..0.680 rows=0 loops=1) |
| -> Nested Loop (cost=1.13..15.23 rows=1 width=16) (actual time=0.680..0.680 rows=0 loops=1) |
| Join Filter: (fol.id = subrel.mail_followers_id) |
| -> Index Scan using mail_followers_mail_followers_res_partner_res_model_id_uniq on mail_followers fol (cost=0.56..8.58 rows=1 width=12) (actual time=0.679..0.679 rows=0 loops=1) |
| Index Cond: (((res_model)::text = 'helpdesk.ticket'::text) AND (res_id = 596703)) |
| -> Index Only Scan using mail_followers_mail_message_s_mail_message_subtype_id_mail__idx on mail_followers_mail_message_subtype_rel subrel (cost=0.56..6.63 rows=1 width=8) (never executed)|
| Index Cond: (mail_message_subtype_id = 43) |
| Heap Fetches: 0 |
| -> Seq Scan on mail_message_subtype subtype (cost=0.00..1.76 rows=1 width=5) (never executed) |
| Filter: (id = 43) |
| -> Sort (cost=50113918.85..50146607.08 rows=13075291 width=106) (actual time=7771.231..7771.232 rows=1 loops=1) |
| Sort Key: partner.id, (NULL::integer), partner.active, partner.partner_share, (NULL::character varying), users.notification_type, (array_agg(groups.id)) |
| Sort Method: quicksort Memory: 25kB |
| -> Append (cost=43695689.97..46289151.75 rows=13075291 width=106) (actual time=7771.160..7771.188 rows=1 loops=1) |
| -> GroupAggregate (cost=43695689.97..46093018.99 rows=13075290 width=80) (actual time=7771.159..7771.159 rows=1 loops=1) |
| Group Key: partner.id, users.notification_type |
| -> Sort (cost=43695689.97..44254161.95 rows=223388789 width=16) (actual time=7771.135..7771.136 rows=1 loops=1) |
| Sort Key: partner.id, users.notification_type |
| Sort Method: quicksort Memory: 25kB |
| -> Hash Right Join (cost=1238468.24..1264227.27 rows=223388789 width=16) (actual time=7769.537..7771.129 rows=1 loops=1) |
| Hash Cond: (users.partner_id = partner.id) |
| -> Hash Left Join (cost=56.60..232.03 rows=3656 width=14) (actual time=1.583..5.261 rows=4019 loops=1) |
| Hash Cond: (groups_rel.gid = groups.id) |
| -> Hash Right Join (cost=52.17..217.63 rows=3656 width=14) (actual time=1.248..4.263 rows=4019 loops=1) |
| Hash Cond: (groups_rel.uid = users.id) |
| -> Seq Scan on res_groups_users_rel groups_rel (cost=0.00..139.70 rows=9670 width=8) (actual time=0.006..1.794 rows=9670 loops=1) |
| -> Hash (cost=50.83..50.83 rows=107 width=14) (actual time=1.222..1.222 rows=107 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 14kB |
| -> Seq Scan on res_users users (cost=0.00..50.83 rows=107 width=14) (actual time=0.344..1.198 rows=107 loops=1) |
| Filter: active |
| Rows Removed by Filter: 176 |
| -> Hash (cost=3.08..3.08 rows=108 width=4) (actual time=0.326..0.326 rows=110 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 12kB |
| -> Seq Scan on res_groups groups (cost=0.00..3.08 rows=108 width=4) (actual time=0.298..0.311 rows=110 loops=1) |
| -> Hash (cost=1131153.08..1131153.08 rows=6537645 width=6) (actual time=7761.254..7761.254 rows=1 loops=1) |
| Buckets: 131072 Batches: 128 Memory Usage: 1025kB |
| -> Seq Scan on res_partner partner (cost=0.00..1131153.08 rows=6537645 width=6) (actual time=850.270..7761.183 rows=1 loops=1) |
| Filter: ((SubPlan 2) OR (id = 647429)) |
| Rows Removed by Filter: 13075287 |
| SubPlan 2 |
| -> CTE Scan on sub_followers sub_followers_1 (cost=0.00..0.02 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=13075288) |
| Filter: ((channel_id IS NULL) AND ((NOT COALESCE(internal, false)) OR (NOT COALESCE(partner.partner_share, false))) AND (partner_id = partner.id)) |
| -> Subquery Scan on "*SELECT* 2" (cost=0.03..3.40 rows=1 width=79) (actual time=0.026..0.026 rows=0 loops=1) |
| -> Hash Semi Join (cost=0.03..3.39 rows=1 width=79) (actual time=0.025..0.025 rows=0 loops=1) |
| Hash Cond: (channel.id = sub_followers.channel_id) |
| -> Seq Scan on mail_channel channel (cost=0.00..3.07 rows=107 width=10) (actual time=0.007..0.007 rows=1 loops=1) |
| -> Hash (cost=0.02..0.02 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 8kB |
| -> CTE Scan on sub_followers (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1) |
| Filter: (partner_id IS NULL) |
|Planning Time: 1.142 ms |
|JIT: |
| Functions: 69 |
| Options: Inlining true, Optimization true, Expressions true, Deforming true |
| Timing: Generation 6.103 ms, Inlining 12.849 ms, Optimization 416.217 ms, Emission 228.789 ms, Total 663.958 ms |
|Execution Time: 7777.582 ms
After:
|QUERY PLAN |
|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|Unique (cost=862558.40..862558.61 rows=28 width=106) (actual time=729.126..729.126 rows=1 loops=1) |
| -> Sort (cost=862558.40..862558.47 rows=28 width=106) (actual time=729.125..729.125 rows=1 loops=1) |
| Sort Key: partner.id, (NULL::integer), users.notification_type |
| Sort Method: quicksort Memory: 25kB |
| -> HashAggregate (cost=862557.16..862557.44 rows=28 width=106) (actual time=729.119..729.120 rows=1 loops=1) |
| Group Key: partner.id, (NULL::integer), partner.active, partner.partner_share, (NULL::text), users.notification_type, (array_agg(groups.id)) |
| CTE sub_followers |
| -> Nested Loop (cost=1.13..17.00 rows=1 width=13) (actual time=0.019..0.019 rows=0 loops=1) |
| -> Nested Loop (cost=1.13..15.23 rows=1 width=16) (actual time=0.018..0.018 rows=0 loops=1) |
| Join Filter: (fol.id = subrel.mail_followers_id) |
| -> Index Scan using mail_followers_mail_followers_res_partner_res_model_id_uniq on mail_followers fol (cost=0.56..8.58 rows=1 width=12) (actual time=0.017..0.017 rows=0 loops=1) |
| Index Cond: (((res_model)::text = 'helpdesk.ticket'::text) AND (res_id = 596703)) |
| -> Index Only Scan using mail_followers_mail_message_s_mail_message_subtype_id_mail__idx on mail_followers_mail_message_subtype_rel subrel (cost=0.56..6.63 rows=1 width=8) (never executed)|
| Index Cond: (mail_message_subtype_id = 43) |
| Heap Fetches: 0 |
| -> Seq Scan on mail_message_subtype subtype (cost=0.00..1.76 rows=1 width=5) (never executed) |
| Filter: (id = 43) |
| -> Append (cost=862421.38..862539.67 rows=28 width=106) (actual time=729.106..729.113 rows=1 loops=1) |
| -> GroupAggregate (cost=862421.38..862421.96 rows=26 width=80) (actual time=0.041..0.041 rows=0 loops=1) |
| Group Key: partner.id, users.notification_type |
| -> Sort (cost=862421.38..862421.44 rows=26 width=16) (actual time=0.040..0.040 rows=0 loops=1) |
| Sort Key: partner.id, users.notification_type |
| Sort Method: quicksort Memory: 25kB |
| -> Nested Loop Left Join (cost=0.03..862420.77 rows=26 width=16) (actual time=0.037..0.037 rows=0 loops=1) |
| Join Filter: (groups.id = groups_rel.gid) |
| -> Nested Loop Left Join (cost=0.03..862375.30 rows=26 width=16) (actual time=0.037..0.037 rows=0 loops=1) |
| Join Filter: (groups_rel.uid = users.id) |
| -> Nested Loop Left Join (cost=0.03..862114.72 rows=1 width=16) (actual time=0.037..0.037 rows=0 loops=1) |
| Join Filter: (users.partner_id = partner.id) |
| -> Hash Semi Join (cost=0.03..862062.55 rows=1 width=6) (actual time=0.036..0.037 rows=0 loops=1) |
| Hash Cond: (partner.id = sub_followers.partner_id) |
| Join Filter: ((NOT COALESCE(sub_followers.internal, false)) OR (NOT COALESCE(partner.partner_share, false))) |
| -> Seq Scan on res_partner partner (cost=0.00..827739.88 rows=13075288 width=6) (actual time=0.012..0.012 rows=1 loops=1) |
| -> Hash (cost=0.02..0.02 rows=1 width=5) (actual time=0.021..0.021 rows=0 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 8kB |
| -> CTE Scan on sub_followers (cost=0.00..0.02 rows=1 width=5) (actual time=0.020..0.020 rows=0 loops=1) |
| Filter: (channel_id IS NULL) |
| -> Seq Scan on res_users users (cost=0.00..50.83 rows=107 width=14) (never executed) |
| Filter: active |
| -> Seq Scan on res_groups_users_rel groups_rel (cost=0.00..139.70 rows=9670 width=8) (never executed) |
| -> Materialize (cost=0.00..3.62 rows=108 width=4) (never executed) |
| -> Seq Scan on res_groups groups (cost=0.00..3.08 rows=108 width=4) (never executed) |
| -> GroupAggregate (cost=113.54..113.90 rows=1 width=80) (actual time=729.064..729.064 rows=1 loops=1) |
| Group Key: partner_1.id, users_1.notification_type |
| -> Sort (cost=113.54..113.63 rows=34 width=16) (actual time=729.057..729.058 rows=1 loops=1) |
| Sort Key: users_1.notification_type |
| Sort Method: quicksort Memory: 25kB |
| -> Hash Left Join (cost=9.42..112.68 rows=34 width=16) (actual time=729.050..729.053 rows=1 loops=1) |
| Hash Cond: (groups_rel_1.gid = groups_1.id) |
| -> Nested Loop Left Join (cost=4.99..108.16 rows=34 width=16) (actual time=729.024..729.027 rows=1 loops=1) |
| -> Nested Loop Left Join (cost=0.43..60.00 rows=1 width=16) (actual time=729.013..729.015 rows=1 loops=1) |
| Join Filter: (users_1.partner_id = partner_1.id) |
| -> Index Scan using res_partner_pkey on res_partner partner_1 (cost=0.43..8.45 rows=1 width=6) (actual time=0.008..0.010 rows=1 loops=1) |
| Index Cond: (id = 647429) |
| -> Seq Scan on res_users users_1 (cost=0.00..51.54 rows=1 width=14) (actual time=728.997..728.997 rows=0 loops=1) |
| Filter: (active AND (partner_id = 647429)) |
| Rows Removed by Filter: 283 |
| -> Bitmap Heap Scan on res_groups_users_rel groups_rel_1 (cost=4.56..47.81 rows=35 width=8) (actual time=0.004..0.004 rows=0 loops=1) |
| Recheck Cond: (uid = users_1.id) |
| -> Bitmap Index Scan on res_groups_users_rel_uid_gid_idx (cost=0.00..4.55 rows=35 width=0) (actual time=0.001..0.001 rows=0 loops=1) |
| Index Cond: (uid = users_1.id) |
| -> Hash (cost=3.08..3.08 rows=108 width=4) (actual time=0.019..0.019 rows=110 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 12kB |
| -> Seq Scan on res_groups groups_1 (cost=0.00..3.08 rows=108 width=4) (actual time=0.005..0.010 rows=110 loops=1) |
| -> Subquery Scan on "*SELECT* 3" (cost=0.03..3.40 rows=1 width=106) (actual time=0.006..0.006 rows=0 loops=1) |
| -> Hash Semi Join (cost=0.03..3.39 rows=1 width=79) (actual time=0.005..0.005 rows=0 loops=1) |
| Hash Cond: (channel.id = sub_followers_1.channel_id) |
| -> Seq Scan on mail_channel channel (cost=0.00..3.07 rows=107 width=10) (actual time=0.003..0.003 rows=1 loops=1) |
| -> Hash (cost=0.02..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 8kB |
| -> CTE Scan on sub_followers sub_followers_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1) |
| Filter: (partner_id IS NULL) |
|Planning Time: 0.946 ms |
|JIT: |
| Functions: 94 |
| Options: Inlining true, Optimization true, Expressions true, Deforming true |
| Timing: Generation 6.183 ms, Inlining 7.780 ms, Optimization 428.226 ms, Emission 292.613 ms, Total 734.802 ms |
|Execution Time: 735.515 ms |
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment