explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zmkt

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 2,189.124 ↑ 190.1 16 1

Unique (cost=318,151.39..318,432.77 rows=3,042 width=383) (actual time=2,189.118..2,189.124 rows=16 loops=1)

2. 0.057 2,189.120 ↑ 190.1 16 1

Sort (cost=318,151.39..318,158.99 rows=3,042 width=383) (actual time=2,189.118..2,189.120 rows=16 loops=1)

  • Sort Key: documents.created_on, documents.document_id, documents.document_status_id, documents.user_id, documents.brand_id, documents.campaign_item_id, documents.template_id, documents.location_id, documents.filename, documents.comments, documents.deleted, documents.approver_user_id, documents.approver_comments, documents.approver_date_modified, documents.name, documents.quality, documents.archived, documents.campaign_item_expiration_id, documents.filesize, documents.saved_vals, (array_to_string(array_agg(DISTINCT shared_documents_locations.user_id), ','::text)), (array_to_string(array_agg(DISTINCT shared_documents_users.share_user_id), ','::text)), campaign_items.name, (CASE WHEN (alternatives: SubPlan 1 or hashed SubPlan 2) THEN true ELSE false END), (CASE WHEN (alternatives: SubPlan 3 or hashed SubPlan 4) THEN true ELSE false END), (CASE WHEN (alternatives: SubPlan 5 or hashed SubPlan 6) THEN true ELSE false END), (CASE WHEN (alternatives: SubPlan 7 or hashed SubPlan 8) THEN true ELSE false END), (CASE WHEN (alternatives: SubPlan 9 or hashed SubPlan 10) THEN true ELSE false END), (CASE WHEN (alternatives: SubPlan 11 or hashed SubPlan 12) THEN true ELSE false END), (CASE WHEN (alternatives: SubPlan 13 or hashed SubPlan 14) THEN true ELSE false END), (CASE WHEN (alternatives: SubPlan 15 or hashed SubPlan 16) THEN 1 ELSE 0 END), templates.name, templates.render_json, users.first, users.last
  • Sort Method: quicksort Memory: 33kB
3. 1,047.696 2,189.063 ↑ 190.1 16 1

GroupAggregate (cost=1,825.61..317,975.40 rows=3,042 width=383) (actual time=1,170.046..2,189.063 rows=16 loops=1)

  • Group Key: documents.document_id, document_status.name, campaign_items.name, campaign_items.campaign_item_id, templates.name, users.first, users.last, templates.render_json
4. 1,033.416 1,101.083 ↓ 1.7 5,020 1

Sort (cost=1,825.61..1,833.22 rows=3,042 width=383) (actual time=1,100.727..1,101.083 rows=5,020 loops=1)

  • Sort Key: documents.document_id, document_status.name, campaign_items.name, campaign_items.campaign_item_id, templates.name, users.first, users.last, templates.render_json
  • Sort Method: quicksort Memory: 2767kB
5. 1.501 67.667 ↓ 1.7 5,020 1

Hash Join (cost=1,393.44..1,649.62 rows=3,042 width=383) (actual time=35.826..67.667 rows=5,020 loops=1)

  • Hash Cond: (documents.user_id = users.user_id)
6. 1.507 63.319 ↓ 1.7 5,020 1

Hash Join (cost=1,106.22..1,305.36 rows=3,042 width=370) (actual time=32.973..63.319 rows=5,020 loops=1)

  • Hash Cond: (documents.template_id = templates.template_id)
7. 1.552 61.145 ↓ 1.7 5,020 1

Hash Join (cost=1,053.84..1,211.15 rows=3,042 width=312) (actual time=32.296..61.145 rows=5,020 loops=1)

  • Hash Cond: (documents.campaign_item_id = campaign_items.campaign_item_id)
8. 14.028 59.144 ↓ 1.7 5,020 1

Hash Left Join (cost=1,009.43..1,124.92 rows=3,042 width=277) (actual time=31.836..59.144 rows=5,020 loops=1)

  • Hash Cond: (documents.document_id = shared_documents_locations.document_id)
  • Filter: ((shared_documents_locations.user_id = 415) OR (shared_documents_locations.location_id = ANY ('{169,253,273,97,271,209,183,10,257,92,64,30,297,145,223,120,229,166,254,53,126,288,149,200,202,188,157,21,86,165,208,28,80,3,109,228,199,73,173,32,164,131,38,161,33,290,94,24,221,72,249,263,225,178,180,75,291,224,268,292,184,179,25,54,295,101,196,27,8,14,22,93,117,189,20,83,26,243,135,235,236,138,210,146,143,55,211,2,87,61,220,136,167,198,29,19,206,82,7,108,52,71,58,16,31,67,88,115,241,43,104,114,116,287,193,181,203,6,81,49,238,252,103,112,260,132,144,106,170,17,45,294,89,128,293,35,123,107,176,48,259,227,218,222,276,74,266,12,134,201,47,280,154,163,242,284,133,124,207,51,77,244,264,270,139,78,250,125,5,194,65,216,142,162,215,150,239,111,113,187,85,147,269,66,91,192,100,197,190,151,168,62,233,191,152,1,277,63,205,285,172,110,90,9,232,174,160,182,171,245,234,283,213,247,98,159,95,148,219,282,226,102,69,68,121,289,237,267,118,59,46,39,298,278,212,60,296,177,251,246,272,214,50,274,175,127,186,204,4,42,217,140,79,153,248,240,286,84,255,99,279,96,195,76,34,129,40,258,155,156,105,13,18,70,36,158,230,256,130,231,185,137,37,57,56,141,23,44,275,119,15,11,41,122,265,281}'::integer[])) OR (shared_documents_users.share_user_id = 415) OR (shared_documents_users.user_id = 415))
  • Rows Removed by Filter: 23831
9. 14.721 45.103 ↓ 9.5 28,851 1

Hash Right Join (cost=1,008.07..1,111.37 rows=3,042 width=277) (actual time=29.751..45.103 rows=28,851 loops=1)

  • Hash Cond: (shared_documents_users.document_id = documents.document_id)
10. 0.652 0.652 ↑ 1.0 5,067 1

Seq Scan on shared_documents_users (cost=0.00..78.67 rows=5,067 width=12) (actual time=0.005..0.652 rows=5,067 loops=1)

11. 15.842 29.730 ↓ 7.8 23,845 1

Hash (cost=970.05..970.05 rows=3,042 width=269) (actual time=29.730..29.730 rows=23,845 loops=1)

  • Buckets: 1024 Batches: 2 (originally 1) Memory Usage: 4097kB
12. 9.499 13.888 ↓ 7.8 23,845 1

Hash Join (cost=1.12..970.05 rows=3,042 width=269) (actual time=0.025..13.888 rows=23,845 loops=1)

  • Hash Cond: (documents.document_status_id = document_status.document_status_id)
13. 4.381 4.381 ↑ 1.0 27,382 1

Seq Scan on documents (cost=0.00..835.82 rows=27,382 width=205) (actual time=0.008..4.381 rows=27,382 loops=1)

14. 0.001 0.008 ↑ 1.0 1 1

Hash (cost=1.11..1.11 rows=1 width=68) (actual time=0.008..0.008 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
15. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on document_status (cost=0.00..1.11 rows=1 width=68) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: (name = 'Approved'::name)
  • Rows Removed by Filter: 8
16. 0.004 0.013 ↑ 1.0 16 1

Hash (cost=1.16..1.16 rows=16 width=12) (actual time=0.013..0.013 rows=16 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
17. 0.009 0.009 ↑ 1.0 16 1

Seq Scan on shared_documents_locations (cost=0.00..1.16 rows=16 width=12) (actual time=0.006..0.009 rows=16 loops=1)

18. 0.185 0.449 ↑ 1.0 818 1

Hash (cost=34.18..34.18 rows=818 width=35) (actual time=0.449..0.449 rows=818 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 56kB
19. 0.264 0.264 ↑ 1.0 818 1

Seq Scan on campaign_items (cost=0.00..34.18 rows=818 width=35) (actual time=0.004..0.264 rows=818 loops=1)

20. 0.286 0.667 ↑ 1.0 1,217 1

Hash (cost=37.17..37.17 rows=1,217 width=62) (actual time=0.667..0.667 rows=1,217 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 115kB
21. 0.381 0.381 ↑ 1.0 1,217 1

Seq Scan on templates (cost=0.00..37.17 rows=1,217 width=62) (actual time=0.008..0.381 rows=1,217 loops=1)

22. 1.279 2.847 ↑ 1.0 5,610 1

Hash (cost=217.10..217.10 rows=5,610 width=17) (actual time=2.847..2.847 rows=5,610 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 286kB
23. 1.568 1.568 ↑ 1.0 5,610 1

Seq Scan on users (cost=0.00..217.10 rows=5,610 width=17) (actual time=0.005..1.568 rows=5,610 loops=1)

24.          

SubPlan (for GroupAggregate)

25. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on campaign_item_actions (cost=4.31..14.55 rows=1 width=0) (never executed)

  • Recheck Cond: (campaign_item_id = campaign_items.campaign_item_id)
  • Filter: (action ~~* 'is_form'::text)
26. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on campaign_item_actions_pkey (cost=0.00..4.31 rows=4 width=0) (never executed)

  • Index Cond: (campaign_item_id = campaign_items.campaign_item_id)
27. 7.449 7.449 ↓ 0.0 0 1

Seq Scan on campaign_item_actions campaign_item_actions_1 (cost=0.00..44.26 rows=1 width=4) (actual time=7.449..7.449 rows=0 loops=1)

  • Filter: (action ~~* 'is_form'::text)
  • Rows Removed by Filter: 2181
28. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on campaign_item_actions campaign_item_actions_2 (cost=4.31..14.55 rows=1 width=0) (never executed)

  • Recheck Cond: (campaign_item_id = campaign_items.campaign_item_id)
  • Filter: (action ~~* 'allow_download'::text)
29. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on campaign_item_actions_pkey (cost=0.00..4.31 rows=4 width=0) (never executed)

  • Index Cond: (campaign_item_id = campaign_items.campaign_item_id)
30. 9.535 9.535 ↑ 1.0 532 1

Seq Scan on campaign_item_actions campaign_item_actions_3 (cost=0.00..44.26 rows=532 width=4) (actual time=0.034..9.535 rows=532 loops=1)

  • Filter: (action ~~* 'allow_download'::text)
  • Rows Removed by Filter: 1649
31. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on campaign_item_actions campaign_item_actions_4 (cost=4.31..14.55 rows=1 width=0) (never executed)

  • Recheck Cond: (campaign_item_id = campaign_items.campaign_item_id)
  • Filter: (action ~~* 'allow_greyscale'::text)
32. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on campaign_item_actions_pkey (cost=0.00..4.31 rows=4 width=0) (never executed)

  • Index Cond: (campaign_item_id = campaign_items.campaign_item_id)
33. 10.433 10.433 ↑ 1.0 309 1

Seq Scan on campaign_item_actions campaign_item_actions_5 (cost=0.00..44.26 rows=309 width=4) (actual time=0.040..10.433 rows=309 loops=1)

  • Filter: (action ~~* 'allow_greyscale'::text)
  • Rows Removed by Filter: 1872
34. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on campaign_item_actions campaign_item_actions_6 (cost=4.31..14.55 rows=1 width=0) (never executed)

  • Recheck Cond: (campaign_item_id = campaign_items.campaign_item_id)
  • Filter: (action ~~* 'allow_remove_crop_marks'::text)
35. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on campaign_item_actions_pkey (cost=0.00..4.31 rows=4 width=0) (never executed)

  • Index Cond: (campaign_item_id = campaign_items.campaign_item_id)
36. 12.867 12.867 ↑ 1.0 286 1

Seq Scan on campaign_item_actions campaign_item_actions_7 (cost=0.00..44.26 rows=286 width=4) (actual time=0.096..12.867 rows=286 loops=1)

  • Filter: (action ~~* 'allow_remove_crop_marks'::text)
  • Rows Removed by Filter: 1895
37. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on campaign_item_actions campaign_item_actions_8 (cost=4.31..14.55 rows=1 width=0) (never executed)

  • Recheck Cond: (campaign_item_id = campaign_items.campaign_item_id)
  • Filter: (action ~~* 'allow_customization'::text)
38. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on campaign_item_actions_pkey (cost=0.00..4.31 rows=4 width=0) (never executed)

  • Index Cond: (campaign_item_id = campaign_items.campaign_item_id)
39.          

SubPlan (for Bitmap Heap Scan)

40. 11.492 11.492 ↑ 1.0 433 1

Seq Scan on campaign_item_actions campaign_item_actions_9 (cost=0.00..44.26 rows=433 width=4) (actual time=0.011..11.492 rows=433 loops=1)

  • Filter: (action ~~* 'allow_customization'::text)
  • Rows Removed by Filter: 1748
41. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on campaign_item_actions campaign_item_actions_10 (cost=4.31..14.55 rows=1 width=0) (never executed)

  • Recheck Cond: (campaign_item_id = campaign_items.campaign_item_id)
  • Filter: (action ~~* 'allow_pdf'::text)
42. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on campaign_item_actions_pkey (cost=0.00..4.31 rows=4 width=0) (never executed)

  • Index Cond: (campaign_item_id = campaign_items.campaign_item_id)
43. 8.183 8.183 ↑ 1.0 334 1

Seq Scan on campaign_item_actions campaign_item_actions_11 (cost=0.00..44.26 rows=334 width=4) (actual time=0.040..8.183 rows=334 loops=1)

  • Filter: (action ~~* 'allow_pdf'::text)
  • Rows Removed by Filter: 1847
44. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on campaign_item_actions campaign_item_actions_12 (cost=4.31..14.55 rows=1 width=0) (never executed)

  • Recheck Cond: (campaign_item_id = campaign_items.campaign_item_id)
  • Filter: (action ~~* 'allow_jpeg'::text)
45. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on campaign_item_actions_pkey (cost=0.00..4.31 rows=4 width=0) (never executed)

  • Index Cond: (campaign_item_id = campaign_items.campaign_item_id)
46. 8.463 8.463 ↑ 1.0 279 1

Seq Scan on campaign_item_actions campaign_item_actions_13 (cost=0.00..44.26 rows=279 width=4) (actual time=0.038..8.463 rows=279 loops=1)

  • Filter: (action ~~* 'allow_jpeg'::text)
  • Rows Removed by Filter: 1902
47. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..2.04 rows=1 width=0) (never executed)

  • Join Filter: (orderable_items.orderable_items_id = campaign_item_orderable_items.orderable_items_id)
48. 0.000 0.000 ↓ 0.0 0

Seq Scan on orderable_items (cost=0.00..1.01 rows=1 width=4) (never executed)

  • Filter: active
49. 0.000 0.000 ↓ 0.0 0

Seq Scan on campaign_item_orderable_items (cost=0.00..1.01 rows=1 width=4) (never executed)

  • Filter: (campaign_items.campaign_item_id = campaign_item_id)
50. 0.002 0.022 ↑ 1.0 1 1

Nested Loop (cost=0.00..2.03 rows=1 width=4) (actual time=0.022..0.022 rows=1 loops=1)

  • Join Filter: (orderable_items_1.orderable_items_id = campaign_item_orderable_items_1.orderable_items_id)
51. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on orderable_items orderable_items_1 (cost=0.00..1.01 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=1)

  • Filter: active
52. 0.004 0.004 ↑ 1.0 1 1

Seq Scan on campaign_item_orderable_items campaign_item_orderable_items_1 (cost=0.00..1.01 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1)

Planning time : 4.944 ms
Execution time : 2,189.964 ms