explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oIJj

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 373.218 ↓ 1.5 109,907 1

Unique (cost=39,314.81..41,064.63 rows=73,545 width=8) (actual time=192.653..373.218 rows=109,907 loops=1)

2. 182.276 274.199 ↑ 1.0 349,964 1

Sort (cost=39,314.81..40,189.72 rows=349,964 width=8) (actual time=192.651..274.199 rows=349,964 loops=1)

  • Sort Key: fact_asset_policy.asset_id
  • Sort Method: quicksort Memory: 28693kB
3. 91.923 91.923 ↑ 1.0 349,964 1

Seq Scan on fact_asset_policy (cost=0.00..7,088.64 rows=349,964 width=8) (actual time=0.013..91.923 rows=349,964 loops=1)

4. 34.154 501.612 ↓ 1.5 109,907 1

Hash (cost=41,800.08..41,800.08 rows=73,545 width=16) (actual time=501.612..501.612 rows=109,907 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 6176kB
5. 45.893 467.458 ↓ 1.5 109,907 1

Subquery Scan on pc_data (cost=39,314.81..41,800.08 rows=73,545 width=16) (actual time=244.515..467.458 rows=109,907 loops=1)

6. 99.171 421.565 ↓ 1.5 109,907 1

Unique (cost=39,314.81..41,064.63 rows=73,545 width=16) (actual time=244.513..421.565 rows=109,907 loops=1)

7. 216.761 322.394 ↑ 1.0 349,964 1

Sort (cost=39,314.81..40,189.72 rows=349,964 width=16) (actual time=244.511..322.394 rows=349,964 loops=1)

  • Sort Key: fact_asset_policy_1.asset_id, fact_asset_policy_1.date_tested DESC
  • Sort Method: quicksort Memory: 28693kB
8. 105.633 105.633 ↑ 1.0 349,964 1

Seq Scan on fact_asset_policy fact_asset_policy_1 (cost=0.00..7,088.64 rows=349,964 width=16) (actual time=0.018..105.633 rows=349,964 loops=1)

  • Filter: (asset_id IS NOT NULL)
9. 89.303 44,026.545 ↓ 3.0 231,350 1

Hash (cost=4,256,667.23..4,256,667.23 rows=78,325 width=16) (actual time=44,026.545..44,026.545 rows=231,350 loops=1)

  • Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 12893kB
10. 97.801 43,937.242 ↓ 3.0 231,350 1

Subquery Scan on scan_data (cost=4,125,481.74..4,256,667.23 rows=78,325 width=16) (actual time=28,925.864..43,937.242 rows=231,350 loops=1)

11. 5,660.014 43,839.441 ↓ 3.0 231,350 1

Unique (cost=4,125,481.74..4,255,883.98 rows=78,325 width=16) (actual time=28,925.861..43,839.441 rows=231,350 loops=1)

12. 31,226.250 38,179.427 ↓ 1.0 26,090,926 1

Sort (cost=4,125,481.74..4,190,682.86 rows=26,080,448 width=16) (actual time=28,925.860..38,179.427 rows=26,090,926 loops=1)

  • Sort Key: fact_asset_event.asset_id, fact_asset_event.date DESC
  • Sort Method: external merge Disk: 663088kB
13. 6,953.177 6,953.177 ↓ 1.0 26,090,926 1

Seq Scan on fact_asset_event (cost=0.00..467,120.48 rows=26,080,448 width=16) (actual time=0.009..6,953.177 rows=26,090,926 loops=1)

14. 15.205 9,205.757 ↑ 2.2 34,940 1

Hash (cost=1,019,206.12..1,019,206.12 rows=78,325 width=16) (actual time=9,205.756..9,205.757 rows=34,940 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 2662kB
15. 15.000 9,190.552 ↑ 2.2 34,940 1

Subquery Scan on agent_data (cost=997,297.70..1,019,206.12 rows=78,325 width=16) (actual time=6,772.532..9,190.552 rows=34,940 loops=1)

16. 908.892 9,175.552 ↑ 2.2 34,940 1

Unique (cost=997,297.70..1,018,422.87 rows=78,325 width=16) (actual time=6,772.530..9,175.552 rows=34,940 loops=1)

17. 4,605.151 8,266.660 ↓ 1.0 4,241,572 1

Sort (cost=997,297.70..1,007,860.29 rows=4,225,033 width=16) (actual time=6,772.528..8,266.660 rows=4,241,572 loops=1)

  • Sort Key: fact_asset_event_1.asset_id, fact_asset_event_1.date DESC
  • Sort Method: quicksort Memory: 352424kB
18. 3,661.509 3,661.509 ↓ 1.0 4,241,572 1

Seq Scan on fact_asset_event fact_asset_event_1 (cost=0.00..532,321.60 rows=4,225,033 width=16) (actual time=109.853..3,661.509 rows=4,241,572 loops=1)

  • Filter: (type = 'AGENT-IMPORT'::text)
  • Rows Removed by Filter: 21849354
Planning time : 1.545 ms
Execution time : 57,852.600 ms