explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LGIa

Settings
# exclusive inclusive rows x rows loops node
1. 15.502 20,601.459 ↓ 5.5 22 1

GroupAggregate (cost=11,770.00..11,770.09 rows=4 width=24) (actual time=20,584.381..20,601.459 rows=22 loops=1)

  • Group Key: dp_1.dp_id, dd.cc_id
2. 18.654 20,585.957 ↓ 7,518.5 30,074 1

Sort (cost=11,770.00..11,770.01 rows=4 width=16) (actual time=20,584.354..20,585.957 rows=30,074 loops=1)

  • Sort Key: dp_1.dp_id, dd.cc_id
  • Sort Method: quicksort Memory: 2,178kB
3. 12,540.523 20,567.303 ↓ 7,518.5 30,074 1

Nested Loop Left Join (cost=4,830.60..11,769.96 rows=4 width=16) (actual time=278.844..20,567.303 rows=30,074 loops=1)

  • Join Filter: (dd.dd_id = x_1.dd_id)
  • Rows Removed by Join Filter: 150,739,562
4. 0.000 27.096 ↓ 7,518.5 30,074 1

Gather (cost=1,037.54..6,694.59 rows=4 width=12) (actual time=0.949..27.096 rows=30,074 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 9.344 230.150 ↓ 5,012.5 10,025 3 / 3

Hash Join (cost=37.53..5,694.19 rows=2 width=12) (actual time=76.656..230.150 rows=10,025 loops=3)

  • Hash Cond: ((dd.dtdt_id = dp_1.dtdt_id) AND ((ddip.ddip_value)::text = ((dp_1.dp_id)::character varying)::text))
6. 16.884 220.724 ↓ 294.5 20,027 3 / 3

Nested Loop (cost=4.28..5,628.96 rows=68 width=44) (actual time=0.267..220.724 rows=20,027 loops=3)

7. 75.258 123.731 ↓ 294.5 20,027 3 / 3

Hash Join (cost=4.00..5,606.95 rows=68 width=36) (actual time=0.227..123.731 rows=20,027 loops=3)

  • Hash Cond: ((ddip.di_id = dip.di_id) AND (ddip.dip_id = dip.dip_id))
8. 48.411 48.411 ↑ 1.2 83,458 3 / 3

Parallel Seq Scan on device_interface_params ddip (cost=0.00..5,055.22 rows=104,322 width=44) (actual time=0.015..48.411 rows=83,458 loops=3)

9. 0.009 0.062 ↑ 1.0 9 3 / 3

Hash (cost=3.86..3.86 rows=9 width=8) (actual time=0.062..0.062 rows=9 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
10. 0.053 0.053 ↑ 1.0 9 3 / 3

Seq Scan on interface_param dip (cost=0.00..3.86 rows=9 width=8) (actual time=0.033..0.053 rows=9 loops=3)

  • Filter: ((dip_name)::text = 'profile'::text)
  • Rows Removed by Filter: 60
11. 80.109 80.109 ↑ 1.0 1 60,082 / 3

Index Scan using device_device_dsid on device dd (cost=0.29..0.32 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=60,082)

  • Index Cond: (dd_id = ddip.dd_id)
12. 0.043 0.082 ↑ 14.5 64 3 / 3

Hash (cost=19.30..19.30 rows=930 width=8) (actual time=0.082..0.082 rows=64 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
13. 0.039 0.039 ↑ 14.5 64 3 / 3

Seq Scan on profile dp_1 (cost=0.00..19.30 rows=930 width=8) (actual time=0.023..0.039 rows=64 loops=3)

14. 7,722.812 7,999.684 ↓ 2,506.5 5,013 30,074

Materialize (cost=3,793.07..5,075.26 rows=2 width=4) (actual time=0.009..0.266 rows=5,013 loops=30,074)

15. 20.857 276.872 ↓ 5,011.5 10,023 1

Subquery Scan on x_1 (cost=3,793.07..5,075.25 rows=2 width=4) (actual time=254.594..276.872 rows=10,023 loops=1)

16. 6.523 256.015 ↓ 5,011.5 10,023 1

HashSetOp Except (cost=3,793.07..5,075.23 rows=2 width=8) (actual time=254.593..256.015 rows=10,023 loops=1)

17. 2.162 249.492 ↓ 2.0 20,312 1

Append (cost=3,793.07..5,049.50 rows=10,289 width=8) (actual time=160.081..249.492 rows=20,312 loops=1)

18. 2.088 241.511 ↓ 5,012.5 10,025 1

Subquery Scan on *SELECT* 1 (cost=3,793.07..3,793.10 rows=2 width=8) (actual time=160.081..241.511 rows=10,025 loops=1)

19. 6.664 239.423 ↓ 5,012.5 10,025 1

Unique (cost=3,793.07..3,793.08 rows=2 width=4) (actual time=160.079..239.423 rows=10,025 loops=1)

20. 125.473 232.759 ↓ 20,041.5 40,083 1

Sort (cost=3,793.07..3,793.07 rows=2 width=4) (actual time=160.076..232.759 rows=40,083 loops=1)

  • Sort Key: device_interface_params.dd_id
  • Sort Method: quicksort Memory: 3,245kB
21. 45.930 107.286 ↓ 20,041.5 40,083 1

Hash Join (cost=56.87..3,793.06 rows=2 width=4) (actual time=4.409..107.286 rows=40,083 loops=1)

  • Hash Cond: ((device_interface_params.di_id = interface_param.di_id) AND (device_interface_params.dip_id = interface_param.dip_id))
22. 57.014 61.328 ↓ 16.1 40,083 1

Bitmap Heap Scan on device_interface_params (cost=52.87..3,775.95 rows=2,497 width=12) (actual time=4.374..61.328 rows=40,083 loops=1)

  • Recheck Cond: (((ddip_value)::text = '1'::text) OR ((ddip_value)::text = 'true'::text))
  • Heap Blocks: exact=290
23. 0.002 4.314 ↓ 0.0 0 1

BitmapOr (cost=52.87..52.87 rows=2,504 width=0) (actual time=4.314..4.314 rows=0 loops=1)

24. 4.305 4.305 ↓ 32.0 40,083 1

Bitmap Index Scan on device_interface_params_ddip_value_idx (cost=0.00..25.81 rows=1,252 width=0) (actual time=4.305..4.305 rows=40,083 loops=1)

  • Index Cond: ((ddip_value)::text = '1'::text)
25. 0.007 0.007 ↓ 0.0 0 1

Bitmap Index Scan on device_interface_params_ddip_value_idx (cost=0.00..25.81 rows=1,252 width=0) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: ((ddip_value)::text = 'true'::text)
26. 0.005 0.028 ↑ 1.0 9 1

Hash (cost=3.86..3.86 rows=9 width=8) (actual time=0.028..0.028 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
27. 0.023 0.023 ↑ 1.0 9 1

Seq Scan on interface_param (cost=0.00..3.86 rows=9 width=8) (actual time=0.016..0.023 rows=9 loops=1)

  • Filter: ((dip_name)::text = 'active'::text)
  • Rows Removed by Filter: 60
28. 1.246 5.819 ↑ 1.0 10,287 1

Subquery Scan on *SELECT* 2 (cost=0.00..1,256.41 rows=10,287 width=8) (actual time=0.017..5.819 rows=10,287 loops=1)

29. 4.573 4.573 ↑ 1.0 10,287 1

Seq Scan on device (cost=0.00..1,153.54 rows=10,287 width=4) (actual time=0.016..4.573 rows=10,287 loops=1)

  • Filter: (ds_id <> 1)
  • Rows Removed by Filter: 15,276
Planning time : 1.936 ms
Execution time : 20,601.911 ms