Created
August 4, 2014 22:46
-
-
Save inklesspen/7e2577cf6fa9f73bc9c2 to your computer and use it in GitHub Desktop.
Postgres CTE optimization problems
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
QUERY PLAN | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
CTE Scan on pn_downstream_thread (cost=28135.55..28281.37 rows=7291 width=8) (actual time=0.131..0.410 rows=6 loops=1) | |
CTE pn_downstream_thread | |
-> Recursive Union (cost=1.71..28135.55 rows=7291 width=12) (actual time=0.126..0.391 rows=6 loops=1) | |
-> Nested Loop (cost=1.71..68.15 rows=1 width=12) (actual time=0.118..0.275 rows=2 loops=1) | |
-> Nested Loop (cost=1.28..59.69 rows=1 width=12) (actual time=0.102..0.230 rows=3 loops=1) | |
-> Nested Loop (cost=0.85..21.12 rows=5 width=8) (actual time=0.058..0.100 rows=12 loops=1) | |
-> Index Scan using idx_16485_unique_asset_typ_id on ams_asset (cost=0.42..8.45 rows=1 width=8) (actual time=0.038..0.039 rows=1 loops=1) | |
Index Cond: ((typ = 'tp'::text) AND (public_id = '59c89bdcaf6711e3b67f12313b0a607d'::text)) | |
-> Index Scan using idx_16551_asset_id on ams_msg_asset (cost=0.43..11.26 rows=141 width=16) (actual time=0.011..0.030 rows=12 loops=1) | |
Index Cond: (asset_id = ams_asset.id) | |
-> Index Scan using amsnew_msg_pkey on ams_msg pn_msg (cost=0.43..7.70 rows=1 width=12) (actual time=0.008..0.008 rows=0 loops=12) | |
Index Cond: (id = ams_msg_asset.msg_id) | |
Filter: ((message_type = 'structured'::ams_message_type) AND (structured_type = 'pursuit_notification'::ams_structured_type)) | |
Rows Removed by Filter: 1 | |
-> Index Scan using amsnew_msg_pkey on ams_msg oi_msg (cost=0.43..8.45 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=3) | |
Index Cond: (id = pn_msg.parent_id) | |
Filter: (date >= '2014-06-05 00:00:00-04'::timestamp with time zone) | |
Rows Removed by Filter: 0 | |
-> Nested Loop (cost=0.43..2792.16 rows=729 width=12) (actual time=0.015..0.021 rows=1 loops=4) | |
-> WorkTable Scan on pn_downstream_thread pn_downstream_thread_1 (cost=0.00..0.20 rows=10 width=8) (actual time=0.001..0.002 rows=2 loops=4) | |
-> Index Scan using ix_ams_msg_parent_id on ams_msg pr_msg (cost=0.43..278.47 rows=73 width=12) (actual time=0.007..0.008 rows=1 loops=6) | |
Index Cond: (parent_id = pn_downstream_thread_1.id) | |
Total runtime: 0.579 ms | |
(23 rows) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH RECURSIVE pn_downstream_thread(id, parent_id) AS | |
(SELECT pn_msg.id AS id, pn_msg.parent_id AS parent_id | |
FROM ams_msg AS pn_msg JOIN ams_msg_asset ON pn_msg.id = ams_msg_asset.msg_id JOIN ams_asset ON ams_asset.id = ams_msg_asset.asset_id JOIN ams_msg AS oi_msg ON oi_msg.id = pn_msg.parent_id | |
WHERE pn_msg.message_type = 'structured' AND pn_msg.structured_type = 'pursuit_notification' AND ams_asset.typ = 'tp' AND ams_asset.public_id = '59c89bdcaf6711e3b67f12313b0a607d' AND oi_msg.date >= '2014-06-05T00:00:00' UNION SELECT pr_msg.id AS id, pr_msg.parent_id AS parent_id | |
FROM ams_msg AS pr_msg JOIN pn_downstream_thread ON pr_msg.parent_id = pn_downstream_thread.id) | |
SELECT pn_downstream_thread.id | |
FROM pn_downstream_thread; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
QUERY PLAN | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
Nested Loop (cost=251.44..259.48 rows=1 width=299) (actual time=2.872..2.888 rows=2 loops=1) | |
-> HashAggregate (cost=251.01..251.02 rows=1 width=8) (actual time=2.859..2.860 rows=2 loops=1) | |
-> HashAggregate (cost=250.99..251.00 rows=1 width=40) (actual time=2.848..2.850 rows=2 loops=1) | |
-> Nested Loop (cost=4.04..250.99 rows=1 width=40) (actual time=1.597..2.829 rows=3 loops=1) | |
-> Nested Loop (cost=3.61..234.60 rows=2 width=16) (actual time=1.569..2.760 rows=6 loops=1) | |
Join Filter: (pr_msg.id = pr_msg_asset.msg_id) | |
-> Nested Loop (cost=3.18..230.24 rows=1 width=16) (actual time=1.555..2.694 rows=3 loops=1) | |
Join Filter: (pr_thread.user_id = pr_user.user_ptr_id) | |
Rows Removed by Join Filter: 915 | |
-> Nested Loop (cost=1.30..155.18 rows=6 width=24) (actual time=0.046..0.207 rows=6 loops=1) | |
-> Nested Loop (cost=0.87..105.96 rows=6 width=24) (actual time=0.035..0.137 rows=6 loops=1) | |
-> Index Only Scan using amsnew_msg_pkey on ams_msg pr_msg (cost=0.43..33.84 rows=6 width=8) (actual time=0.014..0.047 rows=6 loops=1) | |
Index Cond: (id = ANY ('{2648995,2648996,2648997,2648998,2649000,2649002}'::bigint[])) | |
Heap Fetches: 6 | |
-> Index Scan using idx_16556_unique_thread_ref on ams_ref pr_ref (cost=0.43..12.00 rows=2 width=16) (actual time=0.008..0.011 rows=1 loops=6) | |
Index Cond: (msg_id = pr_msg.id) | |
Filter: (header = 'from'::text) | |
Rows Removed by Filter: 2 | |
-> Index Scan using amsnew_thread_pkey on ams_thread pr_thread (cost=0.43..8.19 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=6) | |
Index Cond: (id = pr_ref.thread_id) | |
-> Materialize (cost=1.88..74.01 rows=12 width=8) (actual time=0.013..0.280 rows=153 loops=6) | |
-> Nested Loop (cost=1.88..73.95 rows=12 width=8) (actual time=0.067..0.813 rows=153 loops=1) | |
-> Nested Loop (cost=1.59..62.13 rows=1 width=8) (actual time=0.045..0.131 rows=2 loops=1) | |
-> Nested Loop (cost=1.30..54.09 rows=1 width=8) (actual time=0.033..0.103 rows=2 loops=1) | |
-> Nested Loop (cost=0.87..45.89 rows=1 width=8) (actual time=0.021..0.074 rows=2 loops=1) | |
-> Index Scan using amsnew_msg_pkey on ams_msg pn_msg (cost=0.43..33.87 rows=1 width=8) (actual time=0.010..0.043 rows=2 loops=1) | |
Index Cond: (id = ANY ('{2648995,2648996,2648997,2648998,2649000,2649002}'::bigint[])) | |
Filter: ((message_type = 'structured'::ams_message_type) AND (structured_type = 'pursuit_notification'::ams_structured_type)) | |
Rows Removed by Filter: 4 | |
-> Index Scan using idx_16556_unique_thread_ref on ams_ref pn_ref (cost=0.43..12.00 rows=2 width=16) (actual time=0.007..0.010 rows=1 loops=2) | |
Index Cond: (msg_id = pn_msg.id) | |
Filter: (header = 'to'::text) | |
Rows Removed by Filter: 2 | |
-> Index Scan using amsnew_thread_pkey on ams_thread pn_thread (cost=0.43..8.19 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=2) | |
Index Cond: (id = pn_ref.thread_id) | |
-> Index Scan using auth_axialuser_pkey on auth_axialuser pn_user (cost=0.29..8.02 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=2) | |
Index Cond: (user_ptr_id = pn_thread.user_id) | |
-> Index Scan using idx_16590_auth_axialuser_543518c6 on auth_axialuser pr_user (cost=0.29..11.80 rows=2 width=16) (actual time=0.012..0.215 rows=76 loops=2) | |
Index Cond: (company_id = pn_user.company_id) | |
-> Index Only Scan using amsnew_msg_asset_pkey on ams_msg_asset pr_msg_asset (cost=0.43..4.34 rows=2 width=16) (actual time=0.007..0.014 rows=2 loops=3) | |
Index Cond: (msg_id = pr_ref.msg_id) | |
Heap Fetches: 6 | |
-> Index Scan using amsnew_asset_pkey on ams_asset pr_asset (cost=0.42..8.18 rows=1 width=40) (actual time=0.007..0.008 rows=0 loops=6) | |
Index Cond: (id = pr_msg_asset.asset_id) | |
Filter: (typ = 'opp'::text) | |
Rows Removed by Filter: 0 | |
-> Index Scan using amsnew_msg_pkey on ams_msg (cost=0.43..8.45 rows=1 width=299) (actual time=0.007..0.008 rows=1 loops=2) | |
Index Cond: (id = (min(pr_msg.id))) | |
Total runtime: 3.271 ms | |
(49 rows) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT ams_msg.* | |
FROM ams_msg | |
WHERE ams_msg.id IN (SELECT min(pr_msg.id) AS id | |
FROM ams_msg AS pr_msg JOIN ams_ref AS pr_ref ON pr_msg.id = pr_ref.msg_id JOIN ams_thread AS pr_thread ON pr_ref.thread_id = pr_thread.id JOIN auth_axialuser AS pr_user ON pr_thread.user_id = pr_user.user_ptr_id JOIN auth_axialuser AS pn_user ON pn_user.company_id = pr_user.company_id JOIN ams_thread AS pn_thread ON pn_thread.user_id = pn_user.user_ptr_id JOIN ams_ref AS pn_ref ON pn_ref.thread_id = pn_thread.id JOIN ams_msg AS pn_msg ON pn_ref.msg_id = pn_msg.id JOIN ams_msg_asset AS pr_msg_asset ON pr_msg.id = pr_msg_asset.msg_id JOIN ams_asset AS pr_asset ON pr_msg_asset.asset_id = pr_asset.id | |
WHERE pn_msg.message_type = 'structured' AND pn_msg.structured_type = 'pursuit_notification' AND pn_ref.header = 'to' AND pr_ref.header = 'from' AND pr_msg.id IN (2648995, 2648996, 2648997, 2648998, 2649000, 2649002) AND pn_msg.id IN (2648995, 2648996, 2648997, 2648998, 2649000, 2649002) AND pr_asset.typ = 'opp' GROUP BY pr_asset.public_id); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
QUERY PLAN | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
Nested Loop (cost=483389.42..483397.46 rows=1 width=299) (actual time=700374.934..700374.954 rows=2 loops=1) | |
CTE pn_downstream_thread | |
-> Recursive Union (cost=1.71..28135.55 rows=7291 width=12) (actual time=0.126..0.473 rows=6 loops=1) | |
-> Nested Loop (cost=1.71..68.15 rows=1 width=12) (actual time=0.118..0.342 rows=2 loops=1) | |
-> Nested Loop (cost=1.28..59.69 rows=1 width=12) (actual time=0.103..0.280 rows=3 loops=1) | |
-> Nested Loop (cost=0.85..21.12 rows=5 width=8) (actual time=0.058..0.108 rows=12 loops=1) | |
-> Index Scan using idx_16485_unique_asset_typ_id on ams_asset (cost=0.42..8.45 rows=1 width=8) (actual time=0.040..0.042 rows=1 loops=1) | |
Index Cond: ((typ = 'tp'::text) AND (public_id = '59c89bdcaf6711e3b67f12313b0a607d'::text)) | |
-> Index Scan using idx_16551_asset_id on ams_msg_asset (cost=0.43..11.26 rows=141 width=16) (actual time=0.011..0.034 rows=12 loops=1) | |
Index Cond: (asset_id = ams_asset.id) | |
-> Index Scan using amsnew_msg_pkey on ams_msg pn_msg_1 (cost=0.43..7.70 rows=1 width=12) (actual time=0.011..0.011 rows=0 loops=12) | |
Index Cond: (id = ams_msg_asset.msg_id) | |
Filter: ((message_type = 'structured'::ams_message_type) AND (structured_type = 'pursuit_notification'::ams_structured_type)) | |
Rows Removed by Filter: 1 | |
-> Index Scan using amsnew_msg_pkey on ams_msg oi_msg (cost=0.43..8.45 rows=1 width=8) (actual time=0.012..0.016 rows=1 loops=3) | |
Index Cond: (id = pn_msg_1.parent_id) | |
Filter: (date >= '2014-06-05 00:00:00-04'::timestamp with time zone) | |
Rows Removed by Filter: 0 | |
-> Nested Loop (cost=0.43..2792.16 rows=729 width=12) (actual time=0.017..0.024 rows=1 loops=4) | |
-> WorkTable Scan on pn_downstream_thread (cost=0.00..0.20 rows=10 width=8) (actual time=0.001..0.002 rows=2 loops=4) | |
-> Index Scan using ix_ams_msg_parent_id on ams_msg pr_msg_1 (cost=0.43..278.47 rows=73 width=12) (actual time=0.008..0.009 rows=1 loops=6) | |
Index Cond: (parent_id = pn_downstream_thread.id) | |
-> HashAggregate (cost=455253.44..455253.45 rows=1 width=8) (actual time=700374.910..700374.912 rows=2 loops=1) | |
-> HashAggregate (cost=455253.42..455253.43 rows=1 width=40) (actual time=700374.896..700374.898 rows=2 loops=1) | |
-> Nested Loop (cost=455045.13..455253.41 rows=1 width=40) (actual time=700374.790..700374.870 rows=3 loops=1) | |
-> Hash Join (cost=455044.71..455245.22 rows=1 width=16) (actual time=700374.767..700374.789 rows=6 loops=1) | |
Hash Cond: (cte_1.id = pn_ref.msg_id) | |
-> CTE Scan on pn_downstream_thread cte_1 (cost=0.00..145.82 rows=7291 width=8) (actual time=0.130..0.139 rows=6 loops=1) | |
-> Hash (cost=455042.62..455042.62 rows=167 width=32) (actual time=700374.614..700374.614 rows=10 loops=1) | |
Buckets: 1024 Batches: 1 Memory Usage: 1kB | |
-> Nested Loop (cost=454436.25..455042.62 rows=167 width=32) (actual time=700374.496..700374.591 rows=10 loops=1) | |
-> Merge Join (cost=454435.82..454628.24 rows=95 width=40) (actual time=700374.442..700374.475 rows=5 loops=1) | |
Merge Cond: (pr_msg.id = cte_2.id) | |
-> Sort (cost=453822.21..453899.72 rows=31002 width=32) (actual time=622310.591..667805.360 rows=26231614 loops=1) | |
Sort Key: pr_msg.id | |
Sort Method: external merge Disk: 1077000kB | |
-> Hash Join (cost=318038.61..450767.45 rows=31002 width=32) (actual time=306327.528..396943.344 rows=26231614 loops=1) | |
Hash Cond: (pr_msg.id = pr_ref.msg_id) | |
-> Seq Scan on ams_msg pr_msg (cost=0.00..89947.41 rows=2374141 width=8) (actual time=0.263..4658.827 rows=2374141 loops=1) | |
-> Hash (cost=317469.09..317469.09 rows=31002 width=24) (actual time=306237.993..306237.993 rows=26231614 loops=1) | |
Buckets: 2048 Batches: 2048 (originally 2) Memory Usage: 1025kB | |
-> Hash Join (cost=155156.55..317469.09 rows=31002 width=24) (actual time=132410.113..263949.902 rows=26231614 loops=1) | |
Hash Cond: (pr_ref.thread_id = pr_thread.id) | |
-> Seq Scan on ams_ref pr_ref (cost=0.00..129630.14 rows=2370235 width=16) (actual time=0.015..13992.997 rows=2374141 loops=1) | |
Filter: (header = 'from'::text) | |
Rows Removed by Filter: 2378202 | |
-> Hash (cost=154104.02..154104.02 rows=57322 width=24) (actual time=131698.305..131698.305 rows=26078188 loops=1) | |
Buckets: 2048 Batches: 2048 (originally 4) Memory Usage: 1025kB | |
-> Hash Join (cost=2953.57..154104.02 rows=57322 width=24) (actual time=5513.152..77126.605 rows=26078188 loops=1) | |
Hash Cond: (pr_thread.user_id = pr_user.user_ptr_id) | |
-> Seq Scan on ams_thread pr_thread (cost=0.00..134142.62 rows=4382562 width=16) (actual time=0.004..16796.421 rows=4383993 loops=1) | |
-> Hash (cost=2944.69..2944.69 rows=710 width=24) (actual time=5512.309..5512.309 rows=29576 loops=1) | |
Buckets: 1024 Batches: 2 (originally 1) Memory Usage: 1025kB | |
-> Nested Loop (cost=444.65..2944.69 rows=710 width=24) (actual time=1089.878..5451.100 rows=29576 loops=1) | |
-> Nested Loop (cost=444.36..2341.93 rows=51 width=24) (actual time=1079.548..5112.162 rows=4433 loops=1) | |
-> Nested Loop (cost=444.07..1867.94 rows=59 width=24) (actual time=1079.478..5051.058 rows=4440 loops=1) | |
-> Nested Loop (cost=443.64..1383.99 rows=59 width=24) (actual time=1052.732..3461.278 rows=4440 loops=1) | |
-> Bitmap Heap Scan on ams_msg pn_msg (cost=443.20..674.81 rows=59 width=8) (actual time=1015.754..1039.104 rows=4438 loops=1) | |
Recheck Cond: ((structured_type = 'pursuit_notification'::ams_structured_type) AND (message_type = 'structured'::ams_message_type)) | |
-> BitmapAnd (cost=443.20..443.20 rows=59 width=0) (actual time=1015.538..1015.538 rows=0 loops=1) | |
-> Bitmap Index Scan on ix_ams_msg_structured_type (cost=0.00..221.46 rows=11871 width=0) (actual time=1.298..1.298 rows=4438 loops=1) | |
Index Cond: (structured_type = 'pursuit_notification'::ams_structured_type) | |
-> Bitmap Index Scan on ix_ams_msg_message_type (cost=0.00..221.46 rows=11871 width=0) (actual time=1014.074..1014.074 rows=2354849 loops=1) | |
Index Cond: (message_type = 'structured'::ams_message_type) | |
-> Index Scan using idx_16556_unique_thread_ref on ams_ref pn_ref (cost=0.43..12.00 rows=2 width=16) (actual time=0.437..0.541 rows=1 loops=4438) | |
Index Cond: (msg_id = pn_msg.id) | |
Filter: (header = 'to'::text) | |
Rows Removed by Filter: 1 | |
-> Index Scan using amsnew_thread_pkey on ams_thread pn_thread (cost=0.43..8.19 rows=1 width=16) (actual time=0.352..0.354 rows=1 loops=4440) | |
Index Cond: (id = pn_ref.thread_id) | |
-> Index Scan using auth_axialuser_pkey on auth_axialuser pn_user (cost=0.29..8.02 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=4440) | |
Index Cond: (user_ptr_id = pn_thread.user_id) | |
-> Index Scan using idx_16590_auth_axialuser_543518c6 on auth_axialuser pr_user (cost=0.29..11.80 rows=2 width=16) (actual time=0.046..0.062 rows=7 loops=4433) | |
Index Cond: (company_id = pn_user.company_id) | |
-> Sort (cost=613.61..631.83 rows=7291 width=8) (actual time=0.405..0.413 rows=8 loops=1) | |
Sort Key: cte_2.id | |
Sort Method: quicksort Memory: 25kB | |
-> CTE Scan on pn_downstream_thread cte_2 (cost=0.00..145.82 rows=7291 width=8) (actual time=0.006..0.369 rows=6 loops=1) | |
-> Index Only Scan using amsnew_msg_asset_pkey on ams_msg_asset pr_msg_asset (cost=0.43..4.34 rows=2 width=16) (actual time=0.012..0.015 rows=2 loops=5) | |
Index Cond: (msg_id = pr_msg.id) | |
Heap Fetches: 10 | |
-> Index Scan using amsnew_asset_pkey on ams_asset pr_asset (cost=0.42..8.18 rows=1 width=40) (actual time=0.009..0.009 rows=0 loops=6) | |
Index Cond: (id = pr_msg_asset.asset_id) | |
Filter: (typ = 'opp'::text) | |
Rows Removed by Filter: 0 | |
-> Index Scan using amsnew_msg_pkey on ams_msg (cost=0.43..8.45 rows=1 width=299) (actual time=0.013..0.014 rows=1 loops=2) | |
Index Cond: (id = (min(pr_msg.id))) | |
Total runtime: 700954.434 ms | |
(88 rows) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH RECURSIVE pn_downstream_thread(id, parent_id) AS | |
(SELECT pn_msg.id AS id, pn_msg.parent_id AS parent_id | |
FROM ams_msg AS pn_msg JOIN ams_msg_asset ON pn_msg.id = ams_msg_asset.msg_id JOIN ams_asset ON ams_asset.id = ams_msg_asset.asset_id JOIN ams_msg AS oi_msg ON oi_msg.id = pn_msg.parent_id | |
WHERE pn_msg.message_type = 'structured' AND pn_msg.structured_type = 'pursuit_notification' AND ams_asset.typ = 'tp' AND ams_asset.public_id = '59c89bdcaf6711e3b67f12313b0a607d' AND oi_msg.date >= '2014-06-05T00:00:00' UNION SELECT pr_msg.id AS id, pr_msg.parent_id AS parent_id | |
FROM ams_msg AS pr_msg JOIN pn_downstream_thread ON pr_msg.parent_id = pn_downstream_thread.id) | |
SELECT ams_msg.* | |
FROM ams_msg | |
WHERE ams_msg.id IN (SELECT min(pr_msg.id) AS id | |
FROM ams_msg AS pr_msg JOIN ams_ref AS pr_ref ON pr_msg.id = pr_ref.msg_id JOIN ams_thread AS pr_thread ON pr_ref.thread_id = pr_thread.id JOIN auth_axialuser AS pr_user ON pr_thread.user_id = pr_user.user_ptr_id JOIN auth_axialuser AS pn_user ON pn_user.company_id = pr_user.company_id JOIN ams_thread AS pn_thread ON pn_thread.user_id = pn_user.user_ptr_id JOIN ams_ref AS pn_ref ON pn_ref.thread_id = pn_thread.id JOIN ams_msg AS pn_msg ON pn_ref.msg_id = pn_msg.id JOIN ams_msg_asset AS pr_msg_asset ON pr_msg.id = pr_msg_asset.msg_id JOIN ams_asset AS pr_asset ON pr_msg_asset.asset_id = pr_asset.id | |
JOIN pn_downstream_thread as cte_1 ON pn_msg.id = cte_1.id | |
JOIN pn_downstream_thread as cte_2 ON pr_msg.id = cte_2.id | |
WHERE pn_msg.message_type = 'structured' AND pn_msg.structured_type = 'pursuit_notification' AND pn_ref.header = 'to' AND pr_ref.header = 'from' AND pr_asset.typ = 'opp' GROUP BY pr_asset.public_id); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
QUERY PLAN | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
Nested Loop (cost=512435.29..514092.87 rows=1187070 width=299) (actual time=379071.802..379071.823 rows=2 loops=1) | |
CTE pn_downstream_thread | |
-> Recursive Union (cost=1.71..28135.55 rows=7291 width=12) (actual time=0.170..0.489 rows=6 loops=1) | |
-> Nested Loop (cost=1.71..68.15 rows=1 width=12) (actual time=0.158..0.356 rows=2 loops=1) | |
-> Nested Loop (cost=1.28..59.69 rows=1 width=12) (actual time=0.133..0.302 rows=3 loops=1) | |
-> Nested Loop (cost=0.85..21.12 rows=5 width=8) (actual time=0.074..0.114 rows=12 loops=1) | |
-> Index Scan using idx_16485_unique_asset_typ_id on ams_asset (cost=0.42..8.45 rows=1 width=8) (actual time=0.048..0.049 rows=1 loops=1) | |
Index Cond: ((typ = 'tp'::text) AND (public_id = '59c89bdcaf6711e3b67f12313b0a607d'::text)) | |
-> Index Scan using idx_16551_asset_id on ams_msg_asset (cost=0.43..11.26 rows=141 width=16) (actual time=0.014..0.030 rows=12 loops=1) | |
Index Cond: (asset_id = ams_asset.id) | |
-> Index Scan using amsnew_msg_pkey on ams_msg pn_msg_1 (cost=0.43..7.70 rows=1 width=12) (actual time=0.013..0.013 rows=0 loops=12) | |
Index Cond: (id = ams_msg_asset.msg_id) | |
Filter: ((message_type = 'structured'::ams_message_type) AND (structured_type = 'pursuit_notification'::ams_structured_type)) | |
Rows Removed by Filter: 1 | |
-> Index Scan using amsnew_msg_pkey on ams_msg oi_msg (cost=0.43..8.45 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=3) | |
Index Cond: (id = pn_msg_1.parent_id) | |
Filter: (date >= '2014-06-05 00:00:00-04'::timestamp with time zone) | |
Rows Removed by Filter: 0 | |
-> Nested Loop (cost=0.43..2792.16 rows=729 width=12) (actual time=0.017..0.024 rows=1 loops=4) | |
-> WorkTable Scan on pn_downstream_thread pn_downstream_thread_2 (cost=0.00..0.20 rows=10 width=8) (actual time=0.002..0.003 rows=2 loops=4) | |
-> Index Scan using ix_ams_msg_parent_id on ams_msg pr_msg_1 (cost=0.43..278.47 rows=73 width=12) (actual time=0.008..0.009 rows=1 loops=6) | |
Index Cond: (parent_id = pn_downstream_thread_2.id) | |
-> HashAggregate (cost=484299.31..484301.31 rows=200 width=8) (actual time=379071.753..379071.756 rows=2 loops=1) | |
-> HashAggregate (cost=484286.30..484292.08 rows=578 width=40) (actual time=379071.729..379071.739 rows=2 loops=1) | |
-> Hash Join (cost=467127.91..484283.41 rows=578 width=40) (actual time=379071.557..379071.701 rows=3 loops=1) | |
Hash Cond: (pr_msg_asset.asset_id = pr_asset.id) | |
-> Hash Join (cost=451410.54..468055.19 rows=13610 width=16) (actual time=378945.800..378945.923 rows=6 loops=1) | |
Hash Cond: (pr_msg_asset.msg_id = pr_msg.id) | |
-> Nested Loop (cost=164.48..1039.46 rows=2084476 width=24) (actual time=0.552..0.720 rows=12 loops=1) | |
-> HashAggregate (cost=164.05..166.05 rows=200 width=8) (actual time=0.527..0.539 rows=6 loops=1) | |
-> CTE Scan on pn_downstream_thread (cost=0.00..145.82 rows=7291 width=8) (actual time=0.175..0.507 rows=6 loops=1) | |
-> Index Only Scan using amsnew_msg_asset_pkey on ams_msg_asset pr_msg_asset (cost=0.43..4.35 rows=2 width=16) (actual time=0.016..0.021 rows=2 loops=6) | |
Index Cond: (msg_id = pn_downstream_thread.id) | |
Heap Fetches: 12 | |
-> Hash (cost=451052.30..451052.30 rows=15501 width=16) (actual time=378945.150..378945.150 rows=2396 loops=1) | |
Buckets: 2048 Batches: 1 Memory Usage: 113kB | |
-> Hash Join (cost=318207.16..451052.30 rows=15501 width=16) (actual time=275956.121..378936.272 rows=2396 loops=1) | |
Hash Cond: (pn_ref.msg_id = pn_downstream_thread_1.id) | |
-> Hash Join (cost=318038.61..450767.45 rows=31002 width=32) (actual time=273432.709..346500.045 rows=26231614 loops=1) | |
Hash Cond: (pr_msg.id = pr_ref.msg_id) | |
-> Seq Scan on ams_msg pr_msg (cost=0.00..89947.41 rows=2374141 width=8) (actual time=0.020..3929.575 rows=2374141 loops=1) | |
-> Hash (cost=317469.09..317469.09 rows=31002 width=24) (actual time=273355.257..273355.257 rows=26231614 loops=1) | |
Buckets: 2048 Batches: 2048 (originally 2) Memory Usage: 1025kB | |
-> Hash Join (cost=155156.55..317469.09 rows=31002 width=24) (actual time=124914.036..223312.662 rows=26231614 loops=1) | |
Hash Cond: (pr_ref.thread_id = pr_thread.id) | |
-> Seq Scan on ams_ref pr_ref (cost=0.00..129630.14 rows=2370235 width=16) (actual time=0.010..13122.603 rows=2374141 loops=1) | |
Filter: (header = 'from'::text) | |
Rows Removed by Filter: 2378202 | |
-> Hash (cost=154104.02..154104.02 rows=57322 width=24) (actual time=124778.594..124778.594 rows=26078188 loops=1) | |
Buckets: 2048 Batches: 2048 (originally 4) Memory Usage: 1025kB | |
-> Hash Join (cost=2953.57..154104.02 rows=57322 width=24) (actual time=3043.653..74317.926 rows=26078188 loops=1) | |
Hash Cond: (pr_thread.user_id = pr_user.user_ptr_id) | |
-> Seq Scan on ams_thread pr_thread (cost=0.00..134142.62 rows=4382562 width=16) (actual time=0.007..18718.597 rows=4383993 loops=1) | |
-> Hash (cost=2944.69..2944.69 rows=710 width=24) (actual time=3043.531..3043.531 rows=29576 loops=1) | |
Buckets: 1024 Batches: 2 (originally 1) Memory Usage: 1025kB | |
-> Nested Loop (cost=444.65..2944.69 rows=710 width=24) (actual time=1004.410..2996.868 rows=29576 loops=1) | |
-> Nested Loop (cost=444.36..2341.93 rows=51 width=24) (actual time=1004.393..2837.055 rows=4433 loops=1) | |
-> Nested Loop (cost=444.07..1867.94 rows=59 width=24) (actual time=1004.373..2784.782 rows=4440 loops=1) | |
-> Nested Loop (cost=443.64..1383.99 rows=59 width=24) (actual time=1004.350..1565.038 rows=4440 loops=1) | |
-> Bitmap Heap Scan on ams_msg pn_msg (cost=443.20..674.81 rows=59 width=8) (actual time=1004.309..1017.421 rows=4438 loops=1) | |
Recheck Cond: ((structured_type = 'pursuit_notification'::ams_structured_type) AND (message_type = 'structured'::ams_message_type)) | |
-> BitmapAnd (cost=443.20..443.20 rows=59 width=0) (actual time=1004.116..1004.116 rows=0 loops=1) | |
-> Bitmap Index Scan on ix_ams_msg_structured_type (cost=0.00..221.46 rows=11871 width=0) (actual time=1.056..1.056 rows=4438 loops=1) | |
Index Cond: (structured_type = 'pursuit_notification'::ams_structured_type) | |
-> Bitmap Index Scan on ix_ams_msg_message_type (cost=0.00..221.46 rows=11871 width=0) (actual time=1002.880..1002.880 rows=2354849 loops=1) | |
Index Cond: (message_type = 'structured'::ams_message_type) | |
-> Index Scan using idx_16556_unique_thread_ref on ams_ref pn_ref (cost=0.43..12.00 rows=2 width=16) (actual time=0.098..0.119 rows=1 loops=4438) | |
Index Cond: (msg_id = pn_msg.id) | |
Filter: (header = 'to'::text) | |
Rows Removed by Filter: 1 | |
-> Index Scan using amsnew_thread_pkey on ams_thread pn_thread (cost=0.43..8.19 rows=1 width=16) (actual time=0.270..0.271 rows=1 loops=4440) | |
Index Cond: (id = pn_ref.thread_id) | |
-> Index Scan using auth_axialuser_pkey on auth_axialuser pn_user (cost=0.29..8.02 rows=1 width=16) (actual time=0.006..0.008 rows=1 loops=4440) | |
Index Cond: (user_ptr_id = pn_thread.user_id) | |
-> Index Scan using idx_16590_auth_axialuser_543518c6 on auth_axialuser pr_user (cost=0.29..11.80 rows=2 width=16) (actual time=0.008..0.022 rows=7 loops=4433) | |
Index Cond: (company_id = pn_user.company_id) | |
-> Hash (cost=166.05..166.05 rows=200 width=8) (actual time=0.042..0.042 rows=6 loops=1) | |
Buckets: 1024 Batches: 1 Memory Usage: 1kB | |
-> HashAggregate (cost=164.05..166.05 rows=200 width=8) (actual time=0.025..0.033 rows=6 loops=1) | |
-> CTE Scan on pn_downstream_thread pn_downstream_thread_1 (cost=0.00..145.82 rows=7291 width=8) (actual time=0.002..0.008 rows=6 loops=1) | |
-> Hash (cost=15018.62..15018.62 rows=34380 width=40) (actual time=100.730..100.730 rows=34318 loops=1) | |
Buckets: 2048 Batches: 4 Memory Usage: 675kB | |
-> Bitmap Heap Scan on ams_asset pr_asset (cost=1378.87..15018.62 rows=34380 width=40) (actual time=10.715..53.246 rows=34318 loops=1) | |
Recheck Cond: (typ = 'opp'::text) | |
-> Bitmap Index Scan on idx_16485_unique_asset_typ_id (cost=0.00..1370.27 rows=34380 width=0) (actual time=10.507..10.507 rows=34318 loops=1) | |
Index Cond: (typ = 'opp'::text) | |
-> Index Scan using amsnew_msg_pkey on ams_msg (cost=0.43..8.27 rows=1 width=299) (actual time=0.022..0.024 rows=1 loops=2) | |
Index Cond: (id = (min(pr_msg.id))) | |
Total runtime: 379072.791 ms | |
(89 rows) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH RECURSIVE pn_downstream_thread(id, parent_id) AS | |
(SELECT pn_msg.id AS id, pn_msg.parent_id AS parent_id | |
FROM ams_msg AS pn_msg JOIN ams_msg_asset ON pn_msg.id = ams_msg_asset.msg_id JOIN ams_asset ON ams_asset.id = ams_msg_asset.asset_id JOIN ams_msg AS oi_msg ON oi_msg.id = pn_msg.parent_id | |
WHERE pn_msg.message_type = 'structured' AND pn_msg.structured_type = 'pursuit_notification' AND ams_asset.typ = 'tp' AND ams_asset.public_id = '59c89bdcaf6711e3b67f12313b0a607d' AND oi_msg.date >= '2014-06-05T00:00:00' UNION SELECT pr_msg.id AS id, pr_msg.parent_id AS parent_id | |
FROM ams_msg AS pr_msg JOIN pn_downstream_thread ON pr_msg.parent_id = pn_downstream_thread.id) | |
SELECT ams_msg.* | |
FROM ams_msg | |
WHERE ams_msg.id IN (SELECT min(pr_msg.id) AS id | |
FROM ams_msg AS pr_msg JOIN ams_ref AS pr_ref ON pr_msg.id = pr_ref.msg_id JOIN ams_thread AS pr_thread ON pr_ref.thread_id = pr_thread.id JOIN auth_axialuser AS pr_user ON pr_thread.user_id = pr_user.user_ptr_id JOIN auth_axialuser AS pn_user ON pn_user.company_id = pr_user.company_id JOIN ams_thread AS pn_thread ON pn_thread.user_id = pn_user.user_ptr_id JOIN ams_ref AS pn_ref ON pn_ref.thread_id = pn_thread.id JOIN ams_msg AS pn_msg ON pn_ref.msg_id = pn_msg.id JOIN ams_msg_asset AS pr_msg_asset ON pr_msg.id = pr_msg_asset.msg_id JOIN ams_asset AS pr_asset ON pr_msg_asset.asset_id = pr_asset.id | |
WHERE pn_msg.message_type = 'structured' AND pn_msg.structured_type = 'pursuit_notification' AND pn_ref.header = 'to' AND pr_ref.header = 'from' AND pr_msg.id IN (SELECT pn_downstream_thread.id FROM pn_downstream_thread) AND pn_msg.id IN (SELECT pn_downstream_thread.id FROM pn_downstream_thread) AND pr_asset.typ = 'opp' GROUP BY pr_asset.public_id); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Table "public.ams_msg" | |
Column | Type | Modifiers | |
--------------------------+------------------------------+--------------------------------------------------------- | |
id | bigint | not null default nextval('amsnew_msg_id_seq'::regclass) | |
public_id | text | not null | |
smtp_id | text | not null | |
original_id | text | | |
date | timestamp with time zone | | |
scheduled_mailbox | text | | |
user_msg_id | bigint | | |
user_id | bigint | | |
ext_email_id | bigint | | |
subject | text | | |
created_on | timestamp with time zone | not null | |
parent_id | integer | | |
message_type | ams_message_type | | |
unstructured_type | ams_unstructured_type | | |
structured_type | ams_structured_type | | |
system_notification_type | ams_system_notification_type | | |
Indexes: | |
"amsnew_msg_pkey" PRIMARY KEY, btree (id) | |
"idx_16544_ix_amsnew_msg_public_id" UNIQUE, btree (public_id) | |
"idx_16544_original_id" UNIQUE, btree (original_id) | |
"idx_16544_smtp_id" UNIQUE, btree (smtp_id) | |
"idx_16544_user_msg_id" UNIQUE, btree (user_msg_id) | |
"idx_16544_ext_email_id" btree (ext_email_id) | |
"idx_16544_user_id" btree (user_id) | |
"ix_ams_msg_message_type" btree (message_type) | |
"ix_ams_msg_parent_id" btree (parent_id) | |
"ix_ams_msg_structured_type" btree (structured_type) | |
"ix_ams_msg_system_notification_type" btree (system_notification_type) | |
"ix_ams_msg_unstructured_type" btree (unstructured_type) | |
Foreign-key constraints: | |
"ams_msg_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES ams_msg(id) | |
"amsnew_msg_ibfk_1" FOREIGN KEY (user_msg_id) REFERENCES message_center_usermessage(id) | |
"amsnew_msg_ibfk_2" FOREIGN KEY (user_id) REFERENCES auth_axialuser(user_ptr_id) | |
"amsnew_msg_ibfk_3" FOREIGN KEY (ext_email_id) REFERENCES ams_ext_email(id) | |
Table "public.ams_ref" | |
Column | Type | Modifiers | |
-------------+--------+--------------------------------------------------------- | |
id | bigint | not null default nextval('amsnew_ref_id_seq'::regclass) | |
public_id | text | not null | |
thread_id | bigint | not null | |
msg_id | bigint | not null | |
status | text | not null | |
sub_status | text | not null | |
ext_smtp_id | text | | |
checkpoint | text | | |
header | text | not null | |
bounce_id | bigint | | |
inbound_id | bigint | | |
Indexes: | |
"amsnew_ref_pkey" PRIMARY KEY, btree (id) | |
"idx_16556_public_id" UNIQUE, btree (public_id) | |
"idx_16556_unique_thread_ref" UNIQUE, btree (msg_id, thread_id) | |
"idx_16556_ams_ref_inbound_fk" btree (inbound_id) | |
"idx_16556_bounce_id" btree (bounce_id) | |
"idx_16556_ix_amsnew_ref_ext_smtp_id" btree (ext_smtp_id) | |
"idx_16556_ix_amsnew_ref_header" btree (header) | |
"idx_16556_ix_amsnew_ref_status" btree (status) | |
"idx_16556_thread_id" btree (thread_id) | |
Foreign-key constraints: | |
"ams_ref_inbound_fk" FOREIGN KEY (inbound_id) REFERENCES ams_inbound(id) | |
"amsnew_ref_ibfk_1" FOREIGN KEY (thread_id) REFERENCES ams_thread(id) | |
"amsnew_ref_ibfk_2" FOREIGN KEY (msg_id) REFERENCES ams_msg(id) | |
"amsnew_ref_ibfk_3" FOREIGN KEY (bounce_id) REFERENCES ams_bounce(id) | |
Table "public.ams_thread" | |
Column | Type | Modifiers | |
--------------+--------------------------+------------------------------------------------------------ | |
id | bigint | not null default nextval('amsnew_thread_id_seq'::regclass) | |
public_id | text | not null | |
subject | text | | |
important | boolean | not null | |
user_id | bigint | | |
ext_email_id | bigint | | |
created_on | timestamp with time zone | not null | |
Indexes: | |
"amsnew_thread_pkey" PRIMARY KEY, btree (id) | |
"idx_16565_unique_user_thread" UNIQUE, btree (user_id, public_id, ext_email_id) | |
"idx_16565_ext_email_id" btree (ext_email_id) | |
"idx_16565_ix_amsnew_thread_public_id" btree (public_id) | |
Foreign-key constraints: | |
"amsnew_thread_ibfk_1" FOREIGN KEY (user_id) REFERENCES auth_axialuser(user_ptr_id) | |
"amsnew_thread_ibfk_2" FOREIGN KEY (ext_email_id) REFERENCES ams_ext_email(id) | |
Table "public.auth_axialuser" | |
Column | Type | Modifiers | |
------------------------------+--------------------------+----------------------------------------- | |
user_ptr_id | bigint | not null | |
created_on | timestamp with time zone | not null | |
updated_on | timestamp with time zone | not null | |
company_id | bigint | not null default (2)::bigint | |
phone | text | not null | |
title | text | not null | |
timezone | text | not null default 'US/Eastern'::text | |
salesforce_id | text | | |
mobile_phone | text | not null | |
author_page | text | not null | |
salesforce_sync | boolean | not null | |
salesforce_last_message | text | not null | |
is_email_verified | boolean | not null | |
is_generated_from_opp_invite | boolean | not null | |
tos_date | timestamp with time zone | | |
has_profile_picture | boolean | not null | |
unique_slug_id | bigint | not null | |
registration_source | text | default ''::text | |
slug | text | not null | |
slug_display | text | not null | |
ams_from_email | text | | |
ams_fwd_email | text | | |
company_location_id | bigint | | |
page_rank | numeric(21,20) | not null default 0.00000000000000000000 | |
primary_group_id | bigint | | |
valuation_id | integer | | |
Indexes: | |
"auth_axialuser_pkey" PRIMARY KEY, btree (user_ptr_id) | |
"idx_16590_auth_axialuser_slug" UNIQUE, btree (slug) | |
"idx_16590_primary_group_id_uniq" UNIQUE, btree (primary_group_id) | |
"idx_16590_unique_slug_id" UNIQUE, btree (unique_slug_id) | |
"idx_16590_auth_axialuser_543518c6" btree (company_id) | |
"idx_16590_auth_axialuser_company_location" btree (company_location_id) | |
"idx_16590_auth_axialuser_salesforce_id" btree (salesforce_id) | |
Foreign-key constraints: | |
"auth_axialuser_company_location" FOREIGN KEY (company_location_id) REFERENCES company_location(id) | |
"auth_axialuser_ibfk_1" FOREIGN KEY (primary_group_id) REFERENCES newauth_group(id) | |
"company_id_refs_id_7ff35742" FOREIGN KEY (company_id) REFERENCES company_company(id) | |
"company_id_refs_id_e971af8800ca8be" FOREIGN KEY (company_id) REFERENCES company_company(id) | |
"unique_slug_id_refs_id_30c7ff8b75ddc5ac" FOREIGN KEY (unique_slug_id) REFERENCES common_slug(id) | |
"user_ptr_id_refs_id_2a7cd755" FOREIGN KEY (user_ptr_id) REFERENCES auth_user(id) | |
Table "public.ams_msg_asset" | |
Column | Type | Modifiers | |
----------+--------+----------- | |
msg_id | bigint | not null | |
asset_id | bigint | not null | |
Indexes: | |
"amsnew_msg_asset_pkey" PRIMARY KEY, btree (msg_id, asset_id) | |
"idx_16551_asset_id" btree (asset_id) | |
Foreign-key constraints: | |
"amsnew_msg_asset_ibfk_1" FOREIGN KEY (msg_id) REFERENCES ams_msg(id) | |
"amsnew_msg_asset_ibfk_2" FOREIGN KEY (asset_id) REFERENCES ams_asset(id) | |
Table "public.ams_asset" | |
Column | Type | Modifiers | |
-----------+--------+----------------------------------------------------------- | |
id | bigint | not null default nextval('amsnew_asset_id_seq'::regclass) | |
typ | text | not null | |
public_id | text | not null | |
title | text | not null | |
Indexes: | |
"amsnew_asset_pkey" PRIMARY KEY, btree (id) | |
"idx_16485_unique_asset_typ_id" UNIQUE, btree (typ, public_id) | |
"idx_16485_ix_amsnew_asset_public_id" btree (public_id) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment