explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Vhof : prod

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 310,909.238 ↓ 0.0 0 1

Unique (cost=11,082,153.150..11,082,154.780 rows=163 width=30) (actual time=310,909.238..310,909.238 rows=0 loops=1)

2.          

CTE bravo_seven

3. 25,700.784 72,042.186 ↓ 7,952.6 61,012,047 1

Merge Join (cost=6,156.500..2,448,668.380 rows=7,672 width=20) (actual time=369.840..72,042.186 rows=61,012,047 loops=1)

  • Merge Cond: ((uniform_five_hotel.romeo = uniform_mike.romeo) AND ((uniform_five_hotel.hotel)::text = (seven.hotel)::text) AND ((uniform_five_hotel.whiskey_two)::text = (uniform_mike.whiskey_two)::text))
4. 41,320.466 41,320.466 ↓ 1.0 60,298,211 1

Index Only Scan using yankee_whiskey on three_india uniform_five_hotel (cost=0.690..2,006,257.660 rows=58,127,957 width=41) (actual time=0.035..41,320.466 rows=60,298,211 loops=1)

  • Index Cond: (alpha = 'delta_oscar'::bpchar)
  • Heap Fetches: 1009721
5. 4,958.502 5,020.936 ↓ 2,792.3 61,012,282 1

Sort (cost=6,155.810..6,210.440 rows=21,850 width=51) (actual time=369.802..5,020.936 rows=61,012,282 loops=1)

  • Sort Key: uniform_mike.romeo, seven.hotel, uniform_mike.whiskey_two
  • Sort Method: quicksort Memory: 12504kB
6. 34.596 62.434 ↓ 3.9 85,875 1

Hash Join (cost=180.320..4,580.940 rows=21,850 width=51) (actual time=2.002..62.434 rows=85,875 loops=1)

  • Hash Cond: ((uniform_mike.romeo = seven.romeo) AND ((uniform_mike.bravo_quebec)::text = (seven.bravo_quebec)::text))
7. 25.989 25.989 ↓ 1.0 85,875 1

Seq Scan on three_five uniform_mike (cost=0.000..3,109.710 rows=85,792 width=31) (actual time=0.132..25.989 rows=85,875 loops=1)

  • Filter: (foxtrot_zulu = 'uniform_five_foxtrot'::bpchar)
  • Rows Removed by Filter: 19706
8. 1.067 1.849 ↑ 1.0 4,728 1

Hash (cost=108.730..108.730 rows=4,773 width=37) (actual time=1.848..1.849 rows=4,728 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 390kB
9. 0.782 0.782 ↑ 1.0 4,728 1

Seq Scan on papa seven (cost=0.000..108.730 rows=4,773 width=37) (actual time=0.003..0.782 rows=4,728 loops=1)

10. 0.008 310,909.238 ↓ 0.0 0 1

Sort (cost=8,633,484.780..8,633,485.190 rows=163 width=30) (actual time=310,909.238..310,909.238 rows=0 loops=1)

  • Sort Key: four_xray.bravo_quebec, four_xray.sierra_zulu, whiskey_xray3.xray_bravo
  • Sort Method: quicksort Memory: 25kB
11. 41.740 310,909.230 ↓ 0.0 0 1

Hash Join (cost=8,551,657.300..8,633,478.790 rows=163 width=30) (actual time=310,909.230..310,909.230 rows=0 loops=1)

  • Hash Cond: (whiskey_xray3.whiskey_golf = mike_five.whiskey_golf)
12. 675.604 310,867.477 ↓ 383.2 249,455 1

Nested Loop (cost=8,551,656.240..8,633,473.660 rows=651 width=35) (actual time=214,321.159..310,867.477 rows=249,455 loops=1)

13. 25,127.519 245,885.243 ↓ 952.5 1,108,735 1

Merge Semi Join (cost=8,551,655.680..8,622,754.820 rows=1,164 width=102) (actual time=190,751.035..245,885.243 rows=1,108,735 loops=1)

  • Merge Cond: ((four_xray.bravo_quebec)::text = (four_four.bravo_quebec)::text)
  • Join Filter: ((four_four.sierra_zulu five_romeo NULL) OR (four_four.sierra_zulu = four_xray.sierra_zulu))
  • Rows Removed by Join Filter: 124463155
14. 291.164 54,842.817 ↓ 253.9 1,176,267 1

Merge Left Join (cost=8,551,007.190..8,621,662.270 rows=4,632 width=24) (actual time=48,192.544..54,842.817 rows=1,176,267 loops=1)

  • Merge Cond: (((four_xray.bravo_quebec)::text = (sierra_alpha.bravo_quebec)::text) AND (four_xray.sierra_zulu = sierra_alpha.victor_foxtrot_uniform))
  • Filter: (('uniform_five_foxtrot'::text) five_romeo NULL)
  • Rows Removed by Filter: 7998
15. 2,647.132 3,376.087 ↓ 1.3 1,184,265 1

Sort (cost=151,327.530..153,643.260 rows=926,291 width=24) (actual time=3,052.959..3,376.087 rows=1,184,265 loops=1)

  • Sort Key: four_xray.bravo_quebec, four_xray.sierra_zulu
  • Sort Method: external merge Disk: 40152kB
16. 197.952 728.955 ↓ 1.3 1,184,265 1

Merge Join (cost=0.790..59,526.970 rows=926,291 width=24) (actual time=1.706..728.955 rows=1,184,265 loops=1)

  • Merge Cond: (two_delta.sierra_zulu = four_xray.sierra_zulu)
17. 61.935 61.935 ↓ 1.0 50,092 1

Index Scan using india_oscar on oscar two_delta (cost=0.290..3,530.270 rows=49,047 width=5) (actual time=0.091..61.935 rows=50,092 loops=1)

  • Filter: ((yankee_india)::text = ANY ('golf'::text[]))
  • Rows Removed by Filter: 28981
18. 469.068 469.068 ↓ 1.0 1,495,067 1

Index Only Scan using three_zulu on tango_kilo four_xray (cost=0.430..43,000.430 rows=1,444,744 width=19) (actual time=0.018..469.068 rows=1,495,067 loops=1)

  • Filter: ((bravo_quebec)::text <> november_november ('yankee_seven'::text[]))
  • Rows Removed by Filter: 9914
  • Heap Fetches: 57903
19. 6.254 51,175.566 ↑ 21.2 54,599 1

Materialize (cost=8,399,679.660..8,457,471.970 rows=1,155,846 width=49) (actual time=45,139.579..51,175.566 rows=54,599 loops=1)

20. 1,043.091 51,169.312 ↑ 21.2 54,599 1

Unique (cost=8,399,679.660..8,443,023.900 rows=1,155,846 width=49) (actual time=45,139.574..51,169.312 rows=54,599 loops=1)

21. 13,756.693 50,126.221 ↓ 1.6 9,316,444 1

Sort (cost=8,399,679.660..8,414,127.740 rows=5,779,232 width=49) (actual time=45,139.572..50,126.221 rows=9,316,444 loops=1)

  • Sort Key: sierra_alpha.bravo_quebec, sierra_alpha.victor_foxtrot_uniform
  • Sort Method: external merge Disk: 267560kB
22. 497.166 36,369.528 ↓ 1.6 9,316,444 1

Merge Join (cost=1.390..7,637,725.210 rows=5,779,232 width=49) (actual time=0.078..36,369.528 rows=9,316,444 loops=1)

  • Merge Cond: (november_romeo.two_golf = sierra_alpha.two_golf)
  • Join Filter: (NOT (delta_four 4))
  • Rows Removed by Join Filter: 1786968
23. 222.742 222.742 ↑ 1.2 1,786,968 1

Index Only Scan using three_foxtrot on mike_foxtrot november_romeo (cost=0.430..53,555.640 rows=2,134,814 width=8) (actual time=0.032..222.742 rows=1,786,968 loops=1)

  • Heap Fetches: 0
24. 2,339.384 2,339.384 ↑ 1.0 11,103,412 1

Index Only Scan using foxtrot_six on xray_india sierra_alpha (cost=0.690..402,669.660 rows=11,558,465 width=33) (actual time=0.017..2,339.384 rows=11,103,412 loops=1)

  • Heap Fetches: 0
25.          

SubPlan (forMerge Join)

26. 11,103.412 33,310.236 ↑ 1.0 1 11,103,412

Result (cost=0.600..0.610 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=11,103,412)

27.          

Initplan (forResult)

28. 0.000 22,206.824 ↑ 1.0 1 11,103,412

Limit (cost=0.560..0.600 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=11,103,412)

29. 22,206.824 22,206.824 ↑ 51.0 1 11,103,412

Index Only Scan Backward using kilo on xray_india two_alpha (cost=0.560..2.580 rows=51 width=8) (actual time=0.002..0.002 rows=1 loops=11,103,412)

  • Index Cond: ((two_golf = november_romeo.two_golf) AND (tango_whiskey five_romeo NOT NULL))
  • Heap Fetches: 0
30. 71,888.543 165,914.907 ↓ 24,161.9 185,370,449 1

Sort (cost=648.490..667.670 rows=7,672 width=98) (actual time=142,558.473..165,914.907 rows=185,370,449 loops=1)

  • Sort Key: four_four.bravo_quebec
  • Sort Method: external sort Disk: 1912032kB
31. 94,026.364 94,026.364 ↓ 7,952.6 61,012,047 1

CTE Scan on bravo_seven four_four (cost=0.000..153.440 rows=7,672 width=98) (actual time=369.843..94,026.364 rows=61,012,047 loops=1)

32. 22,191.798 64,306.630 ↓ 0.0 0 1,108,735

Index Scan using five_whiskey on juliet mike_alpha (cost=0.560..9.200 rows=1 width=35) (actual time=0.053..0.058 rows=0 loops=1,108,735)

  • Index Cond: ((sierra_zulu = four_xray.sierra_zulu) AND ((bravo_quebec)::text = (four_xray.bravo_quebec)::text))
  • Filter: ((foxtrot_zulu = 'uniform_five_foxtrot'::bpchar) AND ((xray_bravo)::text = ANY ('five_four'::text[])) AND (NOT (five_oscar.
  • Rows Removed by Filter: 5
33.          

SubPlan (forIndex Scan)

34. 42,114.832 42,114.832 ↓ 3.4 57 2,632,177

Index Scan using quebec on india_papa (cost=0.280..16.570 rows=17 width=16) (actual time=0.003..0.016 rows=57 loops=2,632,177)

  • Index Cond: ((xray_bravo)::text = (whiskey_xray3.xray_bravo)::text)
35. 0.005 0.013 ↑ 1.0 1 1

Hash (cost=1.050..1.050 rows=1 width=5) (actual time=0.013..0.013 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on victor_foxtrot_delta mike_five (cost=0.000..1.050 rows=1 width=5) (actual time=0.006..0.008 rows=1 loops=1)

  • Filter: ((november_lima)::text = 'three_juliet'::text)
  • Rows Removed by Filter: 3