explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OSz

Settings
# exclusive inclusive rows x rows loops node
1. 10.306 4,229,512.768 ↓ 1,600.0 1,600 1

Sort (cost=161,380.70..161,380.70 rows=1 width=387) (actual time=4,229,512.352..4,229,512.768 rows=1,600 loops=1)

  • Sort Key: e.id
  • Sort Method: quicksort Memory: 960kB
2. 31.785 4,229,502.462 ↓ 1,600.0 1,600 1

Nested Loop Left Join (cost=82,058.59..161,380.69 rows=1 width=387) (actual time=3,447.943..4,229,502.462 rows=1,600 loops=1)

3. 6.565 4,178,289.877 ↓ 1,600.0 1,600 1

Nested Loop Left Join (cost=82,058.16..161,378.82 rows=1 width=381) (actual time=3,344.562..4,178,289.877 rows=1,600 loops=1)

4. 6.859 4,177,985.712 ↓ 1,600.0 1,600 1

Nested Loop Left Join (cost=82,057.73..161,375.05 rows=1 width=364) (actual time=3,344.552..4,177,985.712 rows=1,600 loops=1)

5. 5.566 4,177,814.053 ↓ 1,600.0 1,600 1

Nested Loop Left Join (cost=82,057.29..161,371.28 rows=1 width=339) (actual time=3,344.543..4,177,814.053 rows=1,600 loops=1)

6. 6.347 4,177,773.287 ↓ 1,600.0 1,600 1

Nested Loop Left Join (cost=82,056.86..161,367.52 rows=1 width=314) (actual time=3,344.533..4,177,773.287 rows=1,600 loops=1)

7. 6.831 4,177,754.140 ↓ 1,600.0 1,600 1

Nested Loop Left Join (cost=82,056.43..161,363.75 rows=1 width=289) (actual time=3,344.523..4,177,754.140 rows=1,600 loops=1)

8. 6.841 4,177,529.709 ↓ 1,600.0 1,600 1

Nested Loop Left Join (cost=82,056.00..161,359.98 rows=1 width=264) (actual time=3,344.512..4,177,529.709 rows=1,600 loops=1)

9. 6.922 4,177,497.268 ↓ 1,600.0 1,600 1

Nested Loop Left Join (cost=82,055.56..161,356.21 rows=1 width=239) (actual time=3,344.501..4,177,497.268 rows=1,600 loops=1)

10. 12.120 4,177,477.546 ↓ 1,600.0 1,600 1

Nested Loop Left Join (cost=82,055.13..161,352.45 rows=1 width=214) (actual time=3,344.487..4,177,477.546 rows=1,600 loops=1)

11. 17.552 4,177,441.426 ↓ 1,600.0 1,600 1

Nested Loop Left Join (cost=82,054.70..161,348.68 rows=1 width=181) (actual time=3,342.520..4,177,441.426 rows=1,600 loops=1)

12. 98,117.809 4,123,257.474 ↓ 1,600.0 1,600 1

Nested Loop (cost=82,054.27..161,344.91 rows=1 width=156) (actual time=3,214.134..4,123,257.474 rows=1,600 loops=1)

  • Join Filter: (e.id = dp_plan_evidence_13.evidenceid)
  • Rows Removed by Join Filter: 492519736
13. 2,848.605 3,107.884 ↓ 1,609.0 1,609 1

Bitmap Heap Scan on mdl_dp_plan_evidence_info_data dp_plan_evidence_13 (cost=4,316.59..60,761.56 rows=1 width=33) (actual time=422.568..3,107.884 rows=1,609 loops=1)

  • Recheck Cond: (fieldid = 13)
  • Rows Removed by Index Recheck: 1527728
  • Filter: ((data ~~* '%hate%'::text) AND (data ~~* '%crime%'::text))
  • Rows Removed by Filter: 404926
14. 259.279 259.279 ↑ 1.0 406,535 1

Bitmap Index Scan on mdl_dpplanevidinfodata_fie_ix (cost=0.00..4,316.59 rows=410,341 width=0) (actual time=259.279..259.279 rows=406,535 loops=1)

  • Index Cond: (fieldid = 13)
15. 201,107.296 4,022,031.781 ↓ 2.8 306,104 1,609

Hash Left Join (cost=77,737.68..99,224.23 rows=108,730 width=131) (actual time=1,294.475..2,499.709 rows=306,104 loops=1,609)

  • Hash Cond: (e.id = linkedevidence.evidenceid)
16. 247,187.434 3,820,924.480 ↓ 2.8 306,104 1,609

Hash Left Join (cost=77,721.03..98,799.47 rows=108,730 width=131) (actual time=1,294.473..2,374.720 rows=306,104 loops=1,609)

  • Hash Cond: (e.evidencetypeid = et.id)
17. 1,306,034.954 3,573,737.028 ↓ 2.8 306,104 1,609

Hash Join (cost=77,719.82..97,303.23 rows=108,730 width=128) (actual time=1,294.471..2,221.092 rows=306,104 loops=1,609)

  • Hash Cond: (dp_plan_evidence.id = e.id)
18. 186,063.151 186,063.151 ↑ 1.0 406,844 1,609

Seq Scan on mdl_dp_plan_evidence dp_plan_evidence (cost=0.00..11,878.44 rows=406,844 width=8) (actual time=0.016..115.639 rows=406,844 loops=1,609)

19. 324,334.175 2,081,638.923 ↓ 2.8 306,104 1,609

Hash (cost=74,448.70..74,448.70 rows=108,730 width=120) (actual time=1,293.747..1,293.747 rows=306,104 loops=1,609)

  • Buckets: 2048 Batches: 32 (originally 8) Memory Usage: 2622kB
20. 423,171.827 1,757,304.748 ↓ 2.8 306,104 1,609

Hash Join (cost=46,136.90..74,448.70 rows=108,730 width=120) (actual time=659.412..1,092.172 rows=306,104 loops=1,609)

  • Hash Cond: (cm.userid = e.userid)
21. 110,311.431 782,694.832 ↓ 1.2 52,647 1,609

Hash Join (cost=23,212.91..31,336.39 rows=45,697 width=53) (actual time=316.554..486.448 rows=52,647 loops=1,609)

  • Hash Cond: (cm.userid = auser.id)
22. 111,357.281 348,605.940 ↑ 1.0 52,647 1,609

Hash Join (cost=11,593.31..17,088.23 rows=53,279 width=17) (actual time=115.284..216.660 rows=52,647 loops=1,609)

  • Hash Cond: (cm.userid = u.id)
23. 31,544.445 59,565.180 ↑ 1.0 52,647 1,609

Nested Loop (cost=575.03..3,880.97 rows=53,279 width=9) (actual time=4.805..37.020 rows=52,647 loops=1,609)

24. 20.917 20.917 ↑ 1.0 1 1,609

Seq Scan on mdl_dp_plan_evidence_info_field dp_plan_evidence_6_fielddefault (cost=0.00..1.16 rows=1 width=1) (actual time=0.007..0.013 rows=1 loops=1,609)

  • Filter: (id = 6)
  • Rows Removed by Filter: 12
25. 20,406.947 27,999.818 ↑ 1.0 52,647 1,609

Bitmap Heap Scan on mdl_cohort_members cm (cost=575.03..3,347.02 rows=53,279 width=8) (actual time=4.778..17.402 rows=52,647 loops=1,609)

  • Recheck Cond: (cohortid = 34)
26. 7,592.871 7,592.871 ↑ 1.0 52,647 1,609

Bitmap Index Scan on mdl_cohomemb_coh_ix (cost=0.00..561.72 rows=53,279 width=0) (actual time=4.719..4.719 rows=52,647 loops=1,609)

  • Index Cond: (cohortid = 34)
27. 88,876.333 177,683.479 ↑ 1.0 170,990 1,609

Hash (cost=8,212.90..8,212.90 rows=170,990 width=8) (actual time=110.431..110.431 rows=170,990 loops=1,609)

  • Buckets: 8192 Batches: 4 Memory Usage: 1677kB
28. 88,807.146 88,807.146 ↑ 1.0 170,990 1,609

Seq Scan on mdl_user u (cost=0.00..8,212.90 rows=170,990 width=8) (actual time=0.005..55.194 rows=170,990 loops=1,609)

29. 104,390.311 323,777.461 ↓ 1.0 148,222 1,609

Hash (cost=8,640.38..8,640.38 rows=146,658 width=36) (actual time=201.229..201.229 rows=148,222 loops=1,609)

  • Buckets: 4096 Batches: 8 Memory Usage: 1043kB
30. 219,387.150 219,387.150 ↓ 1.0 148,222 1,609

Seq Scan on mdl_user auser (cost=0.00..8,640.38 rows=146,658 width=36) (actual time=0.009..136.350 rows=148,222 loops=1,609)

  • Filter: (deleted = 0)
  • Rows Removed by Filter: 22768
31. 326,155.563 551,438.089 ↑ 1.0 406,844 1,609

Hash (cost=11,878.44..11,878.44 rows=406,844 width=91) (actual time=342.721..342.721 rows=406,844 loops=1,609)

  • Buckets: 4096 Batches: 32 Memory Usage: 1667kB
32. 225,282.526 225,282.526 ↑ 1.0 406,844 1,609

Seq Scan on mdl_dp_plan_evidence e (cost=0.00..11,878.44 rows=406,844 width=91) (actual time=0.008..140.014 rows=406,844 loops=1,609)

33. 0.004 0.018 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=19) (actual time=0.018..0.018 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
34. 0.014 0.014 ↑ 1.0 9 1

Seq Scan on mdl_dp_evidence_type et (cost=0.00..1.09 rows=9 width=19) (actual time=0.011..0.014 rows=9 loops=1)

35. 0.000 0.005 ↓ 0.0 0 1

Hash (cost=14.90..14.90 rows=140 width=8) (actual time=0.005..0.005 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 0kB
36. 0.001 0.005 ↓ 0.0 0 1

Subquery Scan on linkedevidence (cost=12.10..14.90 rows=140 width=8) (actual time=0.005..0.005 rows=0 loops=1)

37. 0.003 0.004 ↓ 0.0 0 1

HashAggregate (cost=12.10..13.50 rows=140 width=8) (actual time=0.004..0.004 rows=0 loops=1)

38. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on mdl_dp_plan_evidence_relation er (cost=0.00..11.40 rows=140 width=8) (actual time=0.001..0.001 rows=0 loops=1)

39. 54,166.400 54,166.400 ↑ 4.0 1 1,600

Index Scan using mdl_dpplanevidinfodata_evi_ix on mdl_dp_plan_evidence_info_data dp_plan_evidence_8 (cost=0.43..3.73 rows=4 width=33) (actual time=33.852..33.854 rows=1 loops=1,600)

  • Index Cond: (evidenceid = dp_plan_evidence.id)
  • Filter: (fieldid = 8)
  • Rows Removed by Filter: 11
40. 24.000 24.000 ↑ 4.0 1 1,600

Index Scan using mdl_dpplanevidinfodata_evi_ix on mdl_dp_plan_evidence_info_data dp_plan_evidence_1 (cost=0.43..3.73 rows=4 width=41) (actual time=0.012..0.015 rows=1 loops=1,600)

  • Index Cond: (evidenceid = dp_plan_evidence.id)
  • Filter: (fieldid = 1)
  • Rows Removed by Filter: 11
41. 12.800 12.800 ↑ 4.0 1 1,600

Index Scan using mdl_dpplanevidinfodata_evi_ix on mdl_dp_plan_evidence_info_data dp_plan_evidence_3 (cost=0.43..3.73 rows=4 width=33) (actual time=0.005..0.008 rows=1 loops=1,600)

  • Index Cond: (evidenceid = dp_plan_evidence.id)
  • Filter: (fieldid = 3)
  • Rows Removed by Filter: 11
42. 25.600 25.600 ↑ 4.0 1 1,600

Index Scan using mdl_dpplanevidinfodata_evi_ix on mdl_dp_plan_evidence_info_data dp_plan_evidence_4 (cost=0.43..3.73 rows=4 width=33) (actual time=0.013..0.016 rows=1 loops=1,600)

  • Index Cond: (evidenceid = dp_plan_evidence.id)
  • Filter: (fieldid = 4)
  • Rows Removed by Filter: 11
43. 217.600 217.600 ↑ 4.0 1 1,600

Index Scan using mdl_dpplanevidinfodata_evi_ix on mdl_dp_plan_evidence_info_data dp_plan_evidence_5 (cost=0.43..3.73 rows=4 width=33) (actual time=0.134..0.136 rows=1 loops=1,600)

  • Index Cond: (evidenceid = dp_plan_evidence.id)
  • Filter: (fieldid = 5)
  • Rows Removed by Filter: 11
44. 12.800 12.800 ↑ 4.0 1 1,600

Index Scan using mdl_dpplanevidinfodata_evi_ix on mdl_dp_plan_evidence_info_data dp_plan_evidence_7 (cost=0.43..3.73 rows=4 width=33) (actual time=0.005..0.008 rows=1 loops=1,600)

  • Index Cond: (evidenceid = dp_plan_evidence.id)
  • Filter: (fieldid = 7)
  • Rows Removed by Filter: 11
45. 35.200 35.200 ↑ 4.0 1 1,600

Index Scan using mdl_dpplanevidinfodata_evi_ix on mdl_dp_plan_evidence_info_data dp_plan_evidence_6 (cost=0.43..3.73 rows=4 width=33) (actual time=0.019..0.022 rows=1 loops=1,600)

  • Index Cond: (evidenceid = dp_plan_evidence.id)
  • Filter: (fieldid = 6)
  • Rows Removed by Filter: 11
46. 164.800 164.800 ↑ 4.0 1 1,600

Index Scan using mdl_dpplanevidinfodata_evi_ix on mdl_dp_plan_evidence_info_data dp_plan_evidence_9 (cost=0.43..3.73 rows=4 width=33) (actual time=0.101..0.103 rows=1 loops=1,600)

  • Index Cond: (evidenceid = dp_plan_evidence.id)
  • Filter: (fieldid = 9)
  • Rows Removed by Filter: 11
47. 297.600 297.600 ↑ 4.0 1 1,600

Index Scan using mdl_dpplanevidinfodata_evi_ix on mdl_dp_plan_evidence_info_data dp_plan_evidence_10 (cost=0.43..3.73 rows=4 width=33) (actual time=0.110..0.186 rows=1 loops=1,600)

  • Index Cond: (evidenceid = dp_plan_evidence.id)
  • Filter: (fieldid = 10)
  • Rows Removed by Filter: 11
48. 51,180.800 51,180.800 ↑ 1.0 1 1,600

Index Scan using mdl_userinfodata_usefie_uix on mdl_user_info_data user_3 (cost=0.43..1.85 rows=1 width=22) (actual time=31.905..31.988 rows=1 loops=1,600)

  • Index Cond: ((userid = auser.id) AND (fieldid = 3))