explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SlbE

Settings
# exclusive inclusive rows x rows loops node
1. 854.177 11,162.445 ↓ 4.5 890 1

HashAggregate (cost=8,207.75..8,210.25 rows=200 width=184) (actual time=11,009.792..11,162.445 rows=890 loops=1)

  • Group Key: 'bloop'::text, list_rows.company_id, list_rows.consumed_resource_uri, list_rows.consumption_type, list_rows.id_type, list_rows.sampled_rate
2.          

CTE lists

3. 0.020 0.718 ↑ 1.0 1 1

Hash Join (cost=1.04..2.67 rows=1 width=26) (actual time=0.711..0.718 rows=1 loops=1)

  • Hash Cond: (user_id_lists.list_id = r.user_id_list)
4. 0.380 0.380 ↓ 1.4 70 1

Seq Scan on user_id_lists (cost=0.00..1.49 rows=49 width=34) (actual time=0.371..0.380 rows=70 loops=1)

5. 0.003 0.318 ↑ 1.0 1 1

Hash (cost=1.02..1.02 rows=1 width=8) (actual time=0.318..0.318 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
6. 0.315 0.315 ↑ 1.0 1 1

Seq Scan on report_info r (cost=0.00..1.02 rows=1 width=8) (actual time=0.315..0.315 rows=1 loops=1)

  • Filter: (report_id = 'bloop'::text)
  • Rows Removed by Filter: 5
7.          

CTE list_rows

8. 2,688.447 5,941.731 ↓ 1.6 889 1

Nested Loop (cost=4,032.61..4,075.79 rows=570 width=86) (actual time=156.311..5,941.731 rows=889 loops=1)

9. 0.719 0.719 ↑ 1.0 1 1

CTE Scan on lists (cost=0.00..0.02 rows=1 width=40) (actual time=0.712..0.719 rows=1 loops=1)

10. 793.046 3,252.565 ↓ 1.6 889 1

Finalize GroupAggregate (cost=4,032.61..4,062.94 rows=570 width=78) (actual time=143.487..3,252.565 rows=889 loops=1)

  • Group Key: discover_raw.company_id, discover_raw.consumed_resource_uri, discover_raw.consumption_type, discover_raw.id_type
11. 0.000 2,459.519 ↓ 1.6 889 1

Gather Merge (cost=4,032.61..4,050.14 rows=568 width=78) (actual time=133.158..2,459.519 rows=889 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
12. 4,840.030 5,385.765 ↓ 1.3 178 5

Partial GroupAggregate (cost=4,032.55..4,036.10 rows=142 width=78) (actual time=118.025..1,077.153 rows=178 loops=5)

  • Group Key: discover_raw.company_id, discover_raw.consumed_resource_uri, discover_raw.consumption_type, discover_raw.id_type
13. 2.380 545.735 ↓ 1.3 178 5

Sort (cost=4,032.55..4,032.91 rows=142 width=64) (actual time=108.978..109.147 rows=178 loops=5)

  • Sort Key: discover_raw.company_id, discover_raw.consumed_resource_uri, discover_raw.consumption_type, discover_raw.id_type
  • Sort Method: quicksort Memory: 63kB
14. 0.495 543.355 ↓ 1.3 178 5

Hash Anti Join (cost=40.60..4,027.48 rows=142 width=64) (actual time=11.395..108.671 rows=178 loops=5)

  • Hash Cond: (discover_raw.consumed_resource_uri = b.consumed_resource_uri)
15. 542.665 542.665 ↑ 1.6 178 5

Parallel Seq Scan on discover_raw (cost=0.00..3,960.58 rows=285 width=64) (actual time=11.301..108.533 rows=178 loops=5)

  • Filter: (raw_count > 2000)
  • Rows Removed by Filter: 15717
16. 0.035 0.195 ↑ 61.8 22 5

Hash (cost=23.60..23.60 rows=1,360 width=32) (actual time=0.039..0.039 rows=22 loops=5)

  • Buckets: 2048 Batches: 1 Memory Usage: 18kB
17. 0.160 0.160 ↑ 61.8 22 5

Seq Scan on blacklist b (cost=0.00..23.60 rows=1,360 width=32) (actual time=0.030..0.032 rows=22 loops=5)

18.          

CTE uris

19. 2.084 2,618.569 ↑ 56.0 1 1

GroupAggregate (cost=4,032.61..4,099.57 rows=56 width=86) (actual time=2,618.569..2,618.569 rows=1 loops=1)

  • Group Key: discover_raw_1.company_id, discover_raw_1.consumed_resource_uri, discover_raw_1.consumption_type, discover_raw_1.id_type, '1'::bigint
20. 0.911 2,616.485 ↑ 56.0 1 1

Nested Loop (cost=4,032.61..4,098.17 rows=56 width=86) (actual time=1,223.735..2,616.485 rows=1 loops=1)

  • Join Filter: (discover_raw_1.consumed_resource_uri = ANY (report_info.app_ids))
  • Rows Removed by Join Filter: 5333
21. 593.738 2,614.685 ↓ 1.6 889 1

Finalize GroupAggregate (cost=4,032.61..4,062.94 rows=570 width=78) (actual time=33.039..2,614.685 rows=889 loops=1)

  • Group Key: discover_raw_1.company_id, discover_raw_1.consumed_resource_uri, discover_raw_1.consumption_type, discover_raw_1.id_type
22. 0.000 2,020.947 ↓ 1.6 889 1

Gather Merge (cost=4,032.61..4,050.14 rows=568 width=78) (actual time=26.953..2,020.947 rows=889 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
23. 2,286.175 2,313.555 ↓ 1.3 178 5

Partial GroupAggregate (cost=4,032.55..4,036.10 rows=142 width=78) (actual time=9.766..462.711 rows=178 loops=5)

  • Group Key: discover_raw_1.company_id, discover_raw_1.consumed_resource_uri, discover_raw_1.consumption_type, discover_raw_1.id_type
24. 2.105 27.380 ↓ 1.3 178 5

Sort (cost=4,032.55..4,032.91 rows=142 width=64) (actual time=5.373..5.476 rows=178 loops=5)

  • Sort Key: discover_raw_1.company_id, discover_raw_1.consumed_resource_uri, discover_raw_1.consumption_type, discover_raw_1.id_type
  • Sort Method: quicksort Memory: 124kB
25. 0.385 25.275 ↓ 1.3 178 5

Hash Anti Join (cost=40.60..4,027.48 rows=142 width=64) (actual time=0.826..5.055 rows=178 loops=5)

  • Hash Cond: (discover_raw_1.consumed_resource_uri = b_1.consumed_resource_uri)
26. 24.735 24.735 ↑ 1.6 178 5

Parallel Seq Scan on discover_raw discover_raw_1 (cost=0.00..3,960.58 rows=285 width=64) (actual time=0.751..4.947 rows=178 loops=5)

  • Filter: (raw_count > 2000)
  • Rows Removed by Filter: 15717
27. 0.030 0.155 ↑ 61.8 22 5

Hash (cost=23.60..23.60 rows=1,360 width=32) (actual time=0.031..0.031 rows=22 loops=5)

  • Buckets: 2048 Batches: 1 Memory Usage: 18kB
28. 0.125 0.125 ↑ 61.8 22 5

Seq Scan on blacklist b_1 (cost=0.00..23.60 rows=1,360 width=32) (actual time=0.023..0.025 rows=22 loops=5)

29. 0.878 0.889 ↓ 3.0 6 889

Materialize (cost=0.00..1.03 rows=2 width=32) (actual time=0.000..0.001 rows=6 loops=889)

30. 0.011 0.011 ↓ 3.0 6 1

Seq Scan on report_info (cost=0.00..1.02 rows=2 width=32) (actual time=0.011..0.011 rows=6 loops=1)

31. 1.064 10,308.268 ↓ 1.4 890 1

Result (cost=0.00..18.78 rows=626 width=176) (actual time=158.034..10,308.268 rows=890 loops=1)

32. 0.470 10,307.204 ↓ 1.4 890 1

Append (cost=0.00..12.52 rows=626 width=144) (actual time=158.032..10,307.204 rows=890 loops=1)

33. 7,687.647 7,687.647 ↓ 1.6 889 1

CTE Scan on list_rows (cost=0.00..11.40 rows=570 width=144) (actual time=158.032..7,687.647 rows=889 loops=1)

34. 2,619.087 2,619.087 ↑ 56.0 1 1

CTE Scan on uris (cost=0.00..1.12 rows=56 width=144) (actual time=2,619.086..2,619.087 rows=1 loops=1)