explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pqz

Settings
# exclusive inclusive rows x rows loops node
1. 25,053.801 25,053.801 ↓ 2.7 300 1

Hash Right Join (cost=468,397.79..471,146.32 rows=111 width=218) (actual time=25,028.739..25,053.801 rows=300 loops=1)

  • Hash Cond: ((otc.year_week_iso = fr.year_week_iso) AND (otc.workgroup_id = fr.workgroup_id) AND (otc.topic_id = fr.topic_id))
2.          

CTE campaign_start

3. 0.008 0.014 ↑ 1.0 1 1

Aggregate (cost=1.12..1.13 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=1)

4. 0.006 0.006 ↓ 1.4 10 1

Seq Scan on campaign_step (cost=0.00..1.09 rows=7 width=8) (actual time=0.003..0.006 rows=10 loops=1)

  • Filter: (campaign_id = 'ae4f98f5-fd13-b722-2c2c-50d5928391bd'::uuid)
5.          

CTE workgroups

6. 0.008 0.008 ↑ 1.0 1 1

Index Only Scan using "PK_WORKGROUP" on workgroup w_1 (cost=0.27..8.29 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=1)

  • Index Cond: (workgroup_id = '1ceea263-9e29-4c9b-b41f-de49ffa83789'::uuid)
  • Heap Fetches: 0
7.          

CTE dashboard_setting

8. 0.119 0.119 ↑ 3.4 29 1

Function Scan on json_to_recordset y (cost=0.00..1.50 rows=100 width=80) (actual time=0.091..0.119 rows=29 loops=1)

9.          

CTE topics

10. 0.027 0.294 ↑ 3.6 28 1

Hash Join (cost=8.77..11.03 rows=100 width=80) (actual time=0.224..0.294 rows=28 loops=1)

  • Hash Cond: (ds.topic_id = ts.topic_id)
11. 0.142 0.142 ↑ 3.4 29 1

CTE Scan on dashboard_setting ds (cost=0.00..2.00 rows=100 width=80) (actual time=0.093..0.142 rows=29 loops=1)

12. 0.062 0.125 ↑ 1.0 212 1

Hash (cost=6.12..6.12 rows=212 width=16) (actual time=0.125..0.125 rows=212 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
13. 0.063 0.063 ↑ 1.0 212 1

Seq Scan on topic_spec ts (cost=0.00..6.12 rows=212 width=16) (actual time=0.004..0.063 rows=212 loops=1)

14.          

CTE raw_data

15. 33.543 1,502.059 ↑ 2.8 61,551 1

Hash Left Join (cost=41.99..421,439.19 rows=174,111 width=72) (actual time=2.229..1,502.059 rows=61,551 loops=1)

  • Hash Cond: (tqs.question_spec_id = qs.question_spec_id)
16. 44.943 1,468.413 ↑ 2.8 61,551 1

Hash Left Join (cost=35.35..420,092.99 rows=174,111 width=56) (actual time=2.120..1,468.413 rows=61,551 loops=1)

  • Hash Cond: (rm.topic_id = ts_1.topic_id)
17. 1,422.958 1,422.958 ↑ 1.0 61,551 1

Index Scan using response_metrics_idx_2 on response_metrics rm (cost=0.55..418,336.26 rows=62,562 width=40) (actual time=1.602..1,422.958 rows=61,551 loops=1)

  • Index Cond: ((campaign_id = 'ae4f98f5-fd13-b722-2c2c-50d5928391bd'::uuid) AND (workgroup_id = '1ceea263-9e29-4c9b-b41f-de49ffa83789'::uuid))
  • Filter: (party_id = ANY ('{dc4f6d1b-0785-4c16-8667-26e172c748fb,a981f8fc-5792-4b26-999b-db6c0090b628,109ac58a-97bb-4db0-8663-7cc55706e170,17f093d5-e041-4a67-ad85-ff2f630a2573,bac93b0b-5bd4-48ba-834a-10c23642273e,b92b07c0-ee1a-460d-8090-606ffe4fc70c,b020f0ce-a7e1-44ad-831b-7b8988446bd6,58afe001-347d-4541-a534-0f8a66fae14b,2f1dee93-c6d1-4e00-b5bb-20a8bd227a34,5f5508fa-ba17-48fe-a396-d5764dcf6670,356e60fc-2116-48ed-b4ea-076c8b86e8ed,a52779af-dc4f-414a-907b-2c096404a514,55d9ff5e-dc41-4b14-baba-1b5150751076,4f8658e9-4fca-4fbb-ae05-82fe8ec3e35b,ae0fdef6-ed20-4e32-af90-643165345cc4,5c2541ae-4eb5-4d07-8980-f4ab9c66097a,93889874-baf2-4a05-bf2e-22026499ea7b,fba99679-d079-4565-b291-288a6bab5fda,6309be1a-9cfd-4208-8ef5-c1741e503389,d4f8cb6f-f27e-4c1d-8971-86abc401b167,9390dad9-def8-4b05-9b29-0e2f7f14f637,03843b82-8343-42c9-ae24-cdd69548087b,bf829b80-c928-42e2-beab-11d4439e8fbd,133b9c6c-df6e-49c8-87c3-d92d414522b2,6deb88f4-94f0-4bc0-987f-220f3218ad20,dd2cffa0-502e-44de-8012-9f11df42951c,83753b9b-ad36-4799-90d1-e79ab21e21de,684c445e-43f1-4143-8ca9-83533ea91d5f,dbc120f2-c258-4cce-baf1-88757f9fa405,9c092359-bebc-4f0a-bdc2-5c27002651df,3579f30f-180d-4ede-b898-a1de1797b256,98720c09-2df9-4760-b28e-62af8bf4b0b4,887d54df-d906-404b-b634-ddc494ec78c2,a1e8dcfb-60ca-4574-8fad-ba23ef9263d8,83498593-61bf-4bb9-a0d8-02d14f64b3a6,4b265c6b-b4e0-4126-aa3b-03ed634eefa1,596fbbdd-53d0-494f-8d92-1624f47ec666,f51f208c-bb68-4a91-8585-f6d8daa70a47,4a615d41-3def-41f2-8938-7ac56d1923fa,07e74e7c-ef5e-44e7-b0c6-9d46eaa47712,081badba-3487-40c1-ae6a-1c2f526d0438,356848b4-dc62-4834-951b-5c6b4f2d4cac,2e05df90-1d4d-4f09-b462-9187982b48ee,2b57cbe3-27df-4d83-b7dd-5a367fd0aebe,44e3b98b-0ac8-41c1-ade6-cc601ec9a934,ea5adf32-ff2d-4b85-b9fb-8bd49a52ca0a,51c25d81-6041-4ada-a801-3706eddb0e41,a53d50d5-3c5f-45bc-be05-2bec229aaf0c,c5944d11-4e63-438c-a719-6f2be2ac64a3,0e28b447-b1a9-42d4-a63b-c43d574096e7,38d1fcc6-23c6-48fa-8a99-b48aa2aea1f5,871213e1-622b-4c55-8cda-f20d4065c847,704b2595-da94-4cfe-8b87-0c5e03c089bf,110afebb-e2b3-4d32-be6d-83a414e981c9,63abe02a-3c1d-4838-ab81-8fd2bde802d9,061fb89a-f19a-44cf-8a61-3af4e5e6ef10,3b70f68a-22a3-46fe-8505-1115a78c6b5e,1ef56e1b-d964-48e7-b482-05c098bceb27,28c2ab54-768e-40cf-b84b-e7ff687a27e5,bb147b1c-821f-46c7-a89b-479f19735ef9,43190311-c7f4-42ba-aa8c-cab99afbb9f8,cd2d6d10-5858-4a26-b1da-bbf4397667ad,c1e24855-b742-490b-ab5d-81b299172cdd,75216d4b-1da0-42b5-b04e-f02be98de35a,d3a47f9e-6a51-4c25-9a5c-c23523171536,c9fe2c53-64a3-47cb-b795-3c4df40c0afa,b526e15a-5cc8-4137-b09a-fd3c94b7971e,af24c950-86d5-4f6e-b450-1c2e29e7573f,7396af47-9ac0-4ce4-9c4b-d101a5f9ae97,0afa2884-85a4-4937-a3d0-e3323c143933,959fd6cd-e07e-4e87-b675-f3c494ee49ae,792ac725-63c2-498d-af98-cd4194fc95ee,8b8c155f-eb25-4ce4-8d97-cb9500921090,c76b63f7-fc42-43a7-ba94-9275f09f8191,dd0f77eb-5dba-4148-a6c7-d7abf1a48a46,eb467d26-e3cd-46a8-9f69-fb75b4dd3f53,cf093184-aa58-4213-b372-ef300b6d5911,e29ef91c-0161-4b74-b965-e65bf486116b,8fe5ec8e-e6c8-47b2-9867-e53bb8766ea6,6a1a6762-fde1-47cd-85a3-e84eaf642366,9338d44b-b952-450b-93ba-6224e440c72e,9bd6ad08-31d9-4b5e-abf6-d8e69e5bf87e,51c56aa8-c4a4-40e9-8dad-056efb5bfbda,d63322ea-5197-40f4-a214-730454de0932,375eedd0-a5ef-4734-a5ef-7d4d99171850,aa799943-98ae-4f14-86df-83fdc06c943e,c2a4f360-17c1-4f93-b2bc-e1d4e35ccfb1,a587479e-64b1-4f0b-82f2-9a84924a332f,e25c6b7f-de24-4bb9-928d-ef53a7735131,a3a31aa7-ebff-4279-a633-0b3c9f95d8f3,a7e4096b-6569-4817-bee1-4d949e1c1498,b67c04a2-bfba-46e2-a60a-b71a592d5c3c,b7601b81-11dd-4979-bc52-c728e0d50ae6,c3e39841-a329-47b8-a96e-357faeafd029,9bf8ffae-939f-4d86-a49f-c29ff1be715a,1db00e08-5671-445a-bffd-6768ae76b921,cf0b1e8f-1ce0-4948-880a-73400a3f43d7,9bee69e2-8065-43d3-94d6-fcbeefb28628,6c494769-4441-4f29-86b9-2da79b15c2a2,c1af9e0e-d4db-429d-89b1-8e35210e365b,99fb9f9d-9041-4e1c-9a5c-4dfad9ba6a5f,434ac9de-b217-4ba5-a8c8-ce326eda2267,3877a160-916c-432c-9b6e-b4db98f39a8e,b772ca97-9c4b-4567-b719-b70c58dda976,38ce4256-f528-4557-9886-0550ccf9bca6,8a5c71fe-e233-41d4-a9d3-c50c8b81b888,5bd1b795-f133-4dd1-9b71-0b1d7c0e8713,678320a2-2d3b-46f2-ba87-15f07299fa51,183a574f-a47a-4c86-b1de-7e8fb92c8bbb,d39c2854-345e-4c32-9faa-53929545e7fd,8f33c236-e797-450d-adae-fb2d1e49b172,dc97de7e-681b-440b-b4bf-04b9fb083ecb,8f304980-3f91-4762-aa77-877fbc58afc4,0adb21fc-963e-4402-b453-1da3362d2080,08a55cba-455c-42de-b037-3cfaa96e405e,fcda6002-9ac9-46e0-a86d-1e591c4742b1,482d3b4b-2366-4e5f-bae8-52851070849f,8a71ab02-cb5d-4878-81e5-eb53e346a45d,bff4c71d-34c3-4bc8-b1fd-bc3df892827d,be6bb8e2-af95-418a-8d3c-5591865a970c,8100c1cb-d083-42f0-9adf-78f0b07d778d,c1ecf831-a931-4640-a32f-e21ecdafc07c,74b9f30a-457f-43c7-bff7-678fcaa248bd,1468feea-211e-49aa-b0eb-9e9d0cafef8b,9baf2c3c-de8d-4ed4-9a9e-2cc671157edd,0d9437e3-7fb0-4106-ab62-53cbbabbb4e1,84e12a3a-fde6-4076-b1f1-f29bccb25efa,f711d9df-4f1a-4334-9f54-b0ed9c2472b3,0141634f-8abe-4f9d-8670-4f03a90ac201,c6768be8-b4b2-4664-87cb-4b4d6ec90177,927e8551-b168-4088-b947-ca355ba3ac02,461d7b57-57e5-4c27-9eaf-fff640766a07,96b263ca-c23b-4ad7-97e9-1173df57a23d,de8f49d9-a3fd-4fc6-84a5-a89554bcf501,34f670c9-7d74-4199-a9d6-95e05a190170,cbf958fc-34e6-4320-9650-97a6dc0056f1,9e2ab3ff-96b0-4102-8991-52873f3e8494,c77b1652-3be9-4f68-a20d-825912a21fa4,6a2764b6-e084-44a3-9d1d-e3ee8fbe188d,16915a5a-390e-46ae-a496-b51afc5d05a0,45d5a660-b547-4d80-aa7b-a09eddf83dc1,deda5fbb-d4f7-45ef-a557-7104e72832f6,8288ccde-7a98-4612-9eba-8d07ac14aa47,aa3a0cc0-b528-4bbf-b9d5-953fd16a4a5a,9d29b714-c37b-4171-adf7-06e1f980fa56,29b1d991-969e-4a7f-99a0-8f32c703cfa9,eb376040-2f95-4e96-8f0f-a869e9995bc1,1da3ee5d-c57e-44f6-ac99-b1b31466965f,14f4f392-f986-4272-96f2-258b6491ea58,d702778e-65c4-4923-8429-83bf7ae9344d,5ec67b28-06c9-4560-9a35-0138fc1dc90e,7ada70d2-bec1-417b-9c9f-d9dbd45a7882,10bbb77a-5641-40e7-a976-39dfccb24923,771e0b5d-d4cc-438c-a43d-0de73eb2e463,ab2ea1d3-0d76-4b18-b1f8-01132e82d900,793d574f-3dba-41cc-8d41-52afe14400e2,cfb67d23-a12e-4c2e-aaf9-25666bec9e0d,79505ae0-819a-4c00-a319-5bda43e1f4bb,0faf00e5-18e6-4efc-aded-65e4fe211203,d4fae18a-a7f3-4eed-af93-144968c69096,dc81c12a-d04b-435d-9359-6b6cabb1931d,786ddfb6-129d-42ff-b417-3a2b44b9e3d5,3813fb2a-e5a3-4adf-a056-e6f1dcb228ba,bcbeaacd-1c07-4dcf-96ec-a5ec08f04da2,a4619a98-7274-4a21-9359-3250561e9e3c,d1019eac-ee65-4ca5-aa4a-80132ce63a1e,9ab3b2ab-2447-4ae1-990d-0105a37f847f,f9dd8da5-2263-4bd7-89d9-10fc7fd840a6,42c3e23a-a60a-48d7-ab74-75985ba6497b,f4416707-3f81-4aab-805c-3dd55f3c2d7d,4fde891d-0e04-401b-84da-598b184f2c01,6b72f03c-8861-463e-bb85-27cc1eb40a0d,797e5e21-90cf-4800-8aae-8d072120584d,4fa16103-5e1c-4e2f-81a5-8410a8b831ab,424a01c1-fe0d-4fa5-9d22-2918ed78ff5d,9715b6b0-2374-4bde-8e0a-5a41cd80d4e2,3b11c2b7-ad6f-45d3-8eb1-b2b232450c9f,2863d9bc-2f4b-4991-a81b-1d5a670be714,f7fdb531-1908-4a92-8cfb-9f8a86f07d76,c40ca8c8-cfce-4b8c-8bdf-00f49fc6fc18,3d40503f-cbb2-4f98-820f-94a18a39d985,8be79353-91c7-418d-a7cc-3ec9eb37bd32,cf60ad7b-451e-47ee-a00d-83b33fe691c1,d1b43a83-4e48-4b1e-8feb-8cfca965ca5f,f10c93a8-e198-4b23-8a34-1104819686da,eac81f3c-298f-41ca-88cb-907152b512e9,ab0f60a1-f6e3-45f0-b0bf-66c39a90267e,f11d6c49-dcfe-42d1-8ed0-2883eceaa49b,6acf49ea-2322-4c5c-89ec-71e6643d4b75,fa6ee6cf-0759-413d-84c7-6dcf827c45b4,cbd73b94-67dc-4199-a7aa-32311f52cb03,f046c0e4-03c9-4b4b-bc83-49ac2c66ff26,e843cb58-0a05-4638-be35-4e8df3f1bb7d,cfc717ea-234f-4c00-b8f0-56bc23b58804,afdcd642-28d9-4932-b237-39221e3793aa,e37d7efa-4e95-46b3-ac78-e720dec3884a,de135083-dcf7-440a-be44-d94780523ccc,3d0079ea-1dd2-4d22-83c1-fe1ed17b2911,b8bc7d32-3a8c-485e-ae70-d0f62fd33bd3,e227e0a7-7e27-404f-95bf-0ba514d2fd71,816e28cd-bb59-43eb-8279-2438dcbe96e5,bf43ba81-61f6-40fb-8558-c6a40afcb27f,a687a8b9-530a-4eeb-b24c-3c673fdf7789,386b12f1-b30d-43f7-9500-e7fd4c7536b9,14d6731f-c3f8-4818-a9f5-bff5ccc436c3,b1b7ef69-f025-4e1e-8085-598dbacd7f51,7b8b6c8b-3725-41d2-aa04-577cc5a5e2a1,2c81b229-6fab-4090-bcd7-d365b2c89bd8,80c8cb0d-2a58-4c6a-b85a-1871556f3bd5,52a71610-39ee-4b91-9e9d-75e1ed417150,5cbad350-3d08-4ec1-9c57-05dc2ce5cdb7,6d1aa36f-925f-4c70-a0b5-399706fcf822,75956e79-335f-4ead-b662-5e8b2721e9c9,310bf77d-2c26-48d1-bd2e-6032385a0a37,5c7e5a48-923e-4b44-8a27-725fc9b8adc9,22b1da09-4364-4c30-9ecd-30171040ed40,876107ba-338d-4e1e-851c-42f6c1427d1b,469592f9-1b62-42e3-8043-15dfd6299b06,3ca34256-c4cd-4077-b70b-e983755e47ee,3aa1a9ca-e83b-4d32-9a3f-125d2042db4a,9fcf7260-9a21-4847-be9d-93405bb2cf7c,a78bbd33-9f46-40c7-8b01-6a00b4840aa8,20245ec7-3b88-4c81-845f-430c3ddd39e5,507ed812-76ea-4337-84ac-be8b09a0fa20,65a2301a-d036-4ae3-96a5-50f2f3514ca1,3199698f-cea1-4c9a-b841-82aec79ade9b,677a5234-f659-4a0b-96e8-769f0a4c2ab0,2e158cb8-9385-4e1a-a64d-8767d2bdc859,9d7429da-931e-4702-8bbc-e138bd8a7f23,648e8ed5-99b4-4e0a-b232-7855b7941397,398ed1cc-8dac-496b-8fb7-fd148cf702ff,d3abc19f-25f1-4336-a145-1aaa51056c0d,4a292025-c795-4a2a-9fed-62a140921406,8f806fad-d315-4e17-8e6a-3cacc7370295,3f8467eb-0dc8-49af-9efa-1718ef022d59,d1c3cb73-28e3-4cd4-bf45-ed5a415b5210,846b3e43-4c2d-4c11-b328-70a9a7ca5335,404373c1-7908-4c7f-8060-03e4a0ba359a,f6665415-fdb1-4e37-942f-01b80321df40,68e88cbb-32ad-4efd-962f-c390e8132d57,705fc169-b975-4d51-ad4a-1e97bb4c0b41,5d36a697-027b-4d5a-9a4b-3ea325ed9550,309d3c04-d674-4d80-b94c-a6f5fbad3fe3,f8b3b8d7-af2d-492a-9ee3-d40e121e805a,775b3771-90b3-487d-9845-0f200c5ed61e,0c0dedc8-d4e1-4cea-bb96-3f9b0d8697e2,7968a44d-8129-4eb0-95cc-1608023828e4,6dfc5d47-09f5-4269-9baf-2c3cf9a56b2c,04638928-c3ee-466d-9f88-96b0ac32c913,4f0620c6-3800-485a-8230-b1df4b15506f,4e0ee0d6-8710-44d0-8c0b-e74b894ff7bc,eb77cb86-bb14-452d-9a6a-2e55fa801604,c7308676-2333-4b87-af28-bd72474e0e98,414c671b-bc43-45cd-a1ad-073afa046db8,5e0f187a-4835-48c8-bca3-14fd8652f9b7,24001c28-f6cb-48cf-90a5-4e9276a508fb,590e3962-1fef-44b9-8eab-f61facd6eef7,1ee263a9-7599-4f03-b98a-655e155359e1,22d0c717-2151-4730-9e95-6dae21fa9670,7066abe3-6af7-4c8e-b434-a29f5bd4e300,1ed87682-8236-46f1-92d1-ca6a6ecdabda,67b581d4-6816-4905-9d5b-9fe3e5213fd2,9a226370-955f-4ad5-ae44-48ec8995978e,5cc0bcdd-de7c-4aad-94ec-f9e2cb7be839,046d00b0-e1a4-41f9-b3be-9e900069cfea,0e6a64e7-ecca-4bf0-98ae-f679aeaf11cf,07bc6093-5129-4819-b72a-0a9454259854,32a16d66-924e-4d7c-9501-1d69763f151f,be67bb36-be36-47fe-9474-ba4054d9e2cc,937c7061-3593-4526-970e-28e40395f388,a74c4589-b671-4938-bf13-83a1d6741ed1,c589f92d-6952-488a-a1b0-4492668e5b0a,7900b0bf-209a-4724-9f23-13a996a97d41,1798f66a-e0fe-474e-8d01-64a5418b68c8,0a56bed5-c9a2-4bb7-b52e-6132fc61fd96,0904fdc7-6da2-46f1-af57-66b8e9e6dfac,2ec653e0-95b0-4043-94e0-99f5d63f307e,b3e38600-8817-4d65-b938-3b4ae9a41e01,99b169ba-d3cb-4f71-af34-2afeeac777ab,5644d6fe-4848-4a2b-8a61-68d103cd5cbf,80818356-6635-40dc-8e6c-926a85c87589,862608d9-197d-4a2a-83aa-fa0c2d5a3708,2762bf1e-baca-43b8-bd6f-b7182370b401,97a0b96e-ecd8-42c6-bad2-09bd3feb4d04,66ded87c-a353-41c0-8781-2c4b61c4f325,15432774-3810-4e00-ade5-e16623a728c1,4c69268c-f62f-4fcb-a8a0-8201dcc3378b,465101c5-3dc4-420f-9bd0-b6302966c8c3,bca5baba-f729-46aa-9bb5-8b788bd97b89,d3e8988f-d2fd-47f1-a793-ef54631e0946,bbc379a9-6b56-4ae0-96fd-3a284980ff24,1413731e-eeba-460f-a50a-7373c9381ca9,211ff1e7-a67e-409c-8c89-fa537285866b,99b336e3-9550-46c7-a9b3-f926d75cd931,5bc7ca57-1109-4357-987a-341016d35a3c,a3d60675-0768-4f24-a297-cce3f825ebbd,8919598e-7fdc-4729-a248-567de6efffed,c80c0ef6-d2b0-4902-8ea6-55d44ede0c34,591c51ac-c5db-4629-ac89-5ad3ac5f162b,54a19ce7-f7ae-44ff-b641-3ef4761a3659,1d2834d5-f706-41c4-b683-128d3cc552f7,b4e60d99-1c15-46b7-ae5d-97b7173e5dc4,35fe4f04-a52e-4e39-8cbd-c621981a8906,b213907e-ab0f-4f1c-a506-99a72ff0e003,aff9ca1f-2ed8-48f8-bc71-1d799c62173e,5fc6c5f2-503f-44a8-8824-877a29e6978a,d4192ffb-f152-4847-86ac-7764c566b12c,dd87591b-836d-4a41-baae-b6caddbf63c2,aa023bc9-fe57-405a-9efd-31e47ea574a1,29deed9a-3a5d-470a-a8af-0b2a552fd80d,49bcf8fa-45c9-4fc9-8902-0dc1ec008859,0f886b1b-980e-40fb-a83b-4233ead3724b,c0a2bd4d-78da-4def-8adf-87a49219245d,28182f25-b45e-4bbc-8237-855c1b1b2707,8fcc8b85-0af8-4f8e-b71c-38f005b954bf,7f33162d-c506-4f5b-beaa-cefe330cf2ec,bebf6f65-4ae6-438e-9d6a-2b9aabc873c2,ec68b5a6-52b6-4082-a582-476e58bf90e0,6a3db355-4499-4d04-8c99-d10bc4ce2d18,082db034-c7f5-46a2-89fb-ff1f7ff0e457,8343edfa-e7d9-43e0-aaaa-dc67ff9dd119,ee5035c4-35bf-47d5-841e-6e12aca5cfee,e9e8adc9-b54e-4976-b297-e23aa2df6832,8252ddd9-6599-49ee-a4e9-fd27746fb5a9,bd901546-8150-40a8-aa0f-51a82b3a2d59,0632bfd7-db4d-4ac5-bab5-b7ab904c3e46,3b96b3dc-94b0-4542-8f49-98606452ccab,02a9fcb3-da0d-4aa8-9474-635c183169c9,e62059ab-ff46-4e0c-9eff-2decdb207982,747d3ccf-b5c5-4980-bb64-6ae8ddcee5b1,0a982709-e637-4f7d-a2e1-73b5f32b7a1d,03715e4f-f97b-425b-b614-c376c2c16e84,bb9bbe14-e64d-49d2-8e4b-8f31e4c983bc,09c205f5-78ea-4663-8890-831607f4cf62,dfa99abe-f5d4-40e5-ba3a-c5ef9c550fea,eebe099e-d160-4057-856c-f55fab3c4adb,cf73bd0c-86c0-4d54-ba7c-087441360b6d,2b29c12d-8e27-4990-9440-2786b88301a3,8b96868d-1a4d-4514-a9dd-89f49180c4e7,656976e3-e853-4cee-a124-2e7e712ff493,6bbccafb-7d1d-4c0b-8da4-bb0e82bb2273,f536c2b3-a0e7-4d05-91d7-536a00ae29ee,665809e0-1fef-415c-8905-eea6c3678f51,eb9c7466-10ea-4b97-8343-e49757cacb95,77597dd5-1b65-4d37-bd7e-e1d5be1ff87f,81e47ace-360e-4a35-86c3-6f65a5c7687c,6327c5a0-abe1-4345-805d-1c260c4ecaec,6cbfc6ab-eac4-4003-8f53-8783f2a1221d,fe49567b-18de-4bfe-833f-6e397a25de40,04b3b7e5-3835-4c1b-91d6-509272fddc6c,f3526130-6c3b-4284-873f-cd75442e027e,01d4ef02-07b6-473a-a179-ec319584908d,66315173-e245-4b06-8c26-e77059cc3426,c47cb593-25a4-40c5-ae5d-440289817f19,cc9e6490-0597-4607-95f9-8ac1d6f6c455,a078e9a3-49b1-4f49-b866-e99f70594938,497ad9f5-eef4-40fb-840d-5b2810d6b9bf,a3e20be3-dc05-496b-944a-097a68e7dd83,70543812-7abc-43bc-b608-379a0592cde6,29e1fed7-1a4c-40e5-b7f6-81ac62fab3cd,fb3bc2f0-7e72-4c9d-8492-4aeffec41801,ab4e120d-7ad6-438a-b118-1bd253093d79,f6b77287-9198-4f8d-acac-70ca6c652f2d,15b6869e-10f6-4f41-8aec-692aba57ba30,788870a1-d78a-4442-af00-2e87cd1ce1db,6cd6b63b-94dc-42b1-8f4e-cb17142fbd25,714de7f8-a8e8-4482-a4bf-f5d0f3723020,d2cb1940-3153-48cc-8c8d-d2cf70332948,bf58a8fa-ec84-4fb0-b00e-c8333ef93925,e64122c8-ce06-475b-9805-6961ea650cb2,d5c44e0a-8737-4def-a995-e4ec221cef1a,7d7d82f3-8f3d-471c-99ba-077bc5dc82e8,01e5b659-c60c-421e-ae58-9f08a5b46714,dabf1559-a24d-4cd7-8046-d96b6ce54c9a,df8f9be1-92ec-4400-8775-53a9cb2e7eb8,f6dec6ec-a133-4346-92d3-3b56e10d324e,db20dbc4-5675-47a7-bf0e-f44a52eeaf0a,e695857a-44a8-4c93-9d11-4a7d248d55ae,dc4dced3-d2eb-4f6f-9166-3b5acaeaaaeb,7896a1ee-5d58-4b81-9b7a-e62f3e37f518,cb4a7506-1886-475d-9469-bd9589f499f3,cbba0bb7-55f8-4c13-9442-8d5a2b2b2e43,f62ad370-be99-4a5c-90ff-e1bb072fd47c,d232f616-b100-4888-8136-d0069b78dc78,636c6670-f160-47ac-b3bd-012e29973e74,bcdf135d-0a44-47b8-ab17-63c5309d7b0e,4669545a-abf3-4afe-85bb-328a075315e9,ee85e191-9acb-4403-a43a-5bf13ca26b7e,d1b282fd-5223-4335-a779-be4ab3ec5b55,d658396e-1ee6-401e-88cf-b6e407d2c031,ea1d0039-a4fc-477f-8b2e-d3bd756fbbe1,da261097-4bc8-4f1e-a1ed-1eaed03dfd70,e395d78a-9468-4f00-8ee3-373a82a1d419,08ffd8d5-58a9-49d3-9e2f-2842c00849b4,97efa80a-4ecd-43fb-a289-289abc60ca94,a2d4f566-a64b-4ca3-8d48-16d2f940af37,88d23859-766a-4c47-85f3-fa5cdf737ed5,5bc8101d-a2c5-4d87-be82-49995f435fd3,996ab3eb-d1c3-451b-aaa1-7387038001f2,8e77ef03-966c-4fca-b51b-139b2ee7a272,f7cf635f-e9eb-4d10-a531-648dd490481b,b7376c8e-4ec1-46a6-af1a-227582bcda9b,e0704f67-1d00-4f4a-a3dc-499d83cb50d6,fc7fbbe1-41c2-4ec1-b73a-d769c22b46cd,63f6dd8e-1843-4d8b-886e-c477e1b4ad5e,cd338df8-5fbc-4160-bd64-6bc90607484f,d6ffa014-4c9a-44a9-862d-0bffea1b66de,961dbacb-6fa9-499b-a267-53b1874ccf01,80eaf06e-ad54-4c66-969d-6964260c2eb7,ac296d98-d5f5-4735-bca4-12c27ec244d7,15a4c271-306e-47df-a5b5-451c654cf9ea,013e1dd1-f6f2-4ae9-afa2-00d17a8fdffa,5c21cab1-3c88-4f55-8ae5-01761be2790a,68dc365b-d3fe-4cca-9991-08fbba6e50c6,252418fb-95b6-49f4-a528-13e8ca61983e,3986eded-5b31-4789-9a52-f431dae94f76}'::uuid[]))
  • Rows Removed by Filter: 215,889
18. 0.052 0.512 ↑ 2.8 162 1

Hash (cost=29.16..29.16 rows=451 width=32) (actual time=0.511..0.512 rows=162 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
19. 0.213 0.460 ↑ 2.8 162 1

Hash Right Join (cost=8.68..29.16 rows=451 width=32) (actual time=0.128..0.460 rows=162 loops=1)

  • Hash Cond: (tqs.topic_id = ts_1.topic_id)
20. 0.134 0.134 ↑ 1.0 590 1

Seq Scan on topic_question_spec tqs (cost=0.00..18.90 rows=590 width=32) (actual time=0.004..0.134 rows=590 loops=1)

21. 0.049 0.113 ↑ 1.0 162 1

Hash (cost=6.65..6.65 rows=162 width=16) (actual time=0.112..0.113 rows=162 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
22. 0.064 0.064 ↑ 1.0 162 1

Seq Scan on topic_spec ts_1 (cost=0.00..6.65 rows=162 width=16) (actual time=0.007..0.064 rows=162 loops=1)

  • Filter: ((type)::text = 'Question'::text)
  • Rows Removed by Filter: 50
23. 0.050 0.103 ↑ 1.0 162 1

Hash (cost=4.62..4.62 rows=162 width=20) (actual time=0.102..0.103 rows=162 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
24. 0.053 0.053 ↑ 1.0 162 1

Seq Scan on question_spec qs (cost=0.00..4.62 rows=162 width=20) (actual time=0.005..0.053 rows=162 loops=1)

25.          

CTE weeks

26. 0.008 0.323 ↑ 27.2 12 1

Unique (cost=62.51..65.02 rows=326 width=12) (actual time=0.284..0.323 rows=12 loops=1)

27.          

Initplan (for Unique)

28. 0.016 0.016 ↑ 1.0 1 1

CTE Scan on campaign_start (cost=0.00..0.03 rows=1 width=4) (actual time=0.015..0.016 rows=1 loops=1)

29. 0.046 0.299 ↑ 4.1 81 1

Sort (cost=62.48..63.32 rows=335 width=12) (actual time=0.283..0.299 rows=81 loops=1)

  • Sort Key: dd.year_week_iso DESC, (dense_rank() OVER (?))
  • Sort Method: quicksort Memory: 28kB
30. 0.082 0.253 ↑ 4.1 81 1

WindowAgg (cost=0.29..48.44 rows=335 width=12) (actual time=0.038..0.253 rows=81 loops=1)

31. 0.171 0.171 ↑ 4.1 81 1

Index Scan Backward using date_dim_new_year_week_iso on date_dim_new dd (cost=0.29..43.41 rows=335 width=4) (actual time=0.027..0.171 rows=81 loops=1)

  • Index Cond: (year_week_iso <= 202,026)
  • Filter: (date_actual >= $5)
  • Rows Removed by Filter: 829
32.          

CTE weeks_limited

33. 0.330 0.330 ↑ 9.1 12 1

CTE Scan on weeks (cost=0.00..7.33 rows=109 width=12) (actual time=0.285..0.330 rows=12 loops=1)

  • Filter: (dr <= '12'::bigint)
34.          

CTE overall_topic_count

35. 12.772 132.554 ↑ 322.4 270 1

GroupAggregate (cost=15,854.58..17,813.34 rows=87,056 width=44) (actual time=108.172..132.554 rows=270 loops=1)

  • Group Key: dd_1.year_week_iso, rd.workgroup_id, rd.topic_id
36. 30.733 119.782 ↑ 1.9 45,851 1

Sort (cost=15,854.58..16,072.22 rows=87,056 width=36) (actual time=108.152..119.782 rows=45,851 loops=1)

  • Sort Key: dd_1.year_week_iso, rd.workgroup_id, rd.topic_id
  • Sort Method: external merge Disk: 2,072kB
37. 22.678 89.049 ↑ 1.9 45,851 1

Hash Join (cost=1,094.04..6,328.28 rows=87,056 width=36) (actual time=21.078..89.049 rows=45,851 loops=1)

  • Hash Cond: (rd.date_dim_id = dd_1.date_dim_id)
38. 28.113 45.373 ↑ 1.9 45,851 1

Hash Join (cost=3.25..5,008.95 rows=87,056 width=36) (actual time=0.037..45.373 rows=45,851 loops=1)

  • Hash Cond: (rd.topic_id = t.topic_id)
39. 17.237 17.237 ↑ 2.8 61,551 1

CTE Scan on raw_data rd (cost=0.00..3,482.22 rows=174,111 width=36) (actual time=0.007..17.237 rows=61,551 loops=1)

40. 0.010 0.023 ↑ 3.6 28 1

Hash (cost=2.00..2.00 rows=100 width=16) (actual time=0.023..0.023 rows=28 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
41. 0.013 0.013 ↑ 3.6 28 1

CTE Scan on topics t (cost=0.00..2.00 rows=100 width=16) (actual time=0.001..0.013 rows=28 loops=1)

42. 10.846 20.998 ↑ 1.0 36,524 1

Hash (cost=634.24..634.24 rows=36,524 width=8) (actual time=20.998..20.998 rows=36,524 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 1,939kB
43. 10.152 10.152 ↑ 1.0 36,524 1

Seq Scan on date_dim_new dd_1 (cost=0.00..634.24 rows=36,524 width=8) (actual time=0.007..10.152 rows=36,524 loops=1)

44.          

CTE date_survey

45. 0.009 0.116 ↓ 10.0 10 1

GroupAggregate (cost=11.94..11.96 rows=1 width=12) (actual time=0.108..0.116 rows=10 loops=1)

  • Group Key: dd_2.year_week_iso
46. 0.008 0.107 ↓ 10.0 10 1

Sort (cost=11.94..11.94 rows=1 width=4) (actual time=0.105..0.107 rows=10 loops=1)

  • Sort Key: dd_2.year_week_iso
  • Sort Method: quicksort Memory: 25kB
47. 0.010 0.099 ↓ 10.0 10 1

Hash Join (cost=9.33..11.93 rows=1 width=4) (actual time=0.052..0.099 rows=10 loops=1)

  • Hash Cond: (wl.year_week_iso = dd_2.year_week_iso)
48. 0.052 0.052 ↑ 9.1 12 1

CTE Scan on weeks_limited wl (cost=0.00..2.18 rows=109 width=4) (actual time=0.000..0.052 rows=12 loops=1)

49. 0.005 0.037 ↓ 10.0 10 1

Hash (cost=9.32..9.32 rows=1 width=4) (actual time=0.036..0.037 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
50. 0.007 0.032 ↓ 10.0 10 1

Nested Loop (cost=0.29..9.32 rows=1 width=4) (actual time=0.009..0.032 rows=10 loops=1)

51. 0.005 0.005 ↓ 10.0 10 1

Seq Scan on survey s (cost=0.00..1.01 rows=1 width=4) (actual time=0.002..0.005 rows=10 loops=1)

52. 0.020 0.020 ↑ 1.0 1 10

Index Scan using date_dim_new_pk on date_dim_new dd_2 (cost=0.29..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=10)

  • Index Cond: (date_dim_id = s.date_dim_id)
53.          

CTE date_responses

54. 1.314 168.818 ↓ 10.0 10 1

GroupAggregate (cost=4,125.96..4,125.98 rows=1 width=28) (actual time=167.184..168.818 rows=10 loops=1)

  • Group Key: dd_3.year_week_iso, wg.workgroup_id
55. 1.763 167.504 ↓ 2,726.0 2,726 1

Sort (cost=4,125.96..4,125.97 rows=1 width=36) (actual time=166.969..167.504 rows=2,726 loops=1)

  • Sort Key: dd_3.year_week_iso, wg.workgroup_id
  • Sort Method: quicksort Memory: 309kB
56. 5.194 165.741 ↓ 2,726.0 2,726 1

Nested Loop (cost=670.60..4,125.95 rows=1 width=36) (actual time=9.504..165.741 rows=2,726 loops=1)

57. 14.332 152.071 ↓ 4,238.0 4,238 1

Nested Loop (cost=670.18..4,125.43 rows=1 width=36) (actual time=9.497..152.071 rows=4,238 loops=1)

  • Join Filter: (dd_3.year_week_iso = wl_1.year_week_iso)
  • Rows Removed by Join Filter: 46,618
58. 28.945 125.025 ↓ 4,238.0 4,238 1

Hash Join (cost=670.18..4,121.89 rows=1 width=36) (actual time=9.490..125.025 rows=4,238 loops=1)

  • Hash Cond: (q.party_id = pgr.party_id)
59. 48.903 86.700 ↓ 10.8 118,754 1

Nested Loop (cost=506.23..3,916.55 rows=11,035 width=36) (actual time=0.098..86.700 rows=118,754 loops=1)

60. 0.017 0.047 ↓ 10.0 10 1

Nested Loop (cost=0.29..9.32 rows=1 width=20) (actual time=0.010..0.047 rows=10 loops=1)

61. 0.010 0.010 ↓ 10.0 10 1

Seq Scan on survey s_1 (cost=0.00..1.01 rows=1 width=20) (actual time=0.003..0.010 rows=10 loops=1)

  • Filter: (campaign_id = 'ae4f98f5-fd13-b722-2c2c-50d5928391bd'::uuid)
62. 0.020 0.020 ↑ 1.0 1 10

Index Scan using date_dim_new_pk on date_dim_new dd_3 (cost=0.29..8.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=10)

  • Index Cond: (date_dim_id = s_1.date_dim_id)
63. 32.700 37.750 ↓ 1.1 11,875 10

Bitmap Heap Scan on questionnaire q (cost=505.94..3,796.88 rows=11,035 width=48) (actual time=0.526..3.775 rows=11,875 loops=10)

  • Recheck Cond: (survey_id = s_1.survey_id)
  • Heap Blocks: exact=1,707
64. 5.050 5.050 ↓ 1.1 11,875 10

Bitmap Index Scan on ix_l1_questionnaire_survey_id (cost=0.00..503.18 rows=11,035 width=0) (actual time=0.505..0.505 rows=11,875 loops=10)

  • Index Cond: (survey_id = s_1.survey_id)
65. 0.133 9.380 ↓ 53.0 424 1

Hash (cost=163.85..163.85 rows=8 width=32) (actual time=9.379..9.380 rows=424 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
66. 0.184 9.247 ↓ 53.0 424 1

Nested Loop (cost=4.59..163.85 rows=8 width=32) (actual time=0.271..9.247 rows=424 loops=1)

67. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on workgroups wg (cost=0.00..0.02 rows=1 width=16) (actual time=0.000..0.003 rows=1 loops=1)

68. 8.889 9.060 ↓ 53.0 424 1

Bitmap Heap Scan on party_group_rel pgr (cost=4.59..163.75 rows=8 width=32) (actual time=0.268..9.060 rows=424 loops=1)

  • Recheck Cond: (workgroup_id = wg.workgroup_id)
  • Filter: (((party_role_group_rel_type)::text <> 'HeadPartyRoleGroupRelation'::text) AND (party_id = ANY ('{dc4f6d1b-0785-4c16-8667-26e172c748fb,a981f8fc-5792-4b26-999b-db6c0090b628,109ac58a-97bb-4db0-8663-7cc55706e170,17f093d5-e041-4a67-ad85-ff2f630a2573,bac93b0b-5bd4-48ba-834a-10c23642273e,b92b07c0-ee1a-460d-8090-606ffe4fc70c,b020f0ce-a7e1-44ad-831b-7b8988446bd6,58afe001-347d-4541-a534-0f8a66fae14b,2f1dee93-c6d1-4e00-b5bb-20a8bd227a34,5f5508fa-ba17-48fe-a396-d5764dcf6670,356e60fc-2116-48ed-b4ea-076c8b86e8ed,a52779af-dc4f-414a-907b-2c096404a514,55d9ff5e-dc41-4b14-baba-1b5150751076,4f8658e9-4fca-4fbb-ae05-82fe8ec3e35b,ae0fdef6-ed20-4e32-af90-643165345cc4,5c2541ae-4eb5-4d07-8980-f4ab9c66097a,93889874-baf2-4a05-bf2e-22026499ea7b,fba99679-d079-4565-b291-288a6bab5fda,6309be1a-9cfd-4208-8ef5-c1741e503389,d4f8cb6f-f27e-4c1d-8971-86abc401b167,9390dad9-def8-4b05-9b29-0e2f7f14f637,03843b82-8343-42c9-ae24-cdd69548087b,bf829b80-c928-42e2-beab-11d4439e8fbd,133b9c6c-df6e-49c8-87c3-d92d414522b2,6deb88f4-94f0-4bc0-987f-220f3218ad20,dd2cffa0-502e-44de-8012-9f11df42951c,83753b9b-ad36-4799-90d1-e79ab21e21de,684c445e-43f1-4143-8ca9-83533ea91d5f,dbc120f2-c258-4cce-baf1-88757f9fa405,9c092359-bebc-4f0a-bdc2-5c27002651df,3579f30f-180d-4ede-b898-a1de1797b256,98720c09-2df9-4760-b28e-62af8bf4b0b4,887d54df-d906-404b-b634-ddc494ec78c2,a1e8dcfb-60ca-4574-8fad-ba23ef9263d8,83498593-61bf-4bb9-a0d8-02d14f64b3a6,4b265c6b-b4e0-4126-aa3b-03ed634eefa1,596fbbdd-53d0-494f-8d92-1624f47ec666,f51f208c-bb68-4a91-8585-f6d8daa70a47,4a615d41-3def-41f2-8938-7ac56d1923fa,07e74e7c-ef5e-44e7-b0c6-9d46eaa47712,081badba-3487-40c1-ae6a-1c2f526d0438,356848b4-dc62-4834-951b-5c6b4f2d4cac,2e05df90-1d4d-4f09-b462-9187982b48ee,2b57cbe3-27df-4d83-b7dd-5a367fd0aebe,44e3b98b-0ac8-41c1-ade6-cc601ec9a934,ea5adf32-ff2d-4b85-b9fb-8bd49a52ca0a,51c25d81-6041-4ada-a801-3706eddb0e41,a53d50d5-3c5f-45bc-be05-2bec229aaf0c,c5944d11-4e63-438c-a719-6f2be2ac64a3,0e28b447-b1a9-42d4-a63b-c43d574096e7,38d1fcc6-23c6-48fa-8a99-b48aa2aea1f5,871213e1-622b-4c55-8cda-f20d4065c847,704b2595-da94-4cfe-8b87-0c5e03c089bf,110afebb-e2b3-4d32-be6d-83a414e981c9,63abe02a-3c1d-4838-ab81-8fd2bde802d9,061fb89a-f19a-44cf-8a61-3af4e5e6ef10,3b70f68a-22a3-46fe-8505-1115a78c6b5e,1ef56e1b-d964-48e7-b482-05c098bceb27,28c2ab54-768e-40cf-b84b-e7ff687a27e5,bb147b1c-821f-46c7-a89b-479f19735ef9,43190311-c7f4-42ba-aa8c-cab99afbb9f8,cd2d6d10-5858-4a26-b1da-bbf4397667ad,c1e24855-b742-490b-ab5d-81b299172cdd,75216d4b-1da0-42b5-b04e-f02be98de35a,d3a47f9e-6a51-4c25-9a5c-c23523171536,c9fe2c53-64a3-47cb-b795-3c4df40c0afa,b526e15a-5cc8-4137-b09a-fd3c94b7971e,af24c950-86d5-4f6e-b450-1c2e29e7573f,7396af47-9ac0-4ce4-9c4b-d101a5f9ae97,0afa2884-85a4-4937-a3d0-e3323c143933,959fd6cd-e07e-4e87-b675-f3c494ee49ae,792ac725-63c2-498d-af98-cd4194fc95ee,8b8c155f-eb25-4ce4-8d97-cb9500921090,c76b63f7-fc42-43a7-ba94-9275f09f8191,dd0f77eb-5dba-4148-a6c7-d7abf1a48a46,eb467d26-e3cd-46a8-9f69-fb75b4dd3f53,cf093184-aa58-4213-b372-ef300b6d5911,e29ef91c-0161-4b74-b965-e65bf486116b,8fe5ec8e-e6c8-47b2-9867-e53bb8766ea6,6a1a6762-fde1-47cd-85a3-e84eaf642366,9338d44b-b952-450b-93ba-6224e440c72e,9bd6ad08-31d9-4b5e-abf6-d8e69e5bf87e,51c56aa8-c4a4-40e9-8dad-056efb5bfbda,d63322ea-5197-40f4-a214-730454de0932,375eedd0-a5ef-4734-a5ef-7d4d99171850,aa799943-98ae-4f14-86df-83fdc06c943e,c2a4f360-17c1-4f93-b2bc-e1d4e35ccfb1,a587479e-64b1-4f0b-82f2-9a84924a332f,e25c6b7f-de24-4bb9-928d-ef53a7735131,a3a31aa7-ebff-4279-a633-0b3c9f95d8f3,a7e4096b-6569-4817-bee1-4d949e1c1498,b67c04a2-bfba-46e2-a60a-b71a592d5c3c,b7601b81-11dd-4979-bc52-c728e0d50ae6,c3e39841-a329-47b8-a96e-357faeafd029,9bf8ffae-939f-4d86-a49f-c29ff1be715a,1db00e08-5671-445a-bffd-6768ae76b921,cf0b1e8f-1ce0-4948-880a-73400a3f43d7,9bee69e2-8065-43d3-94d6-fcbeefb28628,6c494769-4441-4f29-86b9-2da79b15c2a2,c1af9e0e-d4db-429d-89b1-8e35210e365b,99fb9f9d-9041-4e1c-9a5c-4dfad9ba6a5f,434ac9de-b217-4ba5-a8c8-ce326eda2267,3877a160-916c-432c-9b6e-b4db98f39a8e,b772ca97-9c4b-4567-b719-b70c58dda976,38ce4256-f528-4557-9886-0550ccf9bca6,8a5c71fe-e233-41d4-a9d3-c50c8b81b888,5bd1b795-f133-4dd1-9b71-0b1d7c0e8713,678320a2-2d3b-46f2-ba87-15f07299fa51,183a574f-a47a-4c86-b1de-7e8fb92c8bbb,d39c2854-345e-4c32-9faa-53929545e7fd,8f33c236-e797-450d-adae-fb2d1e49b172,dc97de7e-681b-440b-b4bf-04b9fb083ecb,8f304980-3f91-4762-aa77-877fbc58afc4,0adb21fc-963e-4402-b453-1da3362d2080,08a55cba-455c-42de-b037-3cfaa96e405e,fcda6002-9ac9-46e0-a86d-1e591c4742b1,482d3b4b-2366-4e5f-bae8-52851070849f,8a71ab02-cb5d-4878-81e5-eb53e346a45d,bff4c71d-34c3-4bc8-b1fd-bc3df892827d,be6bb8e2-af95-418a-8d3c-5591865a970c,8100c1cb-d083-42f0-9adf-78f0b07d778d,c1ecf831-a931-4640-a32f-e21ecdafc07c,74b9f30a-457f-43c7-bff7-678fcaa248bd,1468feea-211e-49aa-b0eb-9e9d0cafef8b,9baf2c3c-de8d-4ed4-9a9e-2cc671157edd,0d9437e3-7fb0-4106-ab62-53cbbabbb4e1,84e12a3a-fde6-4076-b1f1-f29bccb25efa,f711d9df-4f1a-4334-9f54-b0ed9c2472b3,0141634f-8abe-4f9d-8670-4f03a90ac201,c6768be8-b4b2-4664-87cb-4b4d6ec90177,927e8551-b168-4088-b947-ca355ba3ac02,461d7b57-57e5-4c27-9eaf-fff640766a07,96b263ca-c23b-4ad7-97e9-1173df57a23d,de8f49d9-a3fd-4fc6-84a5-a89554bcf501,34f670c9-7d74-4199-a9d6-95e05a190170,cbf958fc-34e6-4320-9650-97a6dc0056f1,9e2ab3ff-96b0-4102-8991-52873f3e8494,c77b1652-3be9-4f68-a20d-825912a21fa4,6a2764b6-e084-44a3-9d1d-e3ee8fbe188d,16915a5a-390e-46ae-a496-b51afc5d05a0,45d5a660-b547-4d80-aa7b-a09eddf83dc1,deda5fbb-d4f7-45ef-a557-7104e72832f6,8288ccde-7a98-4612-9eba-8d07ac14aa47,aa3a0cc0-b528-4bbf-b9d5-953fd16a4a5a,9d29b714-c37b-4171-adf7-06e1f980fa56,29b1d991-969e-4a7f-99a0-8f32c703cfa9,eb376040-2f95-4e96-8f0f-a869e9995bc1,1da3ee5d-c57e-44f6-ac99-b1b31466965f,14f4f392-f986-4272-96f2-258b6491ea58,d702778e-65c4-4923-8429-83bf7ae9344d,5ec67b28-06c9-4560-9a35-0138fc1dc90e,7ada70d2-bec1-417b-9c9f-d9dbd45a7882,10bbb77a-5641-40e7-a976-39dfccb24923,771e0b5d-d4cc-438c-a43d-0de73eb2e463,ab2ea1d3-0d76-4b18-b1f8-01132e82d900,793d574f-3dba-41cc-8d41-52afe14400e2,cfb67d23-a12e-4c2e-aaf9-25666bec9e0d,79505ae0-819a-4c00-a319-5bda43e1f4bb,0faf00e5-18e6-4efc-aded-65e4fe211203,d4fae18a-a7f3-4eed-af93-144968c69096,dc81c12a-d04b-435d-9359-6b6cabb1931d,786ddfb6-129d-42ff-b417-3a2b44b9e3d5,3813fb2a-e5a3-4adf-a056-e6f1dcb228ba,bcbeaacd-1c07-4dcf-96ec-a5ec08f04da2,a4619a98-7274-4a21-9359-3250561e9e3c,d1019eac-ee65-4ca5-aa4a-80132ce63a1e,9ab3b2ab-2447-4ae1-990d-0105a37f847f,f9dd8da5-2263-4bd7-89d9-10fc7fd840a6,42c3e23a-a60a-48d7-ab74-75985ba6497b,f4416707-3f81-4aab-805c-3dd55f3c2d7d,4fde891d-0e04-401b-84da-598b184f2c01,6b72f03c-8861-463e-bb85-27cc1eb40a0d,797e5e21-90cf-4800-8aae-8d072120584d,4fa16103-5e1c-4e2f-81a5-8410a8b831ab,424a01c1-fe0d-4fa5-9d22-2918ed78ff5d,9715b6b0-2374-4bde-8e0a-5a41cd80d4e2,3b11c2b7-ad6f-45d3-8eb1-b2b232450c9f,2863d9bc-2f4b-4991-a81b-1d5a670be714,f7fdb531-1908-4a92-8cfb-9f8a86f07d76,c40ca8c8-cfce-4b8c-8bdf-00f49fc6fc18,3d40503f-cbb2-4f98-820f-94a18a39d985,8be79353-91c7-418d-a7cc-3ec9eb37bd32,cf60ad7b-451e-47ee-a00d-83b33fe691c1,d1b43a83-4e48-4b1e-8feb-8cfca965ca5f,f10c93a8-e198-4b23-8a34-1104819686da,eac81f3c-298f-41ca-88cb-907152b512e9,ab0f60a1-f6e3-45f0-b0bf-66c39a90267e,f11d6c49-dcfe-42d1-8ed0-2883eceaa49b,6acf49ea-2322-4c5c-89ec-71e6643d4b75,fa6ee6cf-0759-413d-84c7-6dcf827c45b4,cbd73b94-67dc-4199-a7aa-32311f52cb03,f046c0e4-03c9-4b4b-bc83-49ac2c66ff26,e843cb58-0a05-4638-be35-4e8df3f1bb7d,cfc717ea-234f-4c00-b8f0-56bc23b58804,afdcd642-28d9-4932-b237-39221e3793aa,e37d7efa-4e95-46b3-ac78-e720dec3884a,de135083-dcf7-440a-be44-d94780523ccc,3d0079ea-1dd2-4d22-83c1-fe1ed17b2911,b8bc7d32-3a8c-485e-ae70-d0f62fd33bd3,e227e0a7-7e27-404f-95bf-0ba514d2fd71,816e28cd-bb59-43eb-8279-2438dcbe96e5,bf43ba81-61f6-40fb-8558-c6a40afcb27f,a687a8b9-530a-4eeb-b24c-3c673fdf7789,386b12f1-b30d-43f7-9500-e7fd4c7536b9,14d6731f-c3f8-4818-a9f5-bff5ccc436c3,b1b7ef69-f025-4e1e-8085-598dbacd7f51,7b8b6c8b-3725-41d2-aa04-577cc5a5e2a1,2c81b229-6fab-4090-bcd7-d365b2c89bd8,80c8cb0d-2a58-4c6a-b85a-1871556f3bd5,52a71610-39ee-4b91-9e9d-75e1ed417150,5cbad350-3d08-4ec1-9c57-05dc2ce5cdb7,6d1aa36f-925f-4c70-a0b5-399706fcf822,75956e79-335f-4ead-b662-5e8b2721e9c9,310bf77d-2c26-48d1-bd2e-6032385a0a37,5c7e5a48-923e-4b44-8a27-725fc9b8adc9,22b1da09-4364-4c30-9ecd-30171040ed40,876107ba-338d-4e1e-851c-42f6c1427d1b,469592f9-1b62-42e3-8043-15dfd6299b06,3ca34256-c4cd-4077-b70b-e983755e47ee,3aa1a9ca-e83b-4d32-9a3f-125d2042db4a,9fcf7260-9a21-4847-be9d-93405bb2cf7c,a78bbd33-9f46-40c7-8b01-6a00b4840aa8,20245ec7-3b88-4c81-845f-430c3ddd39e5,507ed812-76ea-4337-84ac-be8b09a0fa20,65a2301a-d036-4ae3-96a5-50f2f3514ca1,3199698f-cea1-4c9a-b841-82aec79ade9b,677a5234-f659-4a0b-96e8-769f0a4c2ab0,2e158cb8-9385-4e1a-a64d-8767d2bdc859,9d7429da-931e-4702-8bbc-e138bd8a7f23,648e8ed5-99b4-4e0a-b232-7855b7941397,398ed1cc-8dac-496b-8fb7-fd148cf702ff,d3abc19f-25f1-4336-a145-1aaa51056c0d,4a292025-c795-4a2a-9fed-62a140921406,8f806fad-d315-4e17-8e6a-3cacc7370295,3f8467eb-0dc8-49af-9efa-1718ef022d59,d1c3cb73-28e3-4cd4-bf45-ed5a415b5210,846b3e43-4c2d-4c11-b328-70a9a7ca5335,404373c1-7908-4c7f-8060-03e4a0ba359a,f6665415-fdb1-4e37-942f-01b80321df40,68e88cbb-32ad-4efd-962f-c390e8132d57,705fc169-b975-4d51-ad4a-1e97bb4c0b41,5d36a697-027b-4d5a-9a4b-3ea325ed9550,309d3c04-d674-4d80-b94c-a6f5fbad3fe3,f8b3b8d7-af2d-492a-9ee3-d40e121e805a,775b3771-90b3-487d-9845-0f200c5ed61e,0c0dedc8-d4e1-4cea-bb96-3f9b0d8697e2,7968a44d-8129-4eb0-95cc-1608023828e4,6dfc5d47-09f5-4269-9baf-2c3cf9a56b2c,04638928-c3ee-466d-9f88-96b0ac32c913,4f0620c6-3800-485a-8230-b1df4b15506f,4e0ee0d6-8710-44d0-8c0b-e74b894ff7bc,eb77cb86-bb14-452d-9a6a-2e55fa801604,c7308676-2333-4b87-af28-bd72474e0e98,414c671b-bc43-45cd-a1ad-073afa046db8,5e0f187a-4835-48c8-bca3-14fd8652f9b7,24001c28-f6cb-48cf-90a5-4e9276a508fb,590e3962-1fef-44b9-8eab-f61facd6eef7,1ee263a9-7599-4f03-b98a-655e155359e1,22d0c717-2151-4730-9e95-6dae21fa9670,7066abe3-6af7-4c8e-b434-a29f5bd4e300,1ed87682-8236-46f1-92d1-ca6a6ecdabda,67b581d4-6816-4905-9d5b-9fe3e5213fd2,9a226370-955f-4ad5-ae44-48ec8995978e,5cc0bcdd-de7c-4aad-94ec-f9e2cb7be839,046d00b0-e1a4-41f9-b3be-9e900069cfea,0e6a64e7-ecca-4bf0-98ae-f679aeaf11cf,07bc6093-5129-4819-b72a-0a9454259854,32a16d66-924e-4d7c-9501-1d69763f151f,be67bb36-be36-47fe-9474-ba4054d9e2cc,937c7061-3593-4526-970e-28e40395f388,a74c4589-b671-4938-bf13-83a1d6741ed1,c589f92d-6952-488a-a1b0-4492668e5b0a,7900b0bf-209a-4724-9f23-13a996a97d41,1798f66a-e0fe-474e-8d01-64a5418b68c8,0a56bed5-c9a2-4bb7-b52e-6132fc61fd96,0904fdc7-6da2-46f1-af57-66b8e9e6dfac,2ec653e0-95b0-4043-94e0-99f5d63f307e,b3e38600-8817-4d65-b938-3b4ae9a41e01,99b169ba-d3cb-4f71-af34-2afeeac777ab,5644d6fe-4848-4a2b-8a61-68d103cd5cbf,80818356-6635-40dc-8e6c-926a85c87589,862608d9-197d-4a2a-83aa-fa0c2d5a3708,2762bf1e-baca-43b8-bd6f-b7182370b401,97a0b96e-ecd8-42c6-bad2-09bd3feb4d04,66ded87c-a353-41c0-8781-2c4b61c4f325,15432774-3810-4e00-ade5-e16623a728c1,4c69268c-f62f-4fcb-a8a0-8201dcc3378b,465101c5-3dc4-420f-9bd0-b6302966c8c3,bca5baba-f729-46aa-9bb5-8b788bd97b89,d3e8988f-d2fd-47f1-a793-ef54631e0946,bbc379a9-6b56-4ae0-96fd-3a284980ff24,1413731e-eeba-460f-a50a-7373c9381ca9,211ff1e7-a67e-409c-8c89-fa537285866b,99b336e3-9550-46c7-a9b3-f926d75cd931,5bc7ca57-1109-4357-987a-341016d35a3c,a3d60675-0768-4f24-a297-cce3f825ebbd,8919598e-7fdc-4729-a248-567de6efffed,c80c0ef6-d2b0-4902-8ea6-55d44ede0c34,591c51ac-c5db-4629-ac89-5ad3ac5f162b,54a19ce7-f7ae-44ff-b641-3ef4761a3659,1d2834d5-f706-41c4-b683-128d3cc552f7,b4e60d99-1c15-46b7-ae5d-97b7173e5dc4,35fe4f04-a52e-4e39-8cbd-c621981a8906,b213907e-ab0f-4f1c-a506-99a72ff0e003,aff9ca1f-2ed8-48f8-bc71-1d799c62173e,5fc6c5f2-503f-44a8-8824-877a29e6978a,d4192ffb-f152-4847-86ac-7764c566b12c,dd87591b-836d-4a41-baae-b6caddbf63c2,aa023bc9-fe57-405a-9efd-31e47ea574a1,29deed9a-3a5d-470a-a8af-0b2a552fd80d,49bcf8fa-45c9-4fc9-8902-0dc1ec008859,0f886b1b-980e-40fb-a83b-4233ead3724b,c0a2bd4d-78da-4def-8adf-87a49219245d,28182f25-b45e-4bbc-8237-855c1b1b2707,8fcc8b85-0af8-4f8e-b71c-38f005b954bf,7f33162d-c506-4f5b-beaa-cefe330cf2ec,bebf6f65-4ae6-438e-9d6a-2b9aabc873c2,ec68b5a6-52b6-4082-a582-476e58bf90e0,6a3db355-4499-4d04-8c99-d10bc4ce2d18,082db034-c7f5-46a2-89fb-ff1f7ff0e457,8343edfa-e7d9-43e0-aaaa-dc67ff9dd119,ee5035c4-35bf-47d5-841e-6e12aca5cfee,e9e8adc9-b54e-4976-b297-e23aa2df6832,8252ddd9-6599-49ee-a4e9-fd27746fb5a9,bd901546-8150-40a8-aa0f-51a82b3a2d59,0632bfd7-db4d-4ac5-bab5-b7ab904c3e46,3b96b3dc-94b0-4542-8f49-98606452ccab,02a9fcb3-da0d-4aa8-9474-635c183169c9,e62059ab-ff46-4e0c-9eff-2decdb207982,747d3ccf-b5c5-4980-bb64-6ae8ddcee5b1,0a982709-e637-4f7d-a2e1-73b5f32b7a1d,03715e4f-f97b-425b-b614-c376c2c16e84,bb9bbe14-e64d-49d2-8e4b-8f31e4c983bc,09c205f5-78ea-4663-8890-831607f4cf62,dfa99abe-f5d4-40e5-ba3a-c5ef9c550fea,eebe099e-d160-4057-856c-f55fab3c4adb,cf73bd0c-86c0-4d54-ba7c-087441360b6d,2b29c12d-8e27-4990-9440-2786b88301a3,8b96868d-1a4d-4514-a9dd-89f49180c4e7,656976e3-e853-4cee-a124-2e7e712ff493,6bbccafb-7d1d-4c0b-8da4-bb0e82bb2273,f536c2b3-a0e7-4d05-91d7-536a00ae29ee,665809e0-1fef-415c-8905-eea6c3678f51,eb9c7466-10ea-4b97-8343-e49757cacb95,77597dd5-1b65-4d37-bd7e-e1d5be1ff87f,81e47ace-360e-4a35-86c3-6f65a5c7687c,6327c5a0-abe1-4345-805d-1c260c4ecaec,6cbfc6ab-eac4-4003-8f53-8783f2a1221d,fe49567b-18de-4bfe-833f-6e397a25de40,04b3b7e5-3835-4c1b-91d6-509272fddc6c,f3526130-6c3b-4284-873f-cd75442e027e,01d4ef02-07b6-473a-a179-ec319584908d,66315173-e245-4b06-8c26-e77059cc3426,c47cb593-25a4-40c5-ae5d-440289817f19,cc9e6490-0597-4607-95f9-8ac1d6f6c455,a078e9a3-49b1-4f49-b866-e99f70594938,497ad9f5-eef4-40fb-840d-5b2810d6b9bf,a3e20be3-dc05-496b-944a-097a68e7dd83,70543812-7abc-43bc-b608-379a0592cde6,29e1fed7-1a4c-40e5-b7f6-81ac62fab3cd,fb3bc2f0-7e72-4c9d-8492-4aeffec41801,ab4e120d-7ad6-438a-b118-1bd253093d79,f6b77287-9198-4f8d-acac-70ca6c652f2d,15b6869e-10f6-4f41-8aec-692aba57ba30,788870a1-d78a-4442-af00-2e87cd1ce1db,6cd6b63b-94dc-42b1-8f4e-cb17142fbd25,714de7f8-a8e8-4482-a4bf-f5d0f3723020,d2cb1940-3153-48cc-8c8d-d2cf70332948,bf58a8fa-ec84-4fb0-b00e-c8333ef93925,e64122c8-ce06-475b-9805-6961ea650cb2,d5c44e0a-8737-4def-a995-e4ec221cef1a,7d7d82f3-8f3d-471c-99ba-077bc5dc82e8,01e5b659-c60c-421e-ae58-9f08a5b46714,dabf1559-a24d-4cd7-8046-d96b6ce54c9a,df8f9be1-92ec-4400-8775-53a9cb2e7eb8,f6dec6ec-a133-4346-92d3-3b56e10d324e,db20dbc4-5675-47a7-bf0e-f44a52eeaf0a,e695857a-44a8-4c93-9d11-4a7d248d55ae,dc4dced3-d2eb-4f6f-9166-3b5acaeaaaeb,7896a1ee-5d58-4b81-9b7a-e62f3e37f518,cb4a7506-1886-475d-9469-bd9589f499f3,cbba0bb7-55f8-4c13-9442-8d5a2b2b2e43,f62ad370-be99-4a5c-90ff-e1bb072fd47c,d232f616-b100-4888-8136-d0069b78dc78,636c6670-f160-47ac-b3bd-012e29973e74,bcdf135d-0a44-47b8-ab17-63c5309d7b0e,4669545a-abf3-4afe-85bb-328a075315e9,ee85e191-9acb-4403-a43a-5bf13ca26b7e,d1b282fd-5223-4335-a779-be4ab3ec5b55,d658396e-1ee6-401e-88cf-b6e407d2c031,ea1d0039-a4fc-477f-8b2e-d3bd756fbbe1,da261097-4bc8-4f1e-a1ed-1eaed03dfd70,e395d78a-9468-4f00-8ee3-373a82a1d419,08ffd8d5-58a9-49d3-9e2f-2842c00849b4,97efa80a-4ecd-43fb-a289-289abc60ca94,a2d4f566-a64b-4ca3-8d48-16d2f940af37,88d23859-766a-4c47-85f3-fa5cdf737ed5,5bc8101d-a2c5-4d87-be82-49995f435fd3,996ab3eb-d1c3-451b-aaa1-7387038001f2,8e77ef03-966c-4fca-b51b-139b2ee7a272,f7cf635f-e9eb-4d10-a531-648dd490481b,b7376c8e-4ec1-46a6-af1a-227582bcda9b,e0704f67-1d00-4f4a-a3dc-499d83cb50d6,fc7fbbe1-41c2-4ec1-b73a-d769c22b46cd,63f6dd8e-1843-4d8b-886e-c477e1b4ad5e,cd338df8-5fbc-4160-bd64-6bc90607484f,d6ffa014-4c9a-44a9-862d-0bffea1b66de,961dbacb-6fa9-499b-a267-53b1874ccf01,80eaf06e-ad54-4c66-969d-6964260c2eb7,ac296d98-d5f5-4735-bca4-12c27ec244d7,15a4c271-306e-47df-a5b5-451c654cf9ea,013e1dd1-f6f2-4ae9-afa2-00d17a8fdffa,5c21cab1-3c88-4f55-8ae5-01761be2790a,68dc365b-d3fe-4cca-9991-08fbba6e50c6,252418fb-95b6-49f4-a528-13e8ca61983e,3986eded-5b31-4789-9a52-f431dae94f76}'::uuid[])))
  • Rows Removed by Filter: 1,518
  • Heap Blocks: exact=682
69. 0.171 0.171 ↓ 47.4 1,943 1

Bitmap Index Scan on ix_l2_party_group_rel_idx3 (cost=0.00..4.59 rows=41 width=0) (actual time=0.171..0.171 rows=1,943 loops=1)

  • Index Cond: (workgroup_id = wg.workgroup_id)
70. 12.714 12.714 ↑ 9.1 12 4,238

CTE Scan on weeks_limited wl_1 (cost=0.00..2.18 rows=109 width=4) (actual time=0.000..0.003 rows=12 loops=4,238)

71. 8.476 8.476 ↑ 1.0 1 4,238

Index Scan using ix_l1_questionnaire_response_questionnaire on questionnaire_response qr (cost=0.42..0.51 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=4,238)

  • Index Cond: (questionnaire_id = q.questionnaire_id)
72.          

CTE dim_hist

73. 0.013 169.299 ↑ 9.1 12 1

Hash Left Join (cost=0.07..4.60 rows=109 width=44) (actual time=169.269..169.299 rows=12 loops=1)

  • Hash Cond: ((w_2.year_week_iso = dr.year_week_iso) AND (wg_1.workgroup_id = dr.workgroup_id))
74. 0.007 0.449 ↑ 9.1 12 1

Nested Loop (cost=0.03..3.74 rows=109 width=36) (actual time=0.427..0.449 rows=12 loops=1)

75. 0.009 0.009 ↑ 1.0 1 1

CTE Scan on workgroups wg_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=1)

76. 0.015 0.433 ↑ 9.1 12 1

Hash Left Join (cost=0.03..2.63 rows=109 width=20) (actual time=0.417..0.433 rows=12 loops=1)

  • Hash Cond: (w_2.year_week_iso = ds_1.year_week_iso)
77. 0.291 0.291 ↑ 9.1 12 1

CTE Scan on weeks_limited w_2 (cost=0.00..2.18 rows=109 width=12) (actual time=0.286..0.291 rows=12 loops=1)

78. 0.004 0.127 ↓ 10.0 10 1

Hash (cost=0.02..0.02 rows=1 width=12) (actual time=0.127..0.127 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
79. 0.123 0.123 ↓ 10.0 10 1

CTE Scan on date_survey ds_1 (cost=0.00..0.02 rows=1 width=12) (actual time=0.109..0.123 rows=10 loops=1)

80. 0.008 168.837 ↓ 10.0 10 1

Hash (cost=0.02..0.02 rows=1 width=28) (actual time=168.837..168.837 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
81. 168.829 168.829 ↓ 10.0 10 1

CTE Scan on date_responses dr (cost=0.00..0.02 rows=1 width=28) (actual time=167.188..168.829 rows=10 loops=1)

82.          

CTE date_topics

83. 0.171 169.865 ↑ 35.6 300 1

Nested Loop (cost=0.00..270.39 rows=10,682 width=172) (actual time=169.502..169.865 rows=300 loops=1)

84. 0.319 0.319 ↑ 3.9 25 1

CTE Scan on topics t_1 (cost=0.00..2.25 rows=98 width=80) (actual time=0.227..0.319 rows=25 loops=1)

  • Filter: (topic_type <> ALL ('{Participation,RiskIndicator}'::text[]))
  • Rows Removed by Filter: 3
85. 169.375 169.375 ↑ 9.1 12 25

CTE Scan on dim_hist dh (cost=0.00..2.18 rows=109 width=44) (actual time=6.771..6.775 rows=12 loops=25)

86.          

CTE indices_mw

87. 0.315 171.093 ↓ 48.0 48 1

Unique (cost=287.50..287.52 rows=1 width=144) (actual time=170.651..171.093 rows=48 loops=1)

88. 0.413 170.778 ↓ 336.0 336 1

Sort (cost=287.50..287.50 rows=1 width=144) (actual time=170.649..170.778 rows=336 loops=1)

  • Sort Key: dt.year_week_iso, dt.dr, dt.workgroup_id, dt.topic_id, dt.topic_type, dt.topic_name, dt.status, ts_2.average_period
  • Sort Method: quicksort Memory: 72kB
89. 0.175 170.365 ↓ 336.0 336 1

Hash Join (cost=269.45..287.49 rows=1 width=144) (actual time=170.149..170.365 rows=336 loops=1)

  • Hash Cond: (((ts_2.topic_name)::text = dt.topic_name) AND ((ts_2.topic_type)::text = dt.topic_type))
90. 0.070 0.070 ↑ 1.4 182 1

Seq Scan on topic_settings ts_2 (cost=0.00..6.55 rows=255 width=25) (actual time=0.009..0.070 rows=182 loops=1)

91. 0.027 170.120 ↑ 3.3 48 1

Hash (cost=267.05..267.05 rows=160 width=140) (actual time=170.119..170.120 rows=48 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
92. 170.093 170.093 ↑ 3.3 48 1

CTE Scan on date_topics dt (cost=0.00..267.05 rows=160 width=140) (actual time=169.663..170.093 rows=48 loops=1)

  • Filter: ((topic_type = ANY ('{MainIndex,Index}'::text[])) OR (topic_name = 'managerIndex'::text))
  • Rows Removed by Filter: 252
93.          

CTE indices_val

94. 46.412 3,783.809 ↓ 1,020.0 1,020 1

GroupAggregate (cost=4,196.82..4,196.86 rows=1 width=156) (actual time=3,667.151..3,783.809 rows=1,020 loops=1)

  • Group Key: im.year_week_iso, im.workgroup_id, im.topic_id, im.topic_type, im.topic_name, ttr.child_topic_id
95. 208.339 3,737.397 ↓ 102,956.0 102,956 1

Sort (cost=4,196.82..4,196.83 rows=1 width=148) (actual time=3,667.129..3,737.397 rows=102,956 loops=1)

  • Sort Key: im.year_week_iso, im.workgroup_id, im.topic_id, im.topic_type, im.topic_name, ttr.child_topic_id
  • Sort Method: external merge Disk: 8,872kB
96. 101.260 3,529.058 ↓ 102,956.0 102,956 1

Hash Join (cost=34.62..4,196.81 rows=1 width=148) (actual time=3.404..3,529.058 rows=102,956 loops=1)

  • Hash Cond: (dd_4.year_week_iso = w_3.year_week_iso)
  • Join Filter: ((im.dr <= w_3.dr) AND ((w_3.dr - im.moving_window) < im.dr))
  • Rows Removed by Join Filter: 94,504
97. 211.134 3,427.787 ↓ 5,063.1 197,460 1

Nested Loop (cost=24.02..4,184.48 rows=39 width=164) (actual time=3.379..3,427.787 rows=197,460 loops=1)

98. 682.070 3,019.193 ↓ 5,063.1 197,460 1

Hash Join (cost=23.73..4,172.15 rows=39 width=164) (actual time=3.369..3,019.193 rows=197,460 loops=1)

  • Hash Cond: ((im.topic_id = ttr.parent_topic_id) AND (rda.topic_id = ttr.child_topic_id))
99. 785.620 2,336.838 ↓ 2,826.7 2,462,040 1

Hash Join (cost=0.03..4,143.88 rows=871 width=164) (actual time=2.274..2,336.838 rows=2,462,040 loops=1)

  • Hash Cond: (rda.workgroup_id = im.workgroup_id)
100. 1,551.181 1,551.181 ↑ 2.8 61,551 1

CTE Scan on raw_data rda (cost=0.00..3,482.22 rows=174,111 width=68) (actual time=2.231..1,551.181 rows=61,551 loops=1)

101. 0.018 0.037 ↓ 40.0 40 1

Hash (cost=0.02..0.02 rows=1 width=112) (actual time=0.037..0.037 rows=40 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
102. 0.019 0.019 ↓ 40.0 40 1

CTE Scan on indices_mw im (cost=0.00..0.02 rows=1 width=112) (actual time=0.002..0.019 rows=40 loops=1)

  • Filter: (status IS NULL)
  • Rows Removed by Filter: 8
103. 0.138 0.285 ↑ 1.0 428 1

Hash (cost=17.28..17.28 rows=428 width=32) (actual time=0.285..0.285 rows=428 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
104. 0.147 0.147 ↑ 1.0 428 1

Seq Scan on topic_topic_rel ttr (cost=0.00..17.28 rows=428 width=32) (actual time=0.005..0.147 rows=428 loops=1)

105. 197.460 197.460 ↑ 1.0 1 197,460

Index Scan using date_dim_new_pk on date_dim_new dd_4 (cost=0.29..0.32 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=197,460)

  • Index Cond: (date_dim_id = rda.date_dim_id)
106. 0.006 0.011 ↑ 27.2 12 1

Hash (cost=6.52..6.52 rows=326 width=12) (actual time=0.011..0.011 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
107. 0.005 0.005 ↑ 27.2 12 1

CTE Scan on weeks w_3 (cost=0.00..6.52 rows=326 width=12) (actual time=0.001..0.005 rows=12 loops=1)

108.          

CTE indices_final

109. 0.814 3,785.471 ↓ 40.0 40 1

HashAggregate (cost=0.04..0.06 rows=1 width=132) (actual time=3,785.432..3,785.471 rows=40 loops=1)

  • Group Key: indices_val.year_week_iso, indices_val.workgroup_id, indices_val.topic_id, indices_val.topic_type, indices_val.topic_name
110. 3,784.657 3,784.657 ↓ 1,020.0 1,020 1

CTE Scan on indices_val (cost=0.00..0.02 rows=1 width=156) (actual time=3,667.155..3,784.657 rows=1,020 loops=1)

111.          

CTE indices_status

112. 0.494 6,613.384 ↓ 48.0 48 1

Nested Loop Left Join (cost=0.31..9.21 rows=1 width=236) (actual time=6,612.190..6,613.384 rows=48 loops=1)

  • Join Filter: ((im_1.topic_id = i.topic_id) AND (im_1.workgroup_id = i.workgroup_id) AND (im_1.year_week_iso = i.year_week_iso))
  • Rows Removed by Join Filter: 1,880
113. 0.190 2,826.890 ↓ 48.0 48 1

Hash Right Join (cost=0.31..9.16 rows=1 width=196) (actual time=2,826.666..2,826.890 rows=48 loops=1)

  • Hash Cond: (((tos.topic_name)::text = im_1.topic_name) AND ((tos.topic_type)::text = im_1.topic_type))
  • Join Filter: ((x.avg_responses >= (tos.team_from)::numeric) AND (x.avg_responses < (tos.team_to)::numeric))
  • Rows Removed by Join Filter: 240
114. 0.089 0.089 ↑ 1.4 157 1

Seq Scan on topic_settings tos (cost=0.00..7.19 rows=220 width=53) (actual time=0.010..0.089 rows=157 loops=1)

  • Filter: ((team_type)::text = 'Normal'::text)
  • Rows Removed by Filter: 25
115. 0.149 2,826.611 ↓ 48.0 48 1

Hash (cost=0.29..0.29 rows=1 width=172) (actual time=2,826.610..2,826.611 rows=48 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
116. 0.217 2,826.462 ↓ 48.0 48 1

Nested Loop (cost=0.25..0.29 rows=1 width=172) (actual time=226.449..2,826.462 rows=48 loops=1)

117. 171.173 171.173 ↓ 48.0 48 1

CTE Scan on indices_mw im_1 (cost=0.00..0.02 rows=1 width=136) (actual time=170.652..171.173 rows=48 loops=1)

118. 2,655.072 2,655.072 ↑ 1.0 1 48

Function Scan on f_get_workgroup_average_responses x (cost=0.25..0.26 rows=1 width=36) (actual time=55.313..55.314 rows=1 loops=48)

119. 3,786.000 3,786.000 ↓ 40.0 40 48

CTE Scan on indices_final i (cost=0.00..0.02 rows=1 width=68) (actual time=78.863..78.875 rows=40 loops=48)

120.          

CTE clima_mw

121. 0.011 0.084 ↑ 4.3 12 1

Unique (cost=241.86..242.79 rows=52 width=140) (actual time=0.071..0.084 rows=12 loops=1)

122. 0.023 0.073 ↑ 4.4 12 1

Sort (cost=241.86..242.00 rows=53 width=140) (actual time=0.070..0.073 rows=12 loops=1)

  • Sort Key: dt_1.year_week_iso, dt_1.dr, dt_1.workgroup_id, dt_1.topic_id, dt_1.topic_type, dt_1.status
  • Sort Method: quicksort Memory: 26kB
123. 0.050 0.050 ↑ 4.4 12 1

CTE Scan on date_topics dt_1 (cost=0.00..240.34 rows=53 width=140) (actual time=0.005..0.050 rows=12 loops=1)

  • Filter: (topic_name = 'climaIndex'::text)
  • Rows Removed by Filter: 288
124.          

CTE clima_score

125. 0.957 115.148 ↓ 10.0 10 1

GroupAggregate (cost=4,866.39..4,866.42 rows=1 width=76) (actual time=113.833..115.148 rows=10 loops=1)

  • Group Key: cm.year_week_iso, cm.topic_id, cm.workgroup_id
126. 1.661 114.191 ↓ 2,667.0 2,667 1

Sort (cost=4,866.39..4,866.39 rows=1 width=68) (actual time=113.667..114.191 rows=2,667 loops=1)

  • Sort Key: cm.year_week_iso, cm.topic_id, cm.workgroup_id
  • Sort Method: quicksort Memory: 305kB
127. 37.629 112.530 ↓ 2,667.0 2,667 1

Nested Loop (cost=2.11..4,866.38 rows=1 width=68) (actual time=0.373..112.530 rows=2,667 loops=1)

  • Join Filter: (cm.year_week_iso = dd_5.year_week_iso)
  • Rows Removed by Join Filter: 29,337
128. 26.231 42.897 ↓ 141.6 32,004 1

Hash Join (cost=1.82..4,792.13 rows=226 width=72) (actual time=0.326..42.897 rows=32,004 loops=1)

  • Hash Cond: ((rda_1.topic_id = cm.topic_id) AND (rda_1.workgroup_id = cm.workgroup_id))
129. 16.635 16.635 ↑ 2.8 61,551 1

CTE Scan on raw_data rda_1 (cost=0.00..3,482.22 rows=174,111 width=68) (actual time=0.031..16.635 rows=61,551 loops=1)

130. 0.007 0.031 ↑ 4.3 12 1

Hash (cost=1.04..1.04 rows=52 width=36) (actual time=0.030..0.031 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
131. 0.024 0.024 ↑ 4.3 12 1

CTE Scan on clima_mw cm (cost=0.00..1.04 rows=52 width=36) (actual time=0.002..0.024 rows=12 loops=1)

132. 32.004 32.004 ↑ 1.0 1 32,004

Index Scan using date_dim_new_pk on date_dim_new dd_5 (cost=0.29..0.32 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=32,004)

  • Index Cond: (date_dim_id = rda_1.date_dim_id)
133.          

CTE clima_status

134. 0.026 115.274 ↑ 4.3 12 1

Hash Left Join (cost=0.04..1.93 rows=52 width=196) (actual time=115.258..115.274 rows=12 loops=1)

  • Hash Cond: ((cm_1.year_week_iso = cs.year_week_iso) AND (cm_1.topic_id = cs.topic_id) AND (cm_1.workgroup_id = cs.workgroup_id))
135. 0.077 0.077 ↑ 4.3 12 1

CTE Scan on clima_mw cm_1 (cost=0.00..1.04 rows=52 width=132) (actual time=0.072..0.077 rows=12 loops=1)

136. 0.010 115.171 ↓ 10.0 10 1

Hash (cost=0.02..0.02 rows=1 width=76) (actual time=115.171..115.171 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
137. 115.161 115.161 ↓ 10.0 10 1

CTE Scan on clima_score cs (cost=0.00..0.02 rows=1 width=76) (actual time=113.838..115.161 rows=10 loops=1)

138.          

CTE key_prio_unq

139. 0.005 0.070 ↑ 1.0 1 1

Aggregate (cost=5.75..5.76 rows=1 width=8) (actual time=0.070..0.070 rows=1 loops=1)

140. 0.019 0.065 ↑ 3.0 4 1

Hash Join (cost=4.12..5.72 rows=12 width=16) (actual time=0.048..0.065 rows=4 loops=1)

  • Hash Cond: (abr.area_spec_id = a.area_spec_id)
141. 0.012 0.012 ↑ 1.0 35 1

Seq Scan on area_block_rel abr (cost=0.00..1.35 rows=35 width=32) (actual time=0.004..0.012 rows=35 loops=1)

142. 0.003 0.034 ↑ 1.0 1 1

Hash (cost=4.11..4.11 rows=1 width=16) (actual time=0.034..0.034 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
143. 0.002 0.031 ↑ 1.0 1 1

Nested Loop (cost=0.00..4.11 rows=1 width=16) (actual time=0.028..0.031 rows=1 loops=1)

  • Join Filter: (ms.matrix_spec_id = a.matrix_spec_id)
144. 0.003 0.025 ↑ 1.0 1 1

Nested Loop (cost=0.00..3.06 rows=1 width=16) (actual time=0.023..0.025 rows=1 loops=1)

  • Join Filter: (c.campaign_spec_id = ms.campaign_spec_id)
145. 0.003 0.017 ↑ 1.0 1 1

Nested Loop (cost=0.00..2.04 rows=1 width=32) (actual time=0.016..0.017 rows=1 loops=1)

  • Join Filter: (c.campaign_spec_id = cs_1.campaign_spec_id)
146. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on campaign c (cost=0.00..1.01 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=1)

  • Filter: (campaign_id = 'ae4f98f5-fd13-b722-2c2c-50d5928391bd'::uuid)
147. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on campaign_spec cs_1 (cost=0.00..1.01 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=1)

148. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on matrix_spec ms (cost=0.00..1.01 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=1)

  • Filter: ((status)::text = 'Active'::text)
149. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on area_spec a (cost=0.00..1.04 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=1)

  • Filter: ((area_name)::text = 'keyPriorities'::text)
  • Rows Removed by Filter: 2
150.          

CTE key_prio_mw

151. 0.071 1.330 ↑ 1.1 48 1

Unique (cost=314.63..315.82 rows=52 width=266) (actual time=1.120..1.330 rows=48 loops=1)

152.          

Initplan (for Unique)

153. 0.072 0.072 ↑ 1.0 1 1

CTE Scan on key_prio_unq (cost=0.00..0.02 rows=1 width=8) (actual time=0.071..0.072 rows=1 loops=1)

154. 0.397 1.187 ↓ 6.3 336 1

Sort (cost=314.61..314.74 rows=53 width=266) (actual time=1.118..1.187 rows=336 loops=1)

  • Sort Key: dt_2.year_week_iso, dt_2.dr, dt_2.workgroup_id, dt_2.topic_id, dt_2.topic_name, dt_2.status, ts_3.average_period, ts_3.template_name
  • Sort Method: quicksort Memory: 72kB
155. 0.146 0.790 ↓ 6.3 336 1

Nested Loop (cost=0.00..313.09 rows=53 width=266) (actual time=0.246..0.790 rows=336 loops=1)

156. 0.301 0.301 ↓ 7.0 7 1

Seq Scan on topic_settings ts_3 (cost=0.00..72.21 rows=1 width=134) (actual time=0.230..0.301 rows=7 loops=1)

  • Filter: (((topic_type)::text = 'KeyPriorityScore'::text) AND (block_number_of_questions = $31) AND ((template_name)::text = reporting.f_get_text_param('dashboard_template'::character varying)))
  • Rows Removed by Filter: 175
157. 0.343 0.343 ↑ 1.1 48 7

CTE Scan on date_topics dt_2 (cost=0.00..240.34 rows=53 width=140) (actual time=0.010..0.049 rows=48 loops=7)

  • Filter: (topic_type = 'KeyPriorityScore'::text)
  • Rows Removed by Filter: 252
158.          

CTE key_prio_val

159. 4.934 264.451 ↓ 40.0 40 1

GroupAggregate (cost=4,826.29..4,826.32 rows=1 width=68) (actual time=256.665..264.451 rows=40 loops=1)

  • Group Key: kpm.year_week_iso, kpm.topic_id, kpm.workgroup_id
160. 8.702 259.517 ↓ 14,223.0 14,223 1

Sort (cost=4,826.29..4,826.30 rows=1 width=68) (actual time=256.606..259.517 rows=14,223 loops=1)

  • Sort Key: kpm.year_week_iso, kpm.topic_id, kpm.workgroup_id
  • Sort Method: quicksort Memory: 1,496kB
161. 82.694 250.815 ↓ 14,223.0 14,223 1

Nested Loop (cost=1.34..4,826.28 rows=1 width=68) (actual time=138.290..250.815 rows=14,223 loops=1)

  • Join Filter: ((kpm.dr <= w_4.dr) AND (dd_6.year_week_iso = w_4.year_week_iso) AND ((w_4.dr - kpm.moving_window) < kpm.dr))
  • Rows Removed by Join Filter: 306,417
162. 0.013 0.013 ↑ 27.2 12 1

CTE Scan on weeks w_4 (cost=0.00..6.52 rows=326 width=12) (actual time=0.001..0.013 rows=12 loops=1)

163. 72.880 168.108 ↓ 6,680.0 26,720 12

Materialize (cost=1.34..4,790.43 rows=4 width=84) (actual time=0.130..14.009 rows=26,720 loops=12)

164. 26.262 95.228 ↓ 6,680.0 26,720 1

Nested Loop (cost=1.34..4,790.41 rows=4 width=84) (actual time=1.555..95.228 rows=26,720 loops=1)

165. 25.780 42.246 ↓ 6,680.0 26,720 1

Hash Join (cost=1.05..4,789.15 rows=4 width=84) (actual time=1.538..42.246 rows=26,720 loops=1)

  • Hash Cond: ((rda_2.topic_id = kpm.topic_id) AND (rda_2.workgroup_id = kpm.workgroup_id))
166. 16.209 16.209 ↑ 2.8 61,551 1

CTE Scan on raw_data rda_2 (cost=0.00..3,482.22 rows=174,111 width=68) (actual time=0.011..16.209 rows=61,551 loops=1)

167. 0.018 0.257 ↓ 40.0 40 1

Hash (cost=1.04..1.04 rows=1 width=48) (actual time=0.257..0.257 rows=40 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
168. 0.239 0.239 ↓ 40.0 40 1

CTE Scan on key_prio_mw kpm (cost=0.00..1.04 rows=1 width=48) (actual time=0.002..0.239 rows=40 loops=1)

  • Filter: (status IS NULL)
  • Rows Removed by Filter: 8
169. 26.720 26.720 ↑ 1.0 1 26,720

Index Scan using date_dim_new_pk on date_dim_new dd_6 (cost=0.29..0.32 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=26,720)

  • Index Cond: (date_dim_id = rda_2.date_dim_id)
170.          

CTE key_prio_final

171. 0.430 3,083.962 ↑ 1.1 48 1

Hash Left Join (cost=8.40..12.00 rows=52 width=236) (actual time=324.863..3,083.962 rows=48 loops=1)

  • Hash Cond: ((kpm_1.block_number_of_questions = tos_1.block_number_of_questions) AND ((kpm_1.template_name)::text = (tos_1.template_name)::text))
  • Join Filter: ((x_1.avg_responses >= (tos_1.team_from)::numeric) AND (x_1.avg_responses < (tos_1.team_to)::numeric))
  • Rows Removed by Join Filter: 240
172. 0.190 3,083.466 ↑ 1.1 48 1

Nested Loop (cost=0.29..2.97 rows=52 width=326) (actual time=324.779..3,083.466 rows=48 loops=1)

173. 0.173 265.868 ↑ 1.1 48 1

Hash Left Join (cost=0.04..1.67 rows=52 width=290) (actual time=265.649..265.868 rows=48 loops=1)

  • Hash Cond: ((kpm_1.topic_id = kpv.topic_id) AND (kpm_1.year_week_iso = kpv.year_week_iso) AND (kpm_1.workgroup_id = kpv.workgroup_id))
174. 1.180 1.180 ↑ 1.1 48 1

CTE Scan on key_prio_mw kpm_1 (cost=0.00..1.04 rows=52 width=258) (actual time=1.121..1.180 rows=48 loops=1)

175. 0.027 264.515 ↓ 40.0 40 1

Hash (cost=0.02..0.02 rows=1 width=68) (actual time=264.515..264.515 rows=40 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
176. 264.488 264.488 ↓ 40.0 40 1

CTE Scan on key_prio_val kpv (cost=0.00..0.02 rows=1 width=68) (actual time=256.669..264.488 rows=40 loops=1)

177. 2,817.408 2,817.408 ↑ 1.0 1 48

Function Scan on f_get_workgroup_average_responses x_1 (cost=0.25..0.26 rows=1 width=36) (actual time=58.695..58.696 rows=1 loops=48)

178. 0.018 0.066 ↓ 1.6 30 1

Hash (cost=7.82..7.82 rows=19 width=154) (actual time=0.066..0.066 rows=30 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
179. 0.048 0.048 ↓ 1.6 30 1

Seq Scan on topic_settings tos_1 (cost=0.00..7.82 rows=19 width=154) (actual time=0.026..0.048 rows=30 loops=1)

  • Filter: ((topic_name IS NULL) AND ((topic_type)::text = 'KeyPriorityScore'::text) AND ((team_type)::text = 'Normal'::text))
  • Rows Removed by Filter: 152
180.          

CTE xnps_score

181. 0.069 0.547 ↓ 2.4 12 1

Unique (cost=275.04..275.17 rows=5 width=144) (actual time=0.449..0.547 rows=12 loops=1)

182. 0.091 0.478 ↓ 12.0 84 1

Sort (cost=275.04..275.06 rows=7 width=144) (actual time=0.448..0.478 rows=84 loops=1)

  • Sort Key: dt_3.year_week_iso, dt_3.dr, dt_3.workgroup_id, dt_3.topic_id, dt_3.status, ts_4.average_period
  • Sort Method: quicksort Memory: 36kB
183. 0.046 0.387 ↓ 12.0 84 1

Nested Loop (cost=0.00..274.94 rows=7 width=144) (actual time=0.071..0.387 rows=84 loops=1)

184. 0.053 0.053 ↓ 12.0 12 1

CTE Scan on date_topics dt_3 (cost=0.00..267.05 rows=1 width=140) (actual time=0.048..0.053 rows=12 loops=1)

  • Filter: ((topic_name = 'eNpsIndex'::text) AND (topic_type = 'Score'::text))
  • Rows Removed by Filter: 288
185. 0.288 0.288 ↑ 1.0 7 12

Seq Scan on topic_settings ts_4 (cost=0.00..7.82 rows=7 width=25) (actual time=0.011..0.024 rows=7 loops=12)

  • Filter: (((topic_name)::text = 'eNpsIndex'::text) AND ((topic_type)::text = 'Score'::text))
  • Rows Removed by Filter: 175
186.          

CTE xnps_score_val

187. 5.936 454.073 ↓ 10.0 10 1

GroupAggregate (cost=4,196.90..4,196.94 rows=1 width=60) (actual time=445.335..454.073 rows=10 loops=1)

  • Group Key: xs.year_week_iso, xs.workgroup_id, xs.topic_id
188. 8.925 448.137 ↓ 14,189.0 14,189 1

Sort (cost=4,196.90..4,196.91 rows=1 width=50) (actual time=445.148..448.137 rows=14,189 loops=1)

  • Sort Key: xs.year_week_iso, xs.workgroup_id, xs.topic_id
  • Sort Method: quicksort Memory: 1,493kB
189. 13.125 439.212 ↓ 14,189.0 14,189 1

Hash Join (cost=34.70..4,196.89 rows=1 width=50) (actual time=10.313..439.212 rows=14,189 loops=1)

  • Hash Cond: (dd_7.year_week_iso = w_5.year_week_iso)
  • Join Filter: ((xs.dr <= w_5.dr) AND ((w_5.dr - xs.moving_window) < xs.dr))
  • Rows Removed by Join Filter: 12,501
190. 28.089 426.077 ↓ 684.4 26,690 1

Nested Loop (cost=24.10..4,184.56 rows=39 width=66) (actual time=10.293..426.077 rows=26,690 loops=1)

191. 159.330 371.298 ↓ 684.4 26,690 1

Hash Join (cost=23.81..4,172.23 rows=39 width=66) (actual time=10.273..371.298 rows=26,690 loops=1)

  • Hash Cond: ((xs.topic_id = ttr_1.parent_topic_id) AND (rda_3.topic_id = ttr_1.child_topic_id))
192. 194.199 211.681 ↓ 706.7 615,510 1

Hash Join (cost=0.11..4,143.96 rows=871 width=82) (actual time=0.026..211.681 rows=615,510 loops=1)

  • Hash Cond: (rda_3.workgroup_id = xs.workgroup_id)
193. 17.469 17.469 ↑ 2.8 61,551 1

CTE Scan on raw_data rda_3 (cost=0.00..3,482.22 rows=174,111 width=50) (actual time=0.006..17.469 rows=61,551 loops=1)

194. 0.006 0.013 ↓ 10.0 10 1

Hash (cost=0.10..0.10 rows=1 width=48) (actual time=0.012..0.013 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
195. 0.007 0.007 ↓ 10.0 10 1

CTE Scan on xnps_score xs (cost=0.00..0.10 rows=1 width=48) (actual time=0.002..0.007 rows=10 loops=1)

  • Filter: (status IS NULL)
  • Rows Removed by Filter: 2
196. 0.140 0.287 ↑ 1.0 428 1

Hash (cost=17.28..17.28 rows=428 width=32) (actual time=0.286..0.287 rows=428 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 35kB
197. 0.147 0.147 ↑ 1.0 428 1

Seq Scan on topic_topic_rel ttr_1 (cost=0.00..17.28 rows=428 width=32) (actual time=0.006..0.147 rows=428 loops=1)

198. 26.690 26.690 ↑ 1.0 1 26,690

Index Scan using date_dim_new_pk on date_dim_new dd_7 (cost=0.29..0.32 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=26,690)

  • Index Cond: (date_dim_id = rda_3.date_dim_id)
199. 0.005 0.010 ↑ 27.2 12 1

Hash (cost=6.52..6.52 rows=326 width=12) (actual time=0.010..0.010 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
200. 0.005 0.005 ↑ 27.2 12 1

CTE Scan on weeks w_5 (cost=0.00..6.52 rows=326 width=12) (actual time=0.002..0.005 rows=12 loops=1)

201.          

CTE xnps_score_final

202. 454.097 454.097 ↓ 10.0 10 1

CTE Scan on xnps_score_val xsv (cost=0.00..0.03 rows=1 width=68) (actual time=445.343..454.097 rows=10 loops=1)

203.          

CTE xnps_status

204. 0.047 1,123.263 ↓ 2.4 12 1

Nested Loop Left Join (cost=0.53..9.70 rows=5 width=236) (actual time=1,123.116..1,123.263 rows=12 loops=1)

  • Join Filter: ((xs_1.topic_id = xsf.topic_id) AND (xs_1.year_week_iso = xsf.year_week_iso) AND (xs_1.workgroup_id = xsf.workgroup_id))
  • Rows Removed by Join Filter: 110
205. 0.093 669.076 ↓ 2.4 12 1

Hash Right Join (cost=0.53..9.51 rows=5 width=196) (actual time=668.997..669.076 rows=12 loops=1)

  • Hash Cond: (((tos_2.topic_name)::text = xs_1.topic_name) AND ((tos_2.topic_type)::text = xs_1.topic_type))
  • Join Filter: ((x_2.avg_responses >= (tos_2.team_from)::numeric) AND (x_2.avg_responses < (tos_2.team_to)::numeric))
  • Rows Removed by Join Filter: 60
206. 0.082 0.082 ↑ 1.4 157 1

Seq Scan on topic_settings tos_2 (cost=0.00..7.19 rows=220 width=53) (actual time=0.012..0.082 rows=157 loops=1)

  • Filter: ((team_type)::text = 'Normal'::text)
  • Rows Removed by Filter: 25
207. 0.036 668.901 ↓ 2.4 12 1

Hash (cost=0.45..0.45 rows=5 width=172) (actual time=668.900..668.901 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
208. 0.055 668.865 ↓ 2.4 12 1

Nested Loop (cost=0.25..0.45 rows=5 width=172) (actual time=55.511..668.865 rows=12 loops=1)

209. 0.578 0.578 ↓ 2.4 12 1

CTE Scan on xnps_score xs_1 (cost=0.00..0.10 rows=5 width=136) (actual time=0.451..0.578 rows=12 loops=1)

210. 668.232 668.232 ↑ 1.0 1 12

Function Scan on f_get_workgroup_average_responses x_2 (cost=0.25..0.26 rows=1 width=36) (actual time=55.686..55.686 rows=1 loops=12)

211. 454.140 454.140 ↓ 10.0 10 12

CTE Scan on xnps_score_final xsf (cost=0.00..0.02 rows=1 width=68) (actual time=37.112..37.845 rows=10 loops=12)

212.          

CTE std_scores_mw

213. 0.494 11.038 ↓ 180.0 180 1

Unique (cost=534.62..534.65 rows=1 width=146) (actual time=10.238..11.038 rows=180 loops=1)

214. 0.457 10.544 ↓ 180.0 180 1

Sort (cost=534.62..534.63 rows=1 width=146) (actual time=10.237..10.544 rows=180 loops=1)

  • Sort Key: dt_4.year_week_iso, dt_4.workgroup_id, dt_4.dr, dt_4.topic_id, dt_4.topic_name, dt_4.topic_type, dt_4.status, sss.question_frequency_per_year, sss.block_number_of_questions, sss.moving_window
  • Sort Method: quicksort Memory: 50kB
215. 0.234 10.087 ↓ 180.0 180 1

Hash Join (cost=253.15..534.61 rows=1 width=146) (actual time=9.754..10.087 rows=180 loops=1)

  • Hash Cond: (((dt_4.campaign_id)::uuid = sss.campaign_id) AND (dt_4.topic_id = sss.topic_id))
216. 0.117 0.117 ↓ 1.7 180 1

CTE Scan on date_topics dt_4 (cost=0.00..280.40 rows=105 width=172) (actual time=0.005..0.117 rows=180 loops=1)

  • Filter: ((topic_type = ANY ('{Score,CustomScore}'::text[])) AND (topic_name <> ALL ('{climaIndex,eNpsIndex,managerIndex}'::text[])))
  • Rows Removed by Filter: 120
217. 0.055 9.736 ↓ 4.4 110 1

Hash (cost=252.78..252.78 rows=25 width=38) (actual time=9.735..9.736 rows=110 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
218. 0.054 9.681 ↓ 4.4 110 1

Subquery Scan on sss (cost=252.03..252.78 rows=25 width=38) (actual time=0.728..9.681 rows=110 loops=1)

219. 9.627 9.627 ↓ 4.4 110 1

CTE Scan on all_topics (cost=252.03..252.53 rows=25 width=672) (actual time=0.726..9.627 rows=110 loops=1)

220.          

CTE moving_window

221. 0.108 0.177 ↑ 8.0 25 1

HashAggregate (cost=10.38..12.38 rows=200 width=151) (actual time=0.167..0.177 rows=25 loops=1)

  • Group Key: ts_5.topic_type, ts_5.topic_name, ts_5.block_number_of_questions, ts_5.question_frequency_per_year, ts_5.average_period, ts_5.template_name
222. 0.069 0.069 ↑ 1.4 182 1

Seq Scan on topic_settings ts_5 (cost=0.00..6.55 rows=255 width=151) (actual time=0.006..0.069 rows=182 loops=1)

223.          

CTE blocks

224. 0.024 0.069 ↑ 1.0 23 1

Hash Join (cost=4.16..5.92 rows=23 width=40) (actual time=0.049..0.069 rows=23 loops=1)

  • Hash Cond: (abr_1.area_spec_id = a_1.area_spec_id)
225. 0.013 0.013 ↑ 1.0 23 1

Seq Scan on area_block_rel abr_1 (cost=0.00..1.44 rows=23 width=40) (actual time=0.004..0.013 rows=23 loops=1)

  • Filter: (repetition_amount > 0)
  • Rows Removed by Filter: 12
226. 0.003 0.032 ↑ 1.0 3 1

Hash (cost=4.12..4.12 rows=3 width=32) (actual time=0.032..0.032 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
227. 0.003 0.029 ↑ 1.0 3 1

Nested Loop (cost=0.00..4.12 rows=3 width=32) (actual time=0.025..0.029 rows=3 loops=1)

  • Join Filter: (ms_1.matrix_spec_id = a_1.matrix_spec_id)
228. 0.003 0.020 ↑ 1.0 1 1

Nested Loop (cost=0.00..3.06 rows=1 width=32) (actual time=0.018..0.020 rows=1 loops=1)

  • Join Filter: (cs_2.campaign_spec_id = ms_1.campaign_spec_id)
229. 0.003 0.012 ↑ 1.0 1 1

Nested Loop (cost=0.00..2.03 rows=1 width=48) (actual time=0.011..0.012 rows=1 loops=1)

  • Join Filter: (c_1.campaign_spec_id = cs_2.campaign_spec_id)
230. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on campaign c_1 (cost=0.00..1.01 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=1)

231. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on campaign_spec cs_2 (cost=0.00..1.01 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=1)

232. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on matrix_spec ms_1 (cost=0.00..1.01 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=1)

  • Filter: ((status)::text = 'Active'::text)
233. 0.006 0.006 ↑ 1.0 3 1

Seq Scan on area_spec a_1 (cost=0.00..1.03 rows=3 width=32) (actual time=0.004..0.006 rows=3 loops=1)

234.          

CTE campaign_questions

235. 0.075 0.128 ↓ 2.4 84 1

Hash Join (cost=0.75..8.30 rows=35 width=52) (actual time=0.024..0.128 rows=84 loops=1)

  • Hash Cond: (bqr.block_spec_id = b.block_spec_id)
  • Join Filter: (bqr.priority <= b.priority)
  • Rows Removed by Join Filter: 18
236. 0.038 0.038 ↑ 1.0 162 1

Seq Scan on block_question_rel bqr (cost=0.00..5.62 rows=162 width=36) (actual time=0.002..0.038 rows=162 loops=1)

237. 0.009 0.015 ↑ 1.0 23 1

Hash (cost=0.46..0.46 rows=23 width=40) (actual time=0.015..0.015 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
238. 0.006 0.006 ↑ 1.0 23 1

CTE Scan on blocks b (cost=0.00..0.46 rows=23 width=40) (actual time=0.000..0.006 rows=23 loops=1)

239.          

CTE risk_indicators

240. 0.036 0.371 ↑ 1.0 3 1

GroupAggregate (cost=20.79..20.97 rows=3 width=54) (actual time=0.320..0.371 rows=3 loops=1)

  • Group Key: cq.campaign_id, ts_6.topic_id
  • Filter: (count(cq.*) > 0)
  • Rows Removed by Filter: 3
241. 0.091 0.335 ↓ 13.1 105 1

Sort (cost=20.79..20.81 rows=8 width=110) (actual time=0.303..0.335 rows=105 loops=1)

  • Sort Key: cq.campaign_id, ts_6.topic_id
  • Sort Method: quicksort Memory: 49kB
242. 0.060 0.244 ↓ 13.1 105 1

Hash Right Join (cost=19.82..20.67 rows=8 width=110) (actual time=0.156..0.244 rows=105 loops=1)

  • Hash Cond: (cq.question_spec_id = tqs_1.question_spec_id)
243. 0.042 0.042 ↓ 2.4 84 1

CTE Scan on campaign_questions cq (cost=0.00..0.70 rows=35 width=88) (actual time=0.008..0.042 rows=84 loops=1)

244. 0.039 0.142 ↓ 13.1 105 1

Hash (cost=19.72..19.72 rows=8 width=54) (actual time=0.141..0.142 rows=105 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
245. 0.046 0.103 ↓ 13.1 105 1

Nested Loop (cost=0.28..19.72 rows=8 width=54) (actual time=0.017..0.103 rows=105 loops=1)

246. 0.021 0.021 ↑ 1.0 3 1

Seq Scan on topic_spec ts_6 (cost=0.00..6.65 rows=3 width=38) (actual time=0.008..0.021 rows=3 loops=1)

  • Filter: ((type)::text = 'RiskIndicator'::text)
  • Rows Removed by Filter: 209
247. 0.036 0.036 ↓ 11.7 35 3

Index Only Scan using topic_question_spec_unq on topic_question_spec tqs_1 (cost=0.28..4.33 rows=3 width=32) (actual time=0.003..0.012 rows=35 loops=3)

  • Index Cond: (topic_id = ts_6.topic_id)
  • Heap Fetches: 0
248.          

CTE scores

249. 0.051 0.508 ↑ 1.5 23 1

HashAggregate (cost=17.94..18.29 rows=35 width=66) (actual time=0.499..0.508 rows=23 loops=1)

  • Group Key: b_1.campaign_id, ts_7.topic_id, b_1.repetition_amount
250. 0.080 0.457 ↓ 2.4 84 1

Hash Join (cost=10.04..17.59 rows=35 width=150) (actual time=0.310..0.457 rows=84 loops=1)

  • Hash Cond: (bqr_1.block_spec_id = b_1.block_spec_id)
  • Join Filter: (bqr_1.priority <= b_1.priority)
  • Rows Removed by Join Filter: 18
251. 0.102 0.102 ↑ 1.0 162 1

Seq Scan on block_question_rel bqr_1 (cost=0.00..5.62 rows=162 width=112) (actual time=0.027..0.102 rows=162 loops=1)

252. 0.012 0.275 ↑ 1.0 23 1

Hash (cost=9.75..9.75 rows=23 width=94) (actual time=0.275..0.275 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
253. 0.063 0.263 ↑ 1.0 23 1

Hash Join (cost=2.61..9.75 rows=23 width=94) (actual time=0.162..0.263 rows=23 loops=1)

  • Hash Cond: (ts_7.topic_id = bs.topic_id)
254. 0.058 0.058 ↑ 1.0 212 1

Seq Scan on topic_spec ts_7 (cost=0.00..6.12 rows=212 width=38) (actual time=0.006..0.058 rows=212 loops=1)

255. 0.010 0.142 ↑ 1.0 23 1

Hash (cost=2.32..2.32 rows=23 width=72) (actual time=0.142..0.142 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
256. 0.020 0.132 ↑ 1.0 23 1

Hash Join (cost=1.79..2.32 rows=23 width=72) (actual time=0.084..0.132 rows=23 loops=1)

  • Hash Cond: (b_1.block_spec_id = bs.block_spec_id)
257. 0.085 0.085 ↑ 1.0 23 1

CTE Scan on blocks b_1 (cost=0.00..0.46 rows=23 width=40) (actual time=0.051..0.085 rows=23 loops=1)

258. 0.013 0.027 ↑ 1.0 35 1

Hash (cost=1.35..1.35 rows=35 width=32) (actual time=0.027..0.027 rows=35 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
259. 0.014 0.014 ↑ 1.0 35 1

Seq Scan on block_spec bs (cost=0.00..1.35 rows=35 width=32) (actual time=0.004..0.014 rows=35 loops=1)

260.          

CTE window_scores

261. 0.025 0.761 ↓ 16.0 16 1

Hash Join (cost=4.02..5.12 rows=1 width=678) (actual time=0.718..0.761 rows=16 loops=1)

  • Hash Cond: (((s_2.topic_type)::text = (mw.topic_type)::text) AND (s_2.questions_in_block = mw.block_number_of_questions) AND (s_2.repetition_amount = mw.question_frequency_per_year))
262. 0.529 0.529 ↑ 1.5 23 1

CTE Scan on scores s_2 (cost=0.00..0.70 rows=35 width=678) (actual time=0.501..0.529 rows=23 loops=1)

263. 0.007 0.207 ↓ 6.0 6 1

Hash (cost=4.00..4.00 rows=1 width=130) (actual time=0.207..0.207 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
264. 0.200 0.200 ↓ 14.0 14 1

CTE Scan on moving_window mw (cost=0.00..4.00 rows=1 width=130) (actual time=0.170..0.200 rows=14 loops=1)

  • Filter: (topic_name IS NULL)
  • Rows Removed by Filter: 11
265.          

CTE key_priorities

266. 0.005 0.010 ↑ 1.0 1 1

HashAggregate (cost=0.79..0.80 rows=1 width=20) (actual time=0.010..0.010 rows=1 loops=1)

  • Group Key: scores.campaign_id
267. 0.005 0.005 ↓ 4.0 4 1

CTE Scan on scores (cost=0.00..0.79 rows=1 width=16) (actual time=0.001..0.005 rows=4 loops=1)

  • Filter: ((topic_type)::text = 'KeyPriorityScore'::text)
  • Rows Removed by Filter: 19
268.          

CTE window_key_priorities

269. 0.008 0.278 ↓ 4.0 4 1

Nested Loop (cost=0.00..55.83 rows=1 width=678) (actual time=0.123..0.278 rows=4 loops=1)

  • Join Filter: (kp.kp_cnt = mw_1.block_number_of_questions)
270. 0.009 0.026 ↓ 4.0 4 1

Nested Loop (cost=0.00..0.82 rows=1 width=674) (actual time=0.015..0.026 rows=4 loops=1)

  • Join Filter: (s_3.campaign_id = kp.campaign_id)
271. 0.005 0.005 ↓ 4.0 4 1

CTE Scan on scores s_3 (cost=0.00..0.79 rows=1 width=670) (actual time=0.001..0.005 rows=4 loops=1)

  • Filter: ((topic_type)::text = 'KeyPriorityScore'::text)
  • Rows Removed by Filter: 19
272. 0.012 0.012 ↑ 1.0 1 4

CTE Scan on key_priorities kp (cost=0.00..0.02 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=4)

273. 0.244 0.244 ↑ 1.0 1 4

CTE Scan on moving_window mw_1 (cost=0.00..55.00 rows=1 width=126) (actual time=0.060..0.061 rows=1 loops=4)

  • Filter: (((topic_type)::text = 'KeyPriorityScore'::text) AND ((template_name)::text = reporting.f_get_text_param('dashboard_template'::character varying)))
  • Rows Removed by Filter: 24
274.          

CTE clima

275. 0.005 0.005 ↑ 1.0 1 1

CTE Scan on scores s_4 (cost=0.00..0.79 rows=1 width=674) (actual time=0.004..0.005 rows=1 loops=1)

  • Filter: ((topic_name)::text = 'climaIndex'::text)
  • Rows Removed by Filter: 22
276.          

CTE manager_enps

277. 0.021 0.069 ↓ 2.0 2 1

Hash Join (cost=1.23..11.08 rows=1 width=674) (actual time=0.047..0.069 rows=2 loops=1)

  • Hash Cond: (((mw_2.topic_type)::text = (s_5.topic_type)::text) AND ((mw_2.topic_name)::text = (s_5.topic_name)::text))
278. 0.007 0.007 ↑ 8.0 25 1

CTE Scan on moving_window mw_2 (cost=0.00..4.00 rows=200 width=638) (actual time=0.000..0.007 rows=25 loops=1)

279. 0.013 0.021 ↑ 1.5 23 1

Hash (cost=0.70..0.70 rows=35 width=666) (actual time=0.021..0.021 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
280. 0.008 0.008 ↑ 1.5 23 1

CTE Scan on scores s_5 (cost=0.00..0.70 rows=35 width=666) (actual time=0.001..0.008 rows=23 loops=1)

281.          

SubPlan (for Hash Join)

282. 0.008 0.020 ↑ 1.0 1 2

Aggregate (cost=4.34..4.35 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=2)

283. 0.012 0.012 ↓ 2.7 8 2

Index Only Scan using topic_question_spec_unq on topic_question_spec tqs_2 (cost=0.28..4.33 rows=3 width=0) (actual time=0.003..0.006 rows=8 loops=2)

  • Index Cond: (topic_id = s_5.topic_id)
  • Heap Fetches: 0
284.          

CTE involved_topics

285. 1.403 4.613 ↓ 2.2 157 1

HashAggregate (cost=31.24..31.96 rows=72 width=48) (actual time=4.566..4.613 rows=157 loops=1)

  • Group Key: cq_1.campaign_id, tqs_3.topic_id, tqs2.question_spec_id
286. 1.406 3.210 ↓ 47.7 3,437 1

Nested Loop (cost=8.16..30.70 rows=72 width=48) (actual time=0.325..3.210 rows=3,437 loops=1)

287. 0.086 0.706 ↓ 24.4 122 1

Hash Join (cost=7.89..28.50 rows=5 width=48) (actual time=0.307..0.706 rows=122 loops=1)

  • Hash Cond: (tqs_3.question_spec_id = cq_1.question_spec_id)
288. 0.216 0.404 ↓ 7.1 157 1

Hash Join (cost=6.75..27.23 rows=22 width=48) (actual time=0.060..0.404 rows=157 loops=1)

  • Hash Cond: (tqs_3.topic_id = ts_8.topic_id)
289. 0.138 0.138 ↑ 1.0 590 1

Seq Scan on topic_question_spec tqs_3 (cost=0.00..18.90 rows=590 width=32) (actual time=0.003..0.138 rows=590 loops=1)

290. 0.005 0.050 ↑ 1.0 8 1

Hash (cost=6.65..6.65 rows=8 width=16) (actual time=0.050..0.050 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
291. 0.045 0.045 ↑ 1.0 8 1

Seq Scan on topic_spec ts_8 (cost=0.00..6.65 rows=8 width=16) (actual time=0.022..0.045 rows=8 loops=1)

  • Filter: ((type)::text = ANY ('{MainIndex,Index}'::text[]))
  • Rows Removed by Filter: 204
292. 0.027 0.216 ↓ 2.4 84 1

Hash (cost=0.70..0.70 rows=35 width=32) (actual time=0.216..0.216 rows=84 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
293. 0.189 0.189 ↓ 2.4 84 1

CTE Scan on campaign_questions cq_1 (cost=0.00..0.70 rows=35 width=32) (actual time=0.026..0.189 rows=84 loops=1)

294. 1.098 1.098 ↓ 9.3 28 122

Index Only Scan using topic_question_spec_unq on topic_question_spec tqs2 (cost=0.28..0.41 rows=3 width=32) (actual time=0.002..0.009 rows=28 loops=122)

  • Index Cond: (topic_id = tqs_3.topic_id)
  • Heap Fetches: 0
295.          

CTE campaign_indices

296. 0.086 5.210 ↓ 3.0 3 1

HashAggregate (cost=13.01..13.91 rows=1 width=58) (actual time=5.208..5.210 rows=3 loops=1)

  • Group Key: it.campaign_id, it.topic_id, ts_9.name, ts_9.type
  • Filter: (count(it.*) = count(cq_2.*))
  • Rows Removed by Filter: 5
297. 0.094 5.124 ↓ 2.2 157 1

Hash Left Join (cost=3.56..11.75 rows=72 width=182) (actual time=4.908..5.124 rows=157 loops=1)

  • Hash Cond: ((it.question_spec_id = cq_2.question_spec_id) AND (it.campaign_id = cq_2.campaign_id))
298. 0.103 4.957 ↓ 2.2 157 1

Hash Join (cost=2.34..9.97 rows=72 width=142) (actual time=4.828..4.957 rows=157 loops=1)

  • Hash Cond: (ts_9.topic_id = it.topic_id)
299. 0.056 0.056 ↑ 1.0 212 1

Seq Scan on topic_spec ts_9 (cost=0.00..6.12 rows=212 width=38) (actual time=0.003..0.056 rows=212 loops=1)

300. 0.057 4.798 ↓ 2.2 157 1

Hash (cost=1.44..1.44 rows=72 width=120) (actual time=4.797..4.798 rows=157 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
301. 4.741 4.741 ↓ 2.2 157 1

CTE Scan on involved_topics it (cost=0.00..1.44 rows=72 width=120) (actual time=4.578..4.741 rows=157 loops=1)

302. 0.033 0.073 ↓ 2.4 84 1

Hash (cost=0.70..0.70 rows=35 width=88) (actual time=0.073..0.073 rows=84 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
303. 0.040 0.040 ↓ 2.4 84 1

CTE Scan on campaign_questions cq_2 (cost=0.00..0.70 rows=35 width=88) (actual time=0.005..0.040 rows=84 loops=1)

304.          

CTE window_indices

305. 0.015 5.240 ↓ 3.0 3 1

Hash Join (cost=0.04..5.54 rows=1 width=674) (actual time=5.232..5.240 rows=3 loops=1)

  • Hash Cond: (((mw_3.topic_name)::text = (ci.topic_name)::text) AND ((mw_3.topic_type)::text = (ci.topic_type)::text))
306. 0.007 0.007 ↑ 8.0 25 1

CTE Scan on moving_window mw_3 (cost=0.00..4.00 rows=200 width=638) (actual time=0.000..0.007 rows=25 loops=1)

307. 0.004 5.218 ↓ 3.0 3 1

Hash (cost=0.02..0.02 rows=1 width=670) (actual time=5.217..5.218 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
308. 5.214 5.214 ↓ 3.0 3 1

CTE Scan on campaign_indices ci (cost=0.00..0.02 rows=1 width=670) (actual time=5.209..5.214 rows=3 loops=1)

309.          

CTE standard_questions

310. 0.332 2.168 ↓ 72.0 72 1

Nested Loop (cost=5.20..12.37 rows=1 width=62) (actual time=0.048..2.168 rows=72 loops=1)

311. 0.184 1.505 ↓ 331.0 331 1

Nested Loop (cost=4.93..12.03 rows=1 width=40) (actual time=0.032..1.505 rows=331 loops=1)

312. 0.080 0.241 ↓ 72.0 72 1

Nested Loop (cost=4.65..5.88 rows=1 width=40) (actual time=0.024..0.241 rows=72 loops=1)

313. 0.055 0.089 ↓ 72.0 72 1

Hash Join (cost=4.51..5.35 rows=1 width=56) (actual time=0.015..0.089 rows=72 loops=1)

  • Hash Cond: (cq_3.repetition_amount = mw_4.question_frequency_per_year)
314. 0.026 0.026 ↓ 2.4 84 1

CTE Scan on campaign_questions cq_3 (cost=0.00..0.70 rows=35 width=52) (actual time=0.000..0.026 rows=84 loops=1)

315. 0.002 0.008 ↓ 3.0 3 1

Hash (cost=4.50..4.50 rows=1 width=8) (actual time=0.008..0.008 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
316. 0.006 0.006 ↓ 3.0 3 1

CTE Scan on moving_window mw_4 (cost=0.00..4.50 rows=1 width=8) (actual time=0.002..0.006 rows=3 loops=1)

  • Filter: ((topic_name IS NULL) AND ((topic_type)::text = 'Question'::text))
  • Rows Removed by Filter: 22
317. 0.072 0.072 ↑ 1.0 1 72

Index Scan using block_spec_pk on block_spec bs_1 (cost=0.14..0.50 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=72)

  • Index Cond: (block_spec_id = cq_3.block_spec_id)
  • Filter: ((block_name)::text <> 'eNpsIndex'::text)
318. 1.080 1.080 ↓ 1.2 5 72

Index Only Scan using topic_question_spec_unq on topic_question_spec tqs_4 (cost=0.28..6.11 rows=4 width=32) (actual time=0.004..0.015 rows=5 loops=72)

  • Index Cond: (question_spec_id = cq_3.question_spec_id)
  • Heap Fetches: 0
319. 0.331 0.331 ↓ 0.0 0 331

Index Scan using pk_topic on topic_spec ts_10 (cost=0.27..0.34 rows=1 width=38) (actual time=0.001..0.001 rows=0 loops=331)

  • Index Cond: (topic_id = tqs_4.topic_id)
  • Filter: ((type)::text = 'Question'::text)
  • Rows Removed by Filter: 1
320.          

CTE enps_questions

321. 0.007 0.222 ↓ 1.3 4 1

Nested Loop (cost=2.00..14.79 rows=3 width=62) (actual time=0.071..0.222 rows=4 loops=1)

322. 0.015 0.167 ↓ 6.0 24 1

Nested Loop (cost=1.73..13.41 rows=4 width=40) (actual time=0.047..0.167 rows=24 loops=1)

323. 0.003 0.076 ↓ 4.0 4 1

Nested Loop (cost=1.45..7.26 rows=1 width=40) (actual time=0.040..0.076 rows=4 loops=1)

324. 0.029 0.057 ↓ 4.0 4 1

Hash Join (cost=1.45..2.25 rows=1 width=36) (actual time=0.035..0.057 rows=4 loops=1)

  • Hash Cond: (cq_4.block_spec_id = bs_2.block_spec_id)
325. 0.020 0.020 ↓ 2.4 84 1

CTE Scan on campaign_questions cq_4 (cost=0.00..0.70 rows=35 width=52) (actual time=0.000..0.020 rows=84 loops=1)

326. 0.001 0.008 ↑ 1.0 1 1

Hash (cost=1.44..1.44 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
327. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on block_spec bs_2 (cost=0.00..1.44 rows=1 width=16) (actual time=0.005..0.007 rows=1 loops=1)

  • Filter: ((block_name)::text = 'eNpsIndex'::text)
  • Rows Removed by Filter: 34
328. 0.016 0.016 ↑ 1.0 1 4

CTE Scan on moving_window mw_5 (cost=0.00..5.00 rows=1 width=4) (actual time=0.002..0.004 rows=1 loops=4)

  • Filter: (((topic_type)::text = 'Question'::text) AND ((topic_name)::text = 'eNpsIndex'::text))
  • Rows Removed by Filter: 24
329. 0.076 0.076 ↓ 1.5 6 4

Index Only Scan using topic_question_spec_unq on topic_question_spec tqs_5 (cost=0.28..6.11 rows=4 width=32) (actual time=0.003..0.019 rows=6 loops=4)

  • Index Cond: (question_spec_id = cq_4.question_spec_id)
  • Heap Fetches: 0
330. 0.048 0.048 ↓ 0.0 0 24

Index Scan using pk_topic on topic_spec ts_11 (cost=0.27..0.34 rows=1 width=38) (actual time=0.002..0.002 rows=0 loops=24)

  • Index Cond: (topic_id = tqs_5.topic_id)
  • Filter: ((type)::text = 'Question'::text)
  • Rows Removed by Filter: 1
331.          

CTE clima_question

332. 0.018 0.094 ↑ 3.0 1 1

Nested Loop (cost=2.00..9.78 rows=3 width=90) (actual time=0.061..0.094 rows=1 loops=1)

333. 0.023 0.076 ↓ 1.5 6 1

Nested Loop (cost=1.73..8.40 rows=4 width=32) (actual time=0.050..0.076 rows=6 loops=1)

334. 0.025 0.053 ↑ 1.0 1 1

Hash Join (cost=1.45..2.25 rows=1 width=32) (actual time=0.042..0.053 rows=1 loops=1)

  • Hash Cond: (cq_5.block_spec_id = bs_3.block_spec_id)
335. 0.020 0.020 ↓ 2.4 84 1

CTE Scan on campaign_questions cq_5 (cost=0.00..0.70 rows=35 width=48) (actual time=0.000..0.020 rows=84 loops=1)

336. 0.008 0.008 ↑ 1.0 1 1

Hash (cost=1.44..1.44 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB