explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jE1S

Settings
# exclusive inclusive rows x rows loops node
1. 53.785 1,445.641 ↑ 33,333.3 3 1

Hash Left Join (cost=5,137,511.73..5,227,461.73 rows=100,000 width=1,690) (actual time=1,411.649..1,445.641 rows=3 loops=1)

  • Hash Cond: (iet.fingerprint = (vth.md5)::text)
2.          

CTE pending_requests_t

3. 0.002 0.337 ↑ 333.3 3 1

Limit (cost=0.17..111.14 rows=1,000 width=36) (actual time=0.174..0.337 rows=3 loops=1)

4. 0.335 0.335 ↑ 39,453.3 3 1

Index Scan Backward using requests_pkey on requests rcsr (cost=0.17..13,135.37 rows=118,360 width=36) (actual time=0.174..0.335 rows=3 loops=1)

5.          

CTE items_t

6. 2.872 2.872 ↑ 33,333.3 3 1

CTE Scan on pending_requests_t (cost=0.00..567.50 rows=100,000 width=456) (actual time=0.673..2.872 rows=3 loops=1)

7.          

CTE items_expanded_t

8. 2.998 4.990 ↑ 33,333.3 3 1

CTE Scan on items_t it (cost=0.00..4,997,000.00 rows=100,000 width=1,226) (actual time=1.904..4.990 rows=3 loops=1)

9.          

SubPlan (forCTE Scan)

10. 0.069 0.069 ↑ 1.0 1 3

Seq Scan on device (cost=0.00..7.09 rows=1 width=75) (actual time=0.008..0.023 rows=1 loops=3)

  • Filter: ((device_uuid)::text = it.device_uuid)
  • Rows Removed by Filter: 86
11. 0.054 0.054 ↑ 1.0 1 3

Index Only Scan using user_device_uuid_username_idx on "user" rcsu (cost=0.27..4.29 rows=1 width=9) (actual time=0.018..0.018 rows=1 loops=3)

  • Index Cond: (device_uuid = it.device_uuid)
  • Heap Fetches: 3
12. 0.042 0.042 ↑ 1.0 1 3

Result (cost=0.00..0.02 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=3)

13. 0.165 0.165 ↑ 1.0 1 3

Seq Scan on event_types (cost=0.00..4.24 rows=1 width=60) (actual time=0.030..0.055 rows=1 loops=3)

  • Filter: (event_id = ((it.item ->> 'eventReasonEnum'::text))::integer)
  • Rows Removed by Filter: 116
14. 0.015 0.015 ↑ 4.0 1 3

Seq Scan on event_type (cost=0.00..26.60 rows=4 width=32) (actual time=0.003..0.005 rows=1 loops=3)

  • Filter: (value = ((it.item ->> 'eventTypeEnum'::text))::integer)
  • Rows Removed by Filter: 8
15. 0.900 0.900 ↑ 1.0 1 3

Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.299..0.300 rows=1 loops=3)

16. 0.672 0.672 ↑ 1.0 1 3

Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.224..0.224 rows=1 loops=3)

17. 0.057 0.057 ↑ 1.0 1 3

Seq Scan on device device_1 (cost=0.00..7.09 rows=1 width=32) (actual time=0.007..0.019 rows=1 loops=3)

  • Filter: ((device_uuid)::text = it.device_uuid)
  • Rows Removed by Filter: 86
18. 0.018 0.018 ↑ 1.0 1 3

Result (cost=0.00..0.02 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=3)

19. 5.009 5.009 ↑ 33,333.3 3 1

CTE Scan on items_expanded_t iet (cost=0.00..2,000.00 rows=100,000 width=1,194) (actual time=1.910..5.009 rows=3 loops=1)

20. 753.984 1,386.847 ↓ 1.1 1,912,059 1

Hash (cost=65,209.26..65,209.26 rows=1,744,626 width=224) (actual time=1,386.847..1,386.847 rows=1,912,059 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 6602kB
21. 632.863 632.863 ↓ 1.1 1,912,059 1

Seq Scan on hash vth (cost=0.00..65,209.26 rows=1,744,626 width=224) (actual time=0.030..632.863 rows=1,912,059 loops=1)