explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LkK6

Settings
# exclusive inclusive rows x rows loops node
1. 7.579 30,682.808 ↓ 60.0 60 1

Merge Semi Join (cost=209,950.27..213,849.34 rows=1 width=32) (actual time=1,589.294..30,682.808 rows=60 loops=1)

  • Merge Cond: (device_data_preprocess_2.id = src.id)
2. 13.655 13.655 ↓ 28,469.0 28,469 1

Index Scan using ix_device_data_preprocess_2_id on device_data_preprocess_2 (cost=0.38..1,546.34 rows=1 width=36) (actual time=1.337..13.655 rows=28,469 loops=1)

3. 0.133 30,661.574 ↓ 106.0 106 1

Materialize (cost=209,949.90..212,302.98 rows=1 width=4) (actual time=1,587.307..30,661.574 rows=106 loops=1)

4. 12,979.816 30,661.441 ↓ 106.0 106 1

Nested Loop (cost=209,949.90..212,302.98 rows=1 width=4) (actual time=1,587.304..30,661.441 rows=106 loops=1)

  • Join Filter: ((src.device_id = d.id) AND (src.type_id = s.type_id) AND (src.nr = s.nr))
  • Rows Removed by Join Filter: 50,843,882
5. 38.961 38.961 ↓ 28,548.0 28,548 1

Index Scan using ix_device_data_preprocess_2_id on device_data_preprocess_2 src (cost=0.38..1,546.34 rows=1 width=16) (actual time=1.301..38.961 rows=28,548 loops=1)

  • Index Cond: (id <= '999999999999'::bigint)
6. 17,316.092 17,642.664 ↑ 12.1 1,781 28,548

HashAggregate (cost=209,949.52..210,164.75 rows=21,523 width=16) (actual time=0.012..0.618 rows=1,781 loops=28,548)

  • Group Key: d.id, co.rule_id, s.type_id, s.nr
7. 0.622 326.572 ↑ 12.1 1,781 1

Append (cost=156,816.30..209,734.29 rows=21,523 width=16) (actual time=320.026..326.572 rows=1,781 loops=1)

8. 1.211 323.834 ↑ 14.1 1,459 1

Nested Loop (cost=156,816.30..182,821.43 rows=20,508 width=16) (actual time=320.025..323.834 rows=1,459 loops=1)

9. 8.800 321.691 ↑ 14.1 932 1

Merge Join (cost=156,816.15..179,825.18 rows=13,099 width=16) (actual time=320.012..321.691 rows=932 loops=1)

  • Merge Cond: (co.company_id = d.company_id)
  • Join Filter: ((ca.category IS NULL) OR (ca.category = (d.category)::text))
  • Rows Removed by Join Filter: 1,874
10. 0.019 0.120 ↑ 249.6 23 1

Sort (cost=643.47..657.82 rows=5,740 width=48) (actual time=0.113..0.120 rows=23 loops=1)

  • Sort Key: co.company_id
  • Sort Method: quicksort Memory: 26kB
11. 0.022 0.101 ↑ 249.6 23 1

Merge Join (cost=194.47..285.09 rows=5,740 width=48) (actual time=0.075..0.101 rows=23 loops=1)

  • Merge Cond: (co.rule_id = ca.rule_id)
12. 0.013 0.060 ↑ 39.3 23 1

Sort (cost=106.30..108.56 rows=904 width=12) (actual time=0.058..0.060 rows=23 loops=1)

  • Sort Key: co.rule_id
  • Sort Method: quicksort Memory: 26kB
13. 0.024 0.047 ↑ 39.3 23 1

Hash Join (cost=11.80..61.92 rows=904 width=12) (actual time=0.030..0.047 rows=23 loops=1)

  • Hash Cond: (co.rule_id = drr.id)
14. 0.006 0.006 ↑ 98.3 23 1

Seq Scan on view_ranking_companies co (cost=0.00..32.60 rows=2,260 width=8) (actual time=0.002..0.006 rows=23 loops=1)

15. 0.008 0.017 ↑ 3.5 23 1

Hash (cost=10.80..10.80 rows=80 width=4) (actual time=0.017..0.017 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.009 0.009 ↑ 3.5 23 1

Seq Scan on device_ranking_rules drr (cost=0.00..10.80 rows=80 width=4) (actual time=0.003..0.009 rows=23 loops=1)

17. 0.012 0.019 ↑ 55.2 23 1

Sort (cost=88.17..91.35 rows=1,270 width=36) (actual time=0.015..0.019 rows=23 loops=1)

  • Sort Key: ca.rule_id
  • Sort Method: quicksort Memory: 26kB
18. 0.007 0.007 ↑ 55.2 23 1

Seq Scan on view_ranking_categories ca (cost=0.00..22.70 rows=1,270 width=36) (actual time=0.002..0.007 rows=23 loops=1)

19. 23.789 312.771 ↑ 1.1 42,062 1

Sort (cost=156,172.68..156,287.74 rows=46,024 width=14) (actual time=304.183..312.771 rows=42,062 loops=1)

  • Sort Key: d.company_id
  • Sort Method: quicksort Memory: 3,722kB
20. 288.982 288.982 ↑ 1.0 44,731 1

Seq Scan on devices d (cost=0.00..152,608.10 rows=46,024 width=14) (actual time=1.691..288.982 rows=44,731 loops=1)

  • Filter: (activation_status < 9999)
  • Rows Removed by Filter: 34,223
21. 0.932 0.932 ↑ 1.0 2 932

Index Only Scan using device_ranking_rules_series_rule_id_type_id_nr_key on device_ranking_rules_series s (cost=0.15..0.21 rows=2 width=12) (actual time=0.001..0.001 rows=2 loops=932)

  • Index Cond: (rule_id = co.rule_id)
  • Heap Fetches: 1,459
22. 0.522 2.116 ↑ 3.2 322 1

Merge Join (cost=240.04..26,697.63 rows=1,015 width=8) (actual time=0.148..2.116 rows=322 loops=1)

  • Merge Cond: (co_1.rule_id = ca_1.rule_id)
  • Join Filter: ((ca_1.category IS NULL) OR (ca_1.category = (d_1.category)::text))
  • Rows Removed by Join Filter: 654
23. 0.365 1.345 ↑ 16.4 976 1

Nested Loop (cost=151.87..24,787.93 rows=16,031 width=22) (actual time=0.108..1.345 rows=976 loops=1)

24. 0.008 0.108 ↑ 8.8 8 1

Merge Join (cost=151.33..153.30 rows=70 width=16) (actual time=0.091..0.108 rows=8 loops=1)

  • Merge Cond: (co_1.rule_id = drr_1.id)
25. 0.017 0.075 ↑ 21.9 8 1

Sort (cost=138.00..138.44 rows=175 width=12) (actual time=0.072..0.075 rows=8 loops=1)

  • Sort Key: co_1.rule_id
  • Sort Method: quicksort Memory: 25kB
26. 0.033 0.058 ↑ 21.9 8 1

Hash Join (cost=89.43..131.48 rows=175 width=12) (actual time=0.053..0.058 rows=8 loops=1)

  • Hash Cond: (s_1.rule_id = co_1.rule_id)
27. 0.003 0.007 ↑ 12.6 8 1

Bitmap Heap Scan on device_ranking_rules_series s_1 (cost=28.58..40.09 rows=101 width=4) (actual time=0.007..0.007 rows=8 loops=1)

  • Recheck Cond: ((type_id = 3) AND (nr = 2))
  • Heap Blocks: exact=1
28. 0.004 0.004 ↑ 12.6 8 1

Bitmap Index Scan on device_ranking_rules_series_rule_id_type_id_nr_key (cost=0.00..28.55 rows=101 width=0) (actual time=0.004..0.004 rows=8 loops=1)

  • Index Cond: ((type_id = 3) AND (nr = 2))
29. 0.011 0.018 ↑ 98.3 23 1

Hash (cost=32.60..32.60 rows=2,260 width=8) (actual time=0.018..0.018 rows=23 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 33kB
30. 0.007 0.007 ↑ 98.3 23 1

Seq Scan on view_ranking_companies co_1 (cost=0.00..32.60 rows=2,260 width=8) (actual time=0.003..0.007 rows=23 loops=1)

31. 0.016 0.025 ↑ 4.2 19 1

Sort (cost=13.33..13.53 rows=80 width=4) (actual time=0.018..0.025 rows=19 loops=1)

  • Sort Key: drr_1.id
  • Sort Method: quicksort Memory: 26kB
32. 0.009 0.009 ↑ 3.5 23 1

Seq Scan on device_ranking_rules drr_1 (cost=0.00..10.80 rows=80 width=4) (actual time=0.003..0.009 rows=23 loops=1)

33. 0.872 0.872 ↑ 6.5 122 8

Index Scan using idx_devices_company_wh_status on devices d_1 (cost=0.54..344.03 rows=789 width=14) (actual time=0.006..0.109 rows=122 loops=8)

  • Index Cond: (company_id = co_1.company_id)
34. 0.243 0.249 ↑ 1.3 987 1

Sort (cost=88.17..91.35 rows=1,270 width=36) (actual time=0.016..0.249 rows=987 loops=1)

  • Sort Key: ca_1.rule_id
  • Sort Method: quicksort Memory: 26kB
35. 0.006 0.006 ↑ 55.2 23 1

Seq Scan on view_ranking_categories ca_1 (cost=0.00..22.70 rows=1,270 width=36) (actual time=0.002..0.006 rows=23 loops=1)

Planning time : 5.027 ms
Execution time : 30,683.533 ms