explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IPOn

Settings
# exclusive inclusive rows x rows loops node
1. 525.879 2,422.627 ↑ 11.9 715,294 1

HashAggregate (cost=12,435,129.39..12,541,741.89 rows=8,529,000 width=28) (actual time=2,155.597..2,422.627 rows=715,294 loops=1)

  • Group Key: "*SELECT* 1".co_id, vz.id
2. 87.773 1,896.748 ↑ 622.2 761,696 1

Merge Join (cost=586,705.18..7,696,045.78 rows=473,908,361 width=20) (actual time=1,731.381..1,896.748 rows=761,696 loops=1)

  • Merge Cond: (vzc.zoneid = "*SELECT* 1".zo_id)
3. 15.717 311.895 ↑ 2.2 63,318 1

Sort (cost=58,866.95..59,221.12 rows=141,670 width=12) (actual time=306.558..311.895 rows=63,318 loops=1)

  • Sort Key: vzc.zoneid
  • Sort Method: quicksort Memory: 4505kB
4. 160.836 296.178 ↑ 2.2 63,318 1

Hash Join (cost=4,257.48..46,745.54 rows=141,670 width=12) (actual time=16.493..296.178 rows=63,318 loops=1)

  • Hash Cond: (vzc.visualzoneid = vz.id)
5. 126.005 126.005 ↑ 1.0 2,041,335 1

Seq Scan on tbl_visual_zone_content vzc (cost=0.00..33,416.35 rows=2,041,335 width=8) (actual time=0.008..126.005 rows=2,041,335 loops=1)

6. 4.191 9.337 ↑ 1.0 41,550 1

Hash (cost=3,724.42..3,724.42 rows=42,645 width=8) (actual time=9.337..9.337 rows=41,550 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2136kB
7. 5.146 5.146 ↑ 1.0 41,550 1

Index Only Scan using tbl_visual_zone_layerid_id_index on tbl_visual_zone vz (cost=0.42..3,724.42 rows=42,645 width=8) (actual time=0.015..5.146 rows=41,550 loops=1)

  • Index Cond: (layerid = ANY ('{4,107}'::integer[]))
  • Heap Fetches: 74
8. 143.929 1,497.080 ↓ 1.1 761,695 1

Sort (cost=527,838.23..529,510.81 rows=669,032 width=16) (actual time=1,422.873..1,497.080 rows=761,695 loops=1)

  • Sort Key: "*SELECT* 1".zo_id
  • Sort Method: quicksort Memory: 42042kB
9. 18.820 1,353.151 ↑ 1.8 380,848 1

Append (cost=211,244.52..463,103.65 rows=669,032 width=16) (actual time=1,252.209..1,353.151 rows=380,848 loops=1)

10. 26.463 1,328.794 ↑ 1.8 380,848 1

Subquery Scan on *SELECT* 1 (cost=211,244.52..264,880.14 rows=669,031 width=16) (actual time=1,252.208..1,328.794 rows=380,848 loops=1)

11. 54.115 1,302.331 ↑ 1.8 380,848 1

Hash Join (cost=211,244.52..258,189.83 rows=669,031 width=60) (actual time=1,252.207..1,302.331 rows=380,848 loops=1)

  • Hash Cond: ("*SELECT* 1_1".co_id = rtpezocoav.co_id)
12. 2.253 5.288 ↓ 5.3 4,078 1

HashAggregate (cost=5,688.17..5,695.93 rows=776 width=8) (actual time=4.389..5.288 rows=4,078 loops=1)

  • Group Key: "*SELECT* 1_1".pe_id, "*SELECT* 1_1".co_id
13. 0.525 3.035 ↓ 1.1 8,156 1

Append (cost=215.92..5,649.39 rows=7,757 width=8) (actual time=0.483..3.035 rows=8,156 loops=1)

14. 0.681 2.491 ↓ 1.1 8,156 1

Subquery Scan on *SELECT* 1_1 (cost=215.92..1,692.82 rows=7,756 width=8) (actual time=0.483..2.491 rows=8,156 loops=1)

15. 1.344 1.810 ↓ 1.1 8,156 1

Bitmap Heap Scan on rtsepecoav (cost=215.92..1,615.26 rows=7,756 width=12) (actual time=0.481..1.810 rows=8,156 loops=1)

  • Recheck Cond: ((ad_id = 3) AND (pe_id = 5))
  • Heap Blocks: exact=148
16. 0.466 0.466 ↓ 1.1 8,156 1

Bitmap Index Scan on rtsepecoav_ad_id_pe_id_co_id_index (cost=0.00..213.98 rows=7,756 width=0) (actual time=0.466..0.466 rows=8,156 loops=1)

  • Index Cond: ((ad_id = 3) AND (pe_id = 5))
17. 0.000 0.019 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=3,814.32..3,956.57 rows=1 width=8) (actual time=0.019..0.019 rows=0 loops=1)

18. 0.001 0.020 ↓ 0.0 0 1

Nested Loop (cost=3,814.32..3,956.56 rows=1 width=12) (actual time=0.019..0.020 rows=0 loops=1)

19. 0.001 0.019 ↓ 0.0 0 1

Nested Loop (cost=3,814.18..3,956.39 rows=1 width=12) (actual time=0.019..0.019 rows=0 loops=1)

20. 0.000 0.018 ↓ 0.0 0 1

Merge Anti Join (cost=3,813.76..3,955.38 rows=1 width=12) (actual time=0.018..0.018 rows=0 loops=1)

  • Merge Cond: ((av.se_id = av_1.se_id) AND (av.co_id = av_1.co_id))
  • Join Filter: (av_1.pe_id = rtpe.id)
21. 0.004 0.018 ↓ 0.0 0 1

Sort (cost=1,697.42..1,711.80 rows=5,751 width=12) (actual time=0.018..0.018 rows=0 loops=1)

  • Sort Key: av.se_id, av.co_id
  • Sort Method: quicksort Memory: 25kB
22. 0.002 0.014 ↓ 0.0 0 1

Nested Loop (cost=31.25..1,338.29 rows=5,751 width=12) (actual time=0.014..0.014 rows=0 loops=1)

23. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on rtpe (cost=0.00..1.45 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)

  • Filter: (id = 5)
  • Rows Removed by Filter: 38
24. 0.001 0.002 ↓ 0.0 0 1

Bitmap Heap Scan on rtsepecoav av (cost=31.25..1,326.27 rows=1,057 width=12) (actual time=0.002..0.002 rows=0 loops=1)

  • Recheck Cond: ((ad_id = 3) AND (pe_id = rtpe.baseline_id))
25. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on rtsepecoav_ad_id_pe_id_co_id_index (cost=0.00..30.99 rows=1,057 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: ((ad_id = 3) AND (pe_id = rtpe.baseline_id))
26. 0.000 0.000 ↓ 0.0 0

Sort (cost=2,116.34..2,135.73 rows=7,756 width=12) (never executed)

  • Sort Key: av_1.se_id, av_1.co_id
27. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on rtsepecoav av_1 (cost=215.92..1,615.26 rows=7,756 width=12) (never executed)

  • Recheck Cond: ((ad_id = 3) AND (pe_id = 5))
28. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on rtsepecoav_ad_id_pe_id_co_id_index (cost=0.00..213.98 rows=7,756 width=0) (never executed)

  • Index Cond: ((ad_id = 3) AND (pe_id = 5))
29. 0.000 0.000 ↓ 0.0 0

Index Scan using rtsepecoav_co_id_se_id_idx1 on rtsepecoav av_1_1 (cost=0.42..0.99 rows=2 width=12) (never executed)

  • Index Cond: ((co_id = av.co_id) AND (se_id = av.se_id))
  • Filter: (ad_id = 3)
30. 0.000 0.000 ↓ 0.0 0

Index Only Scan using rtpe_id_baseline_id_idx1 on rtpe rtpe_1 (cost=0.14..0.16 rows=1 width=8) (never executed)

  • Index Cond: ((id = 5) AND (baseline_id = av_1_1.pe_id))
  • Heap Fetches: 0
31. 447.942 1,242.928 ↓ 1.2 3,817,748 1

Hash (cost=164,744.14..164,744.14 rows=3,264,977 width=20) (actual time=1,242.928..1,242.928 rows=3,817,748 loops=1)

  • Buckets: 4194304 Batches: 1 Memory Usage: 226639kB
32. 792.865 794.986 ↓ 1.2 3,817,748 1

Bitmap Heap Scan on rtpezocoav (cost=33,819.21..164,744.14 rows=3,264,977 width=20) (actual time=5.703..794.986 rows=3,817,748 loops=1)

  • Recheck Cond: (pe_id = 5)
  • Rows Removed by Index Recheck: 804356
  • Filter: (ad_id = 6)
  • Rows Removed by Filter: 610147
  • Heap Blocks: lossy=41527
33. 2.121 2.121 ↑ 10.6 416,000 1

Bitmap Index Scan on rtpezocoav_pe_id_idx (cost=0.00..33,002.96 rows=4,398,262 width=0) (actual time=2.121..2.121 rows=416,000 loops=1)

  • Index Cond: (pe_id = 5)
34. 0.000 5.537 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=93,550.96..198,223.50 rows=1 width=16) (actual time=5.537..5.537 rows=0 loops=1)

35. 0.001 5.537 ↓ 0.0 0 1

Nested Loop Anti Join (cost=93,550.96..198,223.49 rows=1 width=60) (actual time=5.537..5.537 rows=0 loops=1)

  • Join Filter: (av_1_2.pe_id = rtpe_2.id)
36. 1.549 5.536 ↓ 0.0 0 1

Hash Join (cost=93,550.53..102,199.99 rows=186,237 width=24) (actual time=5.536..5.536 rows=0 loops=1)

  • Hash Cond: ("*SELECT* 1_2".co_id = av_2.co_id)
37. 1.248 3.787 ↑ 776.0 1 1

HashAggregate (cost=5,688.17..5,695.93 rows=776 width=8) (actual time=3.787..3.787 rows=1 loops=1)

  • Group Key: "*SELECT* 1_2".pe_id, "*SELECT* 1_2".co_id
38. 0.413 2.539 ↓ 1.1 8,156 1

Append (cost=215.92..5,649.39 rows=7,757 width=8) (actual time=0.480..2.539 rows=8,156 loops=1)

39. 0.507 2.103 ↓ 1.1 8,156 1

Subquery Scan on *SELECT* 1_2 (cost=215.92..1,692.82 rows=7,756 width=8) (actual time=0.480..2.103 rows=8,156 loops=1)

40. 1.133 1.596 ↓ 1.1 8,156 1

Bitmap Heap Scan on rtsepecoav rtsepecoav_1 (cost=215.92..1,615.26 rows=7,756 width=12) (actual time=0.479..1.596 rows=8,156 loops=1)

  • Recheck Cond: ((ad_id = 3) AND (pe_id = 5))
  • Heap Blocks: exact=148
41. 0.463 0.463 ↓ 1.1 8,156 1

Bitmap Index Scan on rtsepecoav_ad_id_pe_id_co_id_index (cost=0.00..213.98 rows=7,756 width=0) (actual time=0.463..0.463 rows=8,156 loops=1)

  • Index Cond: ((ad_id = 3) AND (pe_id = 5))
42. 0.000 0.023 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_2 (cost=3,814.32..3,956.57 rows=1 width=8) (actual time=0.023..0.023 rows=0 loops=1)

43. 0.000 0.023 ↓ 0.0 0 1

Nested Loop (cost=3,814.32..3,956.56 rows=1 width=12) (actual time=0.023..0.023 rows=0 loops=1)

44. 0.001 0.023 ↓ 0.0 0 1

Nested Loop (cost=3,814.18..3,956.39 rows=1 width=12) (actual time=0.023..0.023 rows=0 loops=1)

45. 0.000 0.022 ↓ 0.0 0 1

Merge Anti Join (cost=3,813.76..3,955.38 rows=1 width=12) (actual time=0.022..0.022 rows=0 loops=1)

  • Merge Cond: ((av_3.se_id = av_1_3.se_id) AND (av_3.co_id = av_1_3.co_id))
  • Join Filter: (av_1_3.pe_id = rtpe_3.id)
46. 0.007 0.022 ↓ 0.0 0 1

Sort (cost=1,697.42..1,711.80 rows=5,751 width=12) (actual time=0.022..0.022 rows=0 loops=1)

  • Sort Key: av_3.se_id, av_3.co_id
  • Sort Method: quicksort Memory: 25kB
47. 0.001 0.015 ↓ 0.0 0 1

Nested Loop (cost=31.25..1,338.29 rows=5,751 width=12) (actual time=0.015..0.015 rows=0 loops=1)

48. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on rtpe rtpe_3 (cost=0.00..1.45 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)

  • Filter: (id = 5)
  • Rows Removed by Filter: 38
49. 0.001 0.002 ↓ 0.0 0 1

Bitmap Heap Scan on rtsepecoav av_3 (cost=31.25..1,326.27 rows=1,057 width=12) (actual time=0.002..0.002 rows=0 loops=1)

  • Recheck Cond: ((ad_id = 3) AND (pe_id = rtpe_3.baseline_id))
50. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on rtsepecoav_ad_id_pe_id_co_id_index (cost=0.00..30.99 rows=1,057 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: ((ad_id = 3) AND (pe_id = rtpe_3.baseline_id))
51. 0.000 0.000 ↓ 0.0 0

Sort (cost=2,116.34..2,135.73 rows=7,756 width=12) (never executed)

  • Sort Key: av_1_3.se_id, av_1_3.co_id
52. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on rtsepecoav av_1_3 (cost=215.92..1,615.26 rows=7,756 width=12) (never executed)

  • Recheck Cond: ((ad_id = 3) AND (pe_id = 5))
53. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on rtsepecoav_ad_id_pe_id_co_id_index (cost=0.00..213.98 rows=7,756 width=0) (never executed)

  • Index Cond: ((ad_id = 3) AND (pe_id = 5))
54. 0.000 0.000 ↓ 0.0 0

Index Scan using rtsepecoav_co_id_se_id_idx1 on rtsepecoav av_1_4 (cost=0.42..0.99 rows=2 width=12) (never executed)

  • Index Cond: ((co_id = av_3.co_id) AND (se_id = av_3.se_id))
  • Filter: (ad_id = 3)
55. 0.000 0.000 ↓ 0.0 0

Index Only Scan using rtpe_id_baseline_id_idx1 on rtpe rtpe_1_1 (cost=0.14..0.16 rows=1 width=8) (never executed)

  • Index Cond: ((id = 5) AND (baseline_id = av_1_4.pe_id))
  • Heap Fetches: 0
56. 0.001 0.200 ↓ 0.0 0 1

Hash (cost=76,501.55..76,501.55 rows=908,864 width=24) (actual time=0.200..0.200 rows=0 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 8192kB
57. 0.002 0.199 ↓ 0.0 0 1

Nested Loop (cost=2,959.93..76,501.55 rows=908,864 width=24) (actual time=0.199..0.199 rows=0 loops=1)

58. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on rtpe rtpe_2 (cost=0.00..1.45 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)

  • Filter: (id = 5)
  • Rows Removed by Filter: 38
59. 0.067 0.192 ↓ 0.0 0 1

Bitmap Heap Scan on rtpezocoav av_2 (cost=2,959.93..73,656.62 rows=284,348 width=24) (actual time=0.191..0.192 rows=0 loops=1)

  • Recheck Cond: (pe_id = rtpe_2.baseline_id)
  • Filter: (ad_id = 6)
  • Heap Blocks: lossy=55
60. 0.125 0.125 ↑ 299.3 1,280 1

Bitmap Index Scan on rtpezocoav_pe_id_idx (cost=0.00..2,888.84 rows=383,046 width=0) (actual time=0.125..0.125 rows=1,280 loops=1)

  • Index Cond: (pe_id = rtpe_2.baseline_id)
61. 0.000 0.000 ↓ 0.0 0

Index Only Scan using rtpezocoav_unique on rtpezocoav av_1_2 (cost=0.43..0.50 rows=1 width=16) (never executed)

  • Index Cond: ((pe_id = 5) AND (zo_id = av_2.zo_id) AND (co_id = av_2.co_id) AND (ad_id = av_2.ad_id) AND (ad_id = 6))
  • Heap Fetches: 0
Planning time : 3.073 ms
Execution time : 2,466.556 ms