explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZngZ

Settings
# exclusive inclusive rows x rows loops node
1. 2.538 4,366.519 ↓ 11.8 2,355 1

Sort (cost=205,284.00..205,284.50 rows=200 width=12) (actual time=4,365.599..4,366.519 rows=2,355 loops=1)

  • Sort Key: pco.pco_day
  • Sort Method: quicksort Memory: 207kB
2.          

CTE my_zone

3. 15.634 45.700 ↓ 2.1 23,375 1

Hash Left Join (cost=386.61..1,310.85 rows=11,280 width=446) (actual time=5.621..45.700 rows=23,375 loops=1)

  • Hash Cond: (zg.descendant_id = zd.id)
4. 17.083 26.933 ↓ 2.1 23,375 1

Hash Left Join (cost=193.30..1,087.82 rows=11,280 width=227) (actual time=2.480..26.933 rows=23,375 loops=1)

  • Hash Cond: (zg.parent_id = zp.id)
5. 7.383 7.383 ↓ 2.1 23,375 1

Seq Scan on zone_graph_flatten zg (cost=0.00..864.80 rows=11,280 width=8) (actual time=0.005..7.383 rows=23,375 loops=1)

6. 1.206 2.467 ↓ 1.8 2,118 1

Hash (cost=178.69..178.69 rows=1,169 width=223) (actual time=2.467..2.467 rows=2,118 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 133kB
7. 1.261 1.261 ↓ 1.8 2,118 1

Seq Scan on zone zp (cost=0.00..178.69 rows=1,169 width=223) (actual time=0.005..1.261 rows=2,118 loops=1)

8. 2.009 3.133 ↓ 1.8 2,118 1

Hash (cost=178.69..178.69 rows=1,169 width=223) (actual time=3.132..3.133 rows=2,118 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 133kB
9. 1.124 1.124 ↓ 1.8 2,118 1

Seq Scan on zone zd (cost=0.00..178.69 rows=1,169 width=223) (actual time=0.004..1.124 rows=2,118 loops=1)

10.          

CTE origin_zone

11. 3.203 3.203 ↓ 19.4 544 1

CTE Scan on my_zone (cost=0.00..253.80 rows=28 width=4) (actual time=0.009..3.203 rows=544 loops=1)

  • Filter: ((zd_is_port IS TRUE) AND ((zp_name)::text = 'Asia'::text))
  • Rows Removed by Filter: 22831
12.          

CTE destination_zone

13. 62.418 62.418 ↓ 19.4 544 1

CTE Scan on my_zone my_zone_1 (cost=0.00..253.80 rows=28 width=4) (actual time=5.723..62.418 rows=544 loops=1)

  • Filter: ((zd_is_port IS TRUE) AND ((zp_name)::text = 'Asia'::text))
  • Rows Removed by Filter: 22831
14.          

CTE pco

15. 340.350 729.724 ↓ 7.1 231,512 1

Hash Semi Join (cost=0.91..98,663.88 rows=32,481 width=32) (actual time=3.705..729.724 rows=231,512 loops=1)

  • Hash Cond: (port_call.zone_id = origin_zone.zd_id)
16. 385.705 385.705 ↑ 2.0 666,186 1

Seq Scan on port_call (cost=0.00..94,570.42 rows=1,359,542 width=20) (actual time=0.023..385.705 rows=666,186 loops=1)

17. 0.170 3.669 ↓ 19.4 544 1

Hash (cost=0.56..0.56 rows=28 width=4) (actual time=3.669..3.669 rows=544 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
18. 3.499 3.499 ↓ 19.4 544 1

CTE Scan on origin_zone (cost=0.00..0.56 rows=28 width=4) (actual time=0.011..3.499 rows=544 loops=1)

19.          

CTE pcd

20. 386.671 888.186 ↓ 7.1 231,512 1

Hash Semi Join (cost=0.91..98,663.88 rows=32,481 width=32) (actual time=63.068..888.186 rows=231,512 loops=1)

  • Hash Cond: (port_call_1.zone_id = destination_zone.zd_id)
21. 438.467 438.467 ↑ 2.0 666,186 1

Seq Scan on port_call port_call_1 (cost=0.00..94,570.42 rows=1,359,542 width=20) (actual time=0.006..438.467 rows=666,186 loops=1)

22. 0.230 63.048 ↓ 19.4 544 1

Hash (cost=0.56..0.56 rows=28 width=4) (actual time=63.048..63.048 rows=544 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 28kB
23. 62.818 62.818 ↓ 19.4 544 1

CTE Scan on destination_zone (cost=0.00..0.56 rows=28 width=4) (actual time=5.725..62.818 rows=544 loops=1)

24. 38.878 4,363.981 ↓ 11.8 2,355 1

HashAggregate (cost=6,126.14..6,130.14 rows=200 width=12) (actual time=4,360.707..4,363.981 rows=2,355 loops=1)

  • Group Key: pco.pco_day
25. 144.980 4,325.103 ↓ 32.9 68,425 1

Hash Join (cost=5,282.90..6,115.74 rows=2,081 width=12) (actual time=4,090.524..4,325.103 rows=68,425 loops=1)

  • Hash Cond: (pcd.pcd_id = super_trade.port_call_destination_id)
26. 152.701 152.701 ↓ 7.1 231,512 1

CTE Scan on pcd (cost=0.00..649.62 rows=32,481 width=4) (actual time=63.070..152.701 rows=231,512 loops=1)

27. 36.019 4,027.422 ↓ 6.0 68,425 1

Hash (cost=5,141.12..5,141.12 rows=11,343 width=20) (actual time=4,027.421..4,027.422 rows=68,425 loops=1)

  • Buckets: 131072 (originally 16384) Batches: 1 (originally 1) Memory Usage: 4498kB
28. 146.527 3,991.403 ↓ 6.0 68,425 1

Hash Join (cost=4,053.26..5,141.12 rows=11,343 width=20) (actual time=3,756.633..3,991.403 rows=68,425 loops=1)

  • Hash Cond: (pco.pco_id = super_trade.port_call_origin_id)
29. 92.048 92.048 ↓ 7.1 231,512 1

CTE Scan on pco (cost=0.00..649.62 rows=32,481 width=16) (actual time=3.707..92.048 rows=231,512 loops=1)

30. 40.383 3,752.828 ↓ 1.2 68,425 1

Hash (cost=3,336.22..3,336.22 rows=57,363 width=16) (actual time=3,752.828..3,752.828 rows=68,425 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 4232kB
31. 93.036 3,712.445 ↓ 1.2 68,425 1

Hash Join (cost=1,466.57..3,336.22 rows=57,363 width=16) (actual time=2,538.089..3,712.445 rows=68,425 loops=1)

  • Hash Cond: (super_trade.port_call_destination_id = pcd_1.pcd_id)
32. 255.538 2,147.486 ↑ 1.2 97,375 1

Nested Loop (cost=731.24..1,658.32 rows=115,966 width=12) (actual time=1,066.153..2,147.486 rows=97,375 loops=1)

33. 291.553 1,197.412 ↓ 1,157.6 231,512 1

HashAggregate (cost=730.82..732.82 rows=200 width=4) (actual time=1,066.129..1,197.412 rows=231,512 loops=1)

  • Group Key: pco_1.pco_id
34. 905.859 905.859 ↓ 7.1 231,512 1

CTE Scan on pco pco_1 (cost=0.00..649.62 rows=32,481 width=4) (actual time=0.001..905.859 rows=231,512 loops=1)

35. 694.536 694.536 ↓ 0.0 0 231,512

Index Only Scan using super_trade_unique_index on super_trade (cost=0.42..4.62 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=231,512)

  • Index Cond: (port_call_origin_id = pco_1.pco_id)
  • Heap Fetches: 50419
36. 124.012 1,471.923 ↓ 1,157.6 231,512 1

Hash (cost=732.82..732.82 rows=200 width=4) (actual time=1,471.923..1,471.923 rows=231,512 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 1 (originally 1) Memory Usage: 10188kB
37. 314.863 1,347.911 ↓ 1,157.6 231,512 1

HashAggregate (cost=730.82..732.82 rows=200 width=4) (actual time=1,209.012..1,347.911 rows=231,512 loops=1)

  • Group Key: pcd_1.pcd_id
38. 1,033.048 1,033.048 ↓ 7.1 231,512 1

CTE Scan on pcd pcd_1 (cost=0.00..649.62 rows=32,481 width=4) (actual time=0.001..1,033.048 rows=231,512 loops=1)

Planning time : 1.799 ms
Execution time : 4,377.045 ms