explain.depesz.com

PostgreSQL's explain analyze made readable

Result: my9T

Settings
# exclusive inclusive rows x rows loops node
1. 0.331 48,078.737 ↑ 1.0 3,000 1

Limit (cost=1,704,437.69..1,704,445.19 rows=3,000 width=63) (actual time=48,078.166..48,078.737 rows=3,000 loops=1)

2. 1,920.663 48,078.406 ↑ 36.6 3,000 1

Sort (cost=1,704,437.69..1,704,712.14 rows=109,780 width=63) (actual time=48,078.163..48,078.406 rows=3,000 loops=1)

  • Sort Key: ((metric_metricresult.requested_time IS NULL)), metric_metricresult.requested_time, metric_metricresult.start
  • Sort Method: top-N heapsort Memory: 427kB
3. 2,132.986 46,157.743 ↓ 47.5 5,209,768 1

Hash Left Join (cost=1,348,310.70..1,697,548.59 rows=109,780 width=63) (actual time=15,908.696..46,157.743 rows=5,209,768 loops=1)

  • Hash Cond: (metric_retentionmetric.metric_ptr_id = t19.id)
  • Filter: ((NOT t19.local_processing) OR (t19.local_processing IS NULL))
4. 1,226.631 44,011.586 ↓ 22.7 5,209,768 1

Hash Left Join (cost=1,347,399.63..1,691,756.40 rows=229,700 width=67) (actual time=15,895.497..44,011.586 rows=5,209,768 loops=1)

  • Hash Cond: (metric_retentionmetricresult.metric_id = metric_retentionmetric.metric_ptr_id)
5. 3,284.623 42,784.241 ↓ 22.7 5,209,768 1

Hash Left Join (cost=1,347,350.95..1,688,262.22 rows=229,700 width=67) (actual time=15,894.758..42,784.241 rows=5,209,768 loops=1)

  • Hash Cond: (metric_metricresult.id = metric_retentionmetricresult.metricresult_ptr_id)
6. 1,422.941 39,330.592 ↓ 22.7 5,209,768 1

Hash Left Join (cost=1,335,522.82..1,674,108.81 rows=229,700 width=63) (actual time=15,725.429..39,330.592 rows=5,209,768 loops=1)

  • Hash Cond: (metric_eventlistmetricresult.metric_id = metric_eventlistmetric.metric_ptr_id)
  • Filter: ((NOT t16.local_processing) OR (t16.local_processing IS NULL))
  • Rows Removed by Filter: 852
7. 4,481.884 37,904.121 ↓ 10.8 5,210,620 1

Hash Left Join (cost=1,334,908.27..1,666,885.81 rows=480,615 width=67) (actual time=15,721.869..37,904.121 rows=5,210,620 loops=1)

  • Hash Cond: (metric_metricresult.id = metric_eventlistmetricresult.metricresult_ptr_id)
8. 1,393.468 33,287.447 ↓ 10.8 5,210,620 1

Hash Left Join (cost=1,323,660.19..1,648,382.54 rows=480,615 width=63) (actual time=15,586.906..33,287.447 rows=5,210,620 loops=1)

  • Hash Cond: (metric_activeusersmetricresult.metric_id = metric_activeusersmetric.metric_ptr_id)
  • Filter: ((NOT t13.local_processing) OR (t13.local_processing IS NULL))
9. 3,800.148 31,892.231 ↓ 5.2 5,210,620 1

Hash Left Join (cost=1,323,307.92..1,634,203.01 rows=1,005,618 width=67) (actual time=15,585.129..31,892.231 rows=5,210,620 loops=1)

  • Hash Cond: (metric_metricresult.id = metric_activeusersmetricresult.metricresult_ptr_id)
10. 2,277.470 28,061.882 ↓ 5.2 5,210,620 1

Hash Left Join (cost=1,321,344.61..1,619,648.97 rows=1,005,618 width=63) (actual time=15,554.885..28,061.882 rows=5,210,620 loops=1)

  • Hash Cond: (metric_urllistmetricresult.metric_id = metric_urllistmetric.metric_ptr_id)
  • Filter: ((NOT t10.local_processing) OR (t10.local_processing IS NULL))
  • Rows Removed by Filter: 18633
11. 8,855.343 25,764.069 ↓ 2.5 5,229,253 1

Hash Right Join (cost=1,320,112.28..1,573,704.24 rows=2,104,113 width=67) (actual time=15,534.465..25,764.069 rows=5,229,253 loops=1)

  • Hash Cond: (metric_urllistmetricresult.metricresult_ptr_id = metric_metricresult.id)
12. 1,375.652 1,375.652 ↓ 1.1 5,616,537 1

Seq Scan on metric_urllistmetricresult (cost=0.00..160,159.50 rows=5,264,350 width=8) (actual time=0.018..1,375.652 rows=5,616,537 loops=1)

13. 2,669.898 15,533.074 ↓ 2.5 5,229,253 1

Hash (cost=1,271,207.87..1,271,207.87 rows=2,104,113 width=63) (actual time=15,533.074..15,533.074 rows=5,229,253 loops=1)

  • Buckets: 262144 Batches: 4 (originally 2) Memory Usage: 131073kB
14. 6,194.338 12,863.176 ↓ 2.5 5,229,253 1

Hash Left Join (cost=245,944.68..1,271,207.87 rows=2,104,113 width=63) (actual time=1,368.318..12,863.176 rows=5,229,253 loops=1)

  • Hash Cond: (metric_metricresult.id = metric_searchlistmetricresult.metricresult_ptr_id)
  • Filter: ((NOT t7.local_processing) OR (t7.local_processing IS NULL))
  • Rows Removed by Filter: 859
15. 5,363.668 6,417.365 ↓ 1.2 5,230,112 1

Bitmap Heap Scan on metric_metricresult (cost=229,969.92..1,194,284.34 rows=4,402,560 width=63) (actual time=1,116.652..6,417.365 rows=5,230,112 loops=1)

  • Recheck Cond: ((state)::text = ANY ('{new,processing}'::text[]))
  • Filter: ((requested_time IS NULL) OR ((request_delay_seconds)::double precision <= date_part('epoch'::text, ('2019-01-09 21:33:44.383201+03'::timestamp with time zone - requested_time))))
  • Rows Removed by Filter: 52423
  • Heap Blocks: exact=190341
16. 1,053.697 1,053.697 ↑ 1.7 5,286,431 1

Bitmap Index Scan on metric_metricresult_state_55fa73c28f07a886_uniq (cost=0.00..228,869.27 rows=8,725,619 width=0) (actual time=1,053.697..1,053.697 rows=5,286,431 loops=1)

  • Index Cond: ((state)::text = ANY ('{new,processing}'::text[]))
17. 69.310 251.473 ↓ 1.0 319,654 1

Hash (cost=12,126.42..12,126.42 rows=307,868 width=5) (actual time=251.473..251.473 rows=319,654 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 11550kB
18. 117.449 182.163 ↓ 1.0 319,654 1

Hash Left Join (cost=614.55..12,126.42 rows=307,868 width=5) (actual time=2.071..182.163 rows=319,654 loops=1)

  • Hash Cond: (metric_searchlistmetricresult.metric_id = metric_searchlistmetric.metric_ptr_id)
19. 62.682 62.682 ↓ 1.0 319,654 1

Seq Scan on metric_searchlistmetricresult (cost=0.00..7,278.68 rows=307,868 width=8) (actual time=0.016..62.682 rows=319,654 loops=1)

20. 0.153 2.032 ↑ 1.0 452 1

Hash (cost=608.90..608.90 rows=452 width=5) (actual time=2.032..2.032 rows=452 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
21. 0.421 1.879 ↑ 1.0 452 1

Nested Loop Left Join (cost=0.29..608.90 rows=452 width=5) (actual time=0.049..1.879 rows=452 loops=1)

22. 0.102 0.102 ↑ 1.0 452 1

Seq Scan on metric_searchlistmetric (cost=0.00..6.52 rows=452 width=4) (actual time=0.016..0.102 rows=452 loops=1)

23. 1.356 1.356 ↑ 1.0 1 452

Index Scan using metric_metric_pkey on metric_metric t7 (cost=0.29..1.32 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=452)

  • Index Cond: (metric_searchlistmetric.metric_ptr_id = id)
24. 1.679 20.343 ↑ 1.0 6,993 1

Hash (cost=1,142.46..1,142.46 rows=7,190 width=5) (actual time=20.343..20.343 rows=6,993 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 253kB
25. 9.851 18.664 ↑ 1.0 6,993 1

Hash Right Join (cost=193.78..1,142.46 rows=7,190 width=5) (actual time=2.750..18.664 rows=6,993 loops=1)

  • Hash Cond: (t10.id = metric_urllistmetric.metric_ptr_id)
26. 6.098 6.098 ↓ 1.0 27,439 1

Seq Scan on metric_metric t10 (cost=0.00..568.25 rows=27,425 width=5) (actual time=0.012..6.098 rows=27,439 loops=1)

27. 1.468 2.715 ↑ 1.0 6,993 1

Hash (cost=103.90..103.90 rows=7,190 width=4) (actual time=2.715..2.715 rows=6,993 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 246kB
28. 1.247 1.247 ↑ 1.0 6,993 1

Seq Scan on metric_urllistmetric (cost=0.00..103.90 rows=7,190 width=4) (actual time=0.018..1.247 rows=6,993 loops=1)

29. 15.308 30.201 ↑ 1.0 66,739 1

Hash (cost=1,127.58..1,127.58 rows=66,858 width=8) (actual time=30.201..30.201 rows=66,739 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 2607kB
30. 14.893 14.893 ↑ 1.0 66,739 1

Seq Scan on metric_activeusersmetricresult (cost=0.00..1,127.58 rows=66,858 width=8) (actual time=0.026..14.893 rows=66,739 loops=1)

31. 0.100 1.748 ↑ 1.0 226 1

Hash (cost=349.45..349.45 rows=226 width=5) (actual time=1.748..1.748 rows=226 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.228 1.648 ↑ 1.0 226 1

Nested Loop Left Join (cost=0.29..349.45 rows=226 width=5) (actual time=0.071..1.648 rows=226 loops=1)

33. 0.064 0.064 ↑ 1.0 226 1

Seq Scan on metric_activeusersmetric (cost=0.00..3.26 rows=226 width=4) (actual time=0.030..0.064 rows=226 loops=1)

34. 1.356 1.356 ↑ 1.0 1 226

Index Scan using metric_metric_pkey on metric_metric t13 (cost=0.29..1.52 rows=1 width=5) (actual time=0.006..0.006 rows=1 loops=226)

  • Index Cond: (metric_activeusersmetric.metric_ptr_id = id)
35. 61.729 134.790 ↓ 1.0 319,654 1

Hash (cost=7,329.70..7,329.70 rows=313,470 width=8) (actual time=134.790..134.790 rows=319,654 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 12487kB
36. 73.061 73.061 ↓ 1.0 319,654 1

Seq Scan on metric_eventlistmetricresult (cost=0.00..7,329.70 rows=313,470 width=8) (actual time=0.031..73.061 rows=319,654 loops=1)

37. 0.145 3.530 ↑ 1.0 452 1

Hash (cost=608.90..608.90 rows=452 width=5) (actual time=3.530..3.530 rows=452 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
38. 0.109 3.385 ↑ 1.0 452 1

Nested Loop Left Join (cost=0.29..608.90 rows=452 width=5) (actual time=0.061..3.385 rows=452 loops=1)

39. 0.112 0.112 ↑ 1.0 452 1

Seq Scan on metric_eventlistmetric (cost=0.00..6.52 rows=452 width=4) (actual time=0.032..0.112 rows=452 loops=1)

40. 3.164 3.164 ↑ 1.0 1 452

Index Scan using metric_metric_pkey on metric_metric t16 (cost=0.29..1.32 rows=1 width=5) (actual time=0.006..0.007 rows=1 loops=452)

  • Index Cond: (metric_eventlistmetric.metric_ptr_id = id)
41. 83.569 169.026 ↑ 1.0 403,295 1

Hash (cost=6,786.95..6,786.95 rows=403,295 width=8) (actual time=169.026..169.026 rows=403,295 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 15754kB
42. 85.457 85.457 ↑ 1.0 403,295 1

Seq Scan on metric_retentionmetricresult (cost=0.00..6,786.95 rows=403,295 width=8) (actual time=0.017..85.457 rows=403,295 loops=1)

43. 0.362 0.714 ↑ 1.0 1,808 1

Hash (cost=26.08..26.08 rows=1,808 width=4) (actual time=0.714..0.714 rows=1,808 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 64kB
44. 0.352 0.352 ↑ 1.0 1,808 1

Seq Scan on metric_retentionmetric (cost=0.00..26.08 rows=1,808 width=4) (actual time=0.040..0.352 rows=1,808 loops=1)

45. 5.965 13.171 ↓ 1.0 27,439 1

Hash (cost=568.25..568.25 rows=27,425 width=5) (actual time=13.171..13.171 rows=27,439 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 992kB
46. 7.206 7.206 ↓ 1.0 27,439 1

Seq Scan on metric_metric t19 (cost=0.00..568.25 rows=27,425 width=5) (actual time=0.006..7.206 rows=27,439 loops=1)