explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oF59

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 1,742.833 ↑ 1.0 1 1

Aggregate (cost=5,810,812.72..5,810,812.73 rows=1 width=8) (actual time=1,742.832..1,742.833 rows=1 loops=1)

2.          

CTE pending_requests_t

3. 0.006 0.625 ↑ 333.3 3 1

Limit (cost=0.30..111.40 rows=1,000 width=36) (actual time=0.206..0.625 rows=3 loops=1)

4. 0.619 0.619 ↑ 119,958.7 3 1

Index Scan Backward using requests_pkey on requests rcsr (cost=0.30..39,981.82 rows=359,876 width=36) (actual time=0.205..0.619 rows=3 loops=1)

5.          

CTE items_t

6. 6.400 6.400 ↑ 20,000.0 5 1

CTE Scan on pending_requests_t (cost=0.00..567.50 rows=100,000 width=456) (actual time=0.842..6.400 rows=5 loops=1)

7.          

CTE items_expanded_t

8. 58.695 1,742.783 ↑ 20,000.0 5 1

Hash Left Join (cost=109,042.82..5,807,883.82 rows=100,000 width=1,449) (actual time=1,702.649..1,742.783 rows=5 loops=1)

  • Hash Cond: (COALESCE((it.item ->> 'fingerprint'::text), (it.item ->> 'md5'::text)) = (vth.md5)::text)
9. 6.446 6.446 ↑ 20,000.0 5 1

CTE Scan on items_t it (cost=0.00..2,000.00 rows=100,000 width=136) (actual time=0.852..6.446 rows=5 loops=1)

10. 885.674 1,674.242 ↓ 1.0 1,408,479 1

Hash (cost=48,912.70..48,912.70 rows=1,405,770 width=224) (actual time=1,674.242..1,674.242 rows=1,408,479 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 5259kB
11. 788.568 788.568 ↓ 1.0 1,408,479 1

Seq Scan on hash vth (cost=0.00..48,912.70 rows=1,405,770 width=224) (actual time=0.016..788.568 rows=1,408,479 loops=1)

12.          

SubPlan (forHash Left Join)

13. 0.085 0.085 ↑ 1.0 1 5

Index Scan using device_device_uuid_idx on device (cost=0.28..8.29 rows=1 width=26) (actual time=0.016..0.017 rows=1 loops=5)

  • Index Cond: ((device_uuid)::text = it.device_uuid)
14. 0.065 0.065 ↑ 1.0 1 5

Index Only Scan using user_device_uuid_username_idx on "user" rcsu (cost=0.28..8.29 rows=1 width=13) (actual time=0.013..0.013 rows=1 loops=5)

  • Index Cond: (device_uuid = it.device_uuid)
  • Heap Fetches: 5
15. 0.170 0.170 ↑ 1.0 1 5

Result (cost=0.00..0.02 rows=1 width=32) (actual time=0.033..0.034 rows=1 loops=5)

16. 0.280 0.280 ↑ 1.0 1 5

Seq Scan on event_types (cost=0.00..4.34 rows=1 width=59) (actual time=0.032..0.056 rows=1 loops=5)

  • Filter: (event_id = ((it.item ->> 'eventReasonEnum'::text))::integer)
  • Rows Removed by Filter: 116
17. 0.035 0.035 ↑ 4.0 1 5

Seq Scan on event_type (cost=0.00..26.60 rows=4 width=32) (actual time=0.004..0.007 rows=1 loops=5)

  • Filter: (value = ((it.item ->> 'eventTypeEnum'::text))::integer)
  • Rows Removed by Filter: 8
18. 1.650 1.650 ↑ 1.0 1 5

Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.330..0.330 rows=1 loops=5)

19. 1.040 1.040 ↑ 1.0 1 5

Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.207..0.208 rows=1 loops=5)

20. 0.040 0.040 ↑ 1.0 1 5

Index Scan using device_device_uuid_idx on device device_1 (cost=0.28..8.30 rows=1 width=32) (actual time=0.007..0.008 rows=1 loops=5)

  • Index Cond: ((device_uuid)::text = it.device_uuid)
21. 0.035 0.035 ↑ 1.0 1 5

Result (cost=0.00..0.02 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=5)

22. 1,742.823 1,742.823 ↑ 20,000.0 5 1

CTE Scan on items_expanded_t (cost=0.00..2,000.00 rows=100,000 width=0) (actual time=1,702.656..1,742.823 rows=5 loops=1)