explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PvBP

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 39,913.315 ↓ 35.6 459,564 1

Hash Join (cost=106,163.32..279,983.72 rows=12,896 width=1,072) (actual time=2,017.821..39,913.315 rows=459,564 loops=1)

  • Hash Cond: (events.event_type_ref = event_type.id)
2.          

Initplan (forHash Join)

3. 122.434 250.372 ↑ 1.0 1 1

Aggregate (cost=11,845.04..11,845.05 rows=1 width=8) (actual time=250.371..250.372 rows=1 loops=1)

4. 127.938 127.938 ↑ 1.0 458,483 1

Seq Scan on entity_pred_temp entity_pred_temp_1 (cost=0.00..10,698.83 rows=458,483 width=8) (actual time=0.012..127.938 rows=458,483 loops=1)

5. 838.146 39,662.419 ↓ 35.6 459,564 1

Hash Join (cost=94,155.69..267,942.20 rows=12,896 width=862) (actual time=2,015.434..39,662.419 rows=459,564 loops=1)

  • Hash Cond: (events.command_ref = command.id)
6. 312.735 38,521.227 ↓ 35.6 459,564 1

Hash Join (cost=78,197.54..246,021.20 rows=12,896 width=833) (actual time=1,692.604..38,521.227 rows=459,564 loops=1)

  • Hash Cond: (entity_pred_temp.partition_ref = partition.id)
7. 1,230.851 38,205.773 ↓ 35.6 459,564 1

Merge Join (cost=77,967.47..245,757.25 rows=12,896 width=837) (actual time=1,689.822..38,205.773 rows=459,564 loops=1)

  • Merge Cond: (events.entity_ref = entity_pred_temp.entity_id)
  • Join Filter: ((events.effective_at >= entity_pred_temp.from_effective_at) AND ((events.as_at > entity_pred_temp.from_as_at) OR (events.effective_at <> entity_pred_temp.from_effective_at)))
8. 36,073.359 36,073.359 ↓ 79.6 3,321,674 1

Index Scan using iscorrectionindex on events (cost=0.43..406,827.45 rows=41,755 width=791) (actual time=250.402..36,073.359 rows=3,321,674 loops=1)

  • Index Cond: ((effective_at >= $0) AND (effective_at <= '2019-04-09 00:00:00+01'::timestamp with time zone))
  • Filter: (as_at <= '2019-04-09 00:00:00+01'::timestamp with time zone)
  • Rows Removed by Filter: 756
9. 231.267 901.563 ↓ 1.0 459,564 1

Materialize (cost=72,616.65..74,909.07 rows=458,483 width=70) (actual time=442.019..901.563 rows=459,564 loops=1)

10. 508.017 670.296 ↑ 1.0 458,483 1

Sort (cost=72,616.65..73,762.86 rows=458,483 width=70) (actual time=442.015..670.296 rows=458,483 loops=1)

  • Sort Key: entity_pred_temp.entity_id
  • Sort Method: external merge Disk: 36864kB
11. 162.279 162.279 ↑ 1.0 458,483 1

Seq Scan on entity_pred_temp (cost=0.00..10,698.83 rows=458,483 width=70) (actual time=0.009..162.279 rows=458,483 loops=1)

12. 1.290 2.719 ↓ 1.0 4,760 1

Hash (cost=171.14..171.14 rows=4,714 width=4) (actual time=2.719..2.719 rows=4,760 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 232kB
13. 1.429 1.429 ↓ 1.0 4,760 1

Seq Scan on partition (cost=0.00..171.14 rows=4,714 width=4) (actual time=0.006..1.429 rows=4,760 loops=1)

14. 161.506 303.046 ↑ 1.0 410,066 1

Hash (cost=7,620.51..7,620.51 rows=410,451 width=37) (actual time=303.045..303.046 rows=410,066 loops=1)

  • Buckets: 65536 Batches: 16 Memory Usage: 2332kB
15. 141.540 141.540 ↑ 1.0 410,066 1

Seq Scan on command (cost=0.00..7,620.51 rows=410,451 width=37) (actual time=0.005..141.540 rows=410,066 loops=1)

16. 7.601 11.030 ↓ 1.0 3,569 1

Hash (cost=118.37..118.37 rows=3,537 width=154) (actual time=11.030..11.030 rows=3,569 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 688kB
17. 3.429 3.429 ↓ 1.0 3,569 1

Seq Scan on event_type (cost=0.00..118.37 rows=3,537 width=154) (actual time=0.008..3.429 rows=3,569 loops=1)