explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sV1M : old_query

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.075 14,532.047 ↓ 2.0 2 1

GroupAggregate (cost=401,857.58..401,857.66 rows=1 width=129) (actual time=14,532.028..14,532.047 rows=2 loops=1)

  • Group Key: bravo_tango_oscar.kilo_romeo, ((((('zulu_papa'::text || (juliet_kilo.kilo_oscar)::text) || 'three_tango'::text) || (juliet_kilo.quebec_seven)::text) || ''yankee_four'1345'::bigint ELSE NULL::bigint END), juliet_kilo.delta_juliet, (CASE WHEN ((echo_victor.six_four = 4) OR (delta_yankee.six_four = 4)) THEN four_bravo.charlie_five ELSE NULL::bigint END), (CASE WHEN (four_bravo.xray five_romeo NULL) THEN uniform_alpha.bravo_six WHEN (uniform_alpha.bravo_six = six_whiskey.whiskey_tango) THEN four_foxtrot.bravo_six ELSE uniform_alpha.bravo_six END)
  • Buffers: shared hit=91,098 read=382,424, temp read=3,776 written=3,758
2. 0.216 14,531.972 ↓ 10.0 10 1

Sort (cost=401,857.58..401,857.59 rows=1 width=142) (actual time=14,531.969..14,531.972 rows=10 loops=1)

  • Sort Key: bravo_tango_oscar.kilo_romeo, ((((('zulu_papa'::text || (juliet_kilo.kilo_oscar)::text) || 'three_tango'::text) || (juliet_kilo.quebec_seven)::text) || ''yankee_four'1345'::bigint ELSE NULL::bigint END), juliet_kilo.delta_juliet, (CASE WHEN ((echo_victor.six_four = 4) OR (delta_yankee.six_four = 4)) THEN four_bravo.charlie_five ELSE NULL::bigint END), (CASE WHEN (four_bravo.xray five_romeo NULL) THEN uniform_alpha.bravo_six WHEN (uniform_alpha.bravo_six = six_whiskey.whiskey_tango) THEN four_foxtrot.bravo_six ELSE uniform_alpha.bravo_six END)
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=91,098 read=382,424, temp read=3,776 written=3,758
3. 0.043 14,531.756 ↓ 10.0 10 1

Nested Loop (cost=35,750.57..401,857.57 rows=1 width=142) (actual time=14,524.701..14,531.756 rows=10 loops=1)

  • Buffers: shared hit=91,098 read=382,424, temp read=3,776 written=3,758
4. 0.038 14,531.033 ↓ 10.0 10 1

Nested Loop (cost=35,750.29..401,853.31 rows=1 width=214) (actual time=14,524.043..14,531.033 rows=10 loops=1)

  • Buffers: shared hit=91,080 read=382,421, temp read=3,776 written=3,758
5. 0.029 14,530.045 ↓ 10.0 10 1

Nested Loop (cost=35,750.00..401,850.51 rows=1 width=214) (actual time=14,523.112..14,530.045 rows=10 loops=1)

  • Filter: ((oscar_victor.charlie_papa five_romeo NULL) OR (oscar_victor.mike_four = 8,085))
  • Buffers: shared hit=91,062 read=382,418, temp read=3,776 written=3,758
6. 4.010 14,528.556 ↓ 10.0 10 1

Nested Loop (cost=35,749.58..401,847.26 rows=1 width=222) (actual time=14,521.701..14,528.556 rows=10 loops=1)

  • Buffers: shared hit=91,026 read=382,414, temp read=3,776 written=3,758
7. 0.033 14,523.486 ↓ 10.0 10 1

Nested Loop (cost=35,730.40..401,826.07 rows=1 width=218) (actual time=14,518.815..14,523.486 rows=10 loops=1)

  • Buffers: shared hit=90,935 read=382,405, temp read=3,776 written=3,758
8. 0.000 14,522.703 ↓ 10.0 10 1

Nested Loop (cost=35,730.12..401,824.98 rows=1 width=226) (actual time=14,518.087..14,522.703 rows=10 loops=1)

  • Buffers: shared hit=90,916 read=382,403, temp read=3,776 written=3,758
9. 103.874 14,522.303 ↓ 10.0 10 1

Gather (cost=35,729.83..401,822.29 rows=1 width=210) (actual time=14,517.128..14,522.303 rows=10 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=90,891 read=382,398, temp read=3,776 written=3,758
10. 3.222 14,418.429 ↓ 3.0 3 3 / 3

Nested Loop (cost=34,729.83..400,822.19 rows=1 width=210) (actual time=14,058.760..14,418.429 rows=3 loops=3)

  • Buffers: shared hit=90,891 read=382,398, temp read=3,776 written=3,758
11. 15.070 14,001.165 ↓ 11.4 4,358 3 / 3

Hash Join (cost=34,729.54..399,691.92 rows=381 width=218) (actual time=1,060.564..14,001.165 rows=4,358 loops=3)

  • Filter: ((yankee_yankee.four_zulu five_romeo NULL) OR ((yankee_yankee.juliet_oscar)::text !~~* 'three_sierra'::text))
  • Buffers: shared hit=54,083 read=379,941, temp read=3,776 written=3,758
12. 4.400 13,985.862 ↓ 10.9 4,381 3 / 3

Hash Join (cost=34,692.35..399,653.67 rows=401 width=226) (actual time=1,060.258..13,985.862 rows=4,381 loops=3)

  • Buffers: shared hit=54,008 read=379,941, temp read=3,776 written=3,758
13. 5.255 13,980.960 ↓ 5.5 4,381 3 / 3

Hash Join (cost=34,658.54..399,613.29 rows=802 width=234) (actual time=1,059.747..13,980.960 rows=4,381 loops=3)

  • Buffers: shared hit=53,956 read=379,918, temp read=3,776 written=3,758
14. 5.317 13,970.646 ↓ 5.5 4,381 3 / 3

Hash Join (cost=34,541.62..399,494.27 rows=802 width=234) (actual time=1,054.676..13,970.646 rows=4,381 loops=3)

  • Buffers: shared hit=53,935 read=379,907, temp read=3,776 written=3,758
15. 6.419 13,960.023 ↓ 5.5 4,381 3 / 3

Hash Join (cost=34,386.91..399,337.45 rows=802 width=230) (actual time=1,049.354..13,960.023 rows=4,381 loops=3)

  • Buffers: shared hit=53,751 read=379,815, temp read=3,776 written=3,758
16. 4.755 13,935.177 ↓ 5.5 4,381 3 / 3

Hash Join (cost=33,148.30..398,096.74 rows=802 width=214) (actual time=1,030.216..13,935.177 rows=4,381 loops=3)

  • Buffers: shared hit=52,830 read=379,372, temp read=3,776 written=3,758
17. 5.410 13,930.407 ↓ 5.5 4,381 3 / 3

Hash Join (cost=33,147.25..398,089.13 rows=802 width=206) (actual time=1,030.182..13,930.407 rows=4,381 loops=3)

  • Buffers: shared hit=52,827 read=379,372, temp read=3,776 written=3,758
18. 9.032 13,914.187 ↓ 5.5 4,381 3 / 3

Nested Loop (cost=32,008.35..396,948.12 rows=802 width=214) (actual time=1,019.303..13,914.187 rows=4,381 loops=3)

  • Buffers: shared hit=51,223 read=378,573, temp read=3,776 written=3,758
19. 70.607 13,835.054 ↓ 5.5 4,381 3 / 3

Hash Join (cost=32,007.92..391,234.49 rows=802 width=213) (actual time=1,018.147..13,835.054 rows=4,381 loops=3)

  • Buffers: shared hit=48,657 read=377,946, temp read=3,776 written=3,758
20. 12,841.647 12,841.647 ↑ 1.1 4,388 3 / 3

Seq Scan on tango_lima four_bravo (cost=0.00..358,396.33 rows=5,045 width=126) (actual time=0.484..12,841.647 rows=4,388 loops=3)

  • Filter: ((four_bravo.golf_romeo_three five_romeo NOT NULL) AND (four_bravo.juliet_charlie = 0))
  • Buffers: shared hit=82 read=349,490
21. 47.735 922.800 ↓ 2.2 100,834 3 / 3

Hash (cost=30,804.68..30,804.68 rows=45,939 width=87) (actual time=922.800..922.800 rows=100,834 loops=3)

  • Buffers: shared hit=48,529 read=28,452, temp written=2,972
22. 874.277 875.065 ↓ 2.2 100,834 3 / 3

Seq Scan on foxtrot_romeo juliet_kilo (cost=94.50..30,804.68 rows=45,939 width=87) (actual time=0.958..875.065 rows=100,834 loops=3)

  • Filter: ((NOT (mike_mike delta_four 2)) AND (juliet_kilo.juliet_charlie = 0) AND (juliet_kilo.three_november = 1))
  • Buffers: shared hit=48,529 read=28,452
23.          

SubPlan (for Seq Scan)

24. 0.000 0.788 ↓ 0.0 0 3 / 3

Unique (cost=94.49..94.50 rows=1 width=8) (actual time=0.788..0.788 rows=0 loops=3)

  • Buffers: shared hit=23 read=5
25. 0.030 0.788 ↓ 0.0 0 3 / 3

Sort (cost=94.49..94.50 rows=1 width=8) (actual time=0.788..0.788 rows=0 loops=3)

  • Sort Key: bravo_tango_india.papa
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=23 read=5
26. 0.001 0.758 ↓ 0.0 0 3 / 3

Nested Loop (cost=4.77..94.48 rows=1 width=8) (actual time=0.758..0.758 rows=0 loops=3)

  • Buffers: shared hit=9 read=5
27. 0.017 0.757 ↓ 0.0 0 3 / 3

Hash Join (cost=4.48..93.92 rows=1 width=16) (actual time=0.757..0.757 rows=0 loops=3)

  • Buffers: shared hit=9 read=5
28. 0.094 0.094 ↑ 4,708.0 1 3 / 3

Seq Scan on india_seven bravo_tango_india (cost=0.00..77.08 rows=4,708 width=16) (actual time=0.094..0.094 rows=1 loops=3)

  • Buffers: shared hit=2 read=1
29. 0.001 0.646 ↓ 0.0 0 3 / 3

Hash (cost=4.47..4.47 rows=1 width=8) (actual time=0.646..0.646 rows=0 loops=3)

  • Buffers: shared hit=7 read=4
30. 0.645 0.645 ↓ 0.0 0 3 / 3

Index Scan using kilo_delta on foxtrot_romeo kilo_uniform (cost=0.42..4.47 rows=1 width=8) (actual time=0.645..0.645 rows=0 loops=3)

  • Index Cond: (two_papa1.november_hotel = 8,092)
  • Buffers: shared hit=7 read=4
31. 0.000 0.000 ↓ 0.0 0 / 3

Index Scan using seven on echo_romeo oscar_oscar (cost=0.29..0.56 rows=1 width=8) (never executed)

  • Index Cond: (oscar_oscar.mike_quebec = bravo_tango_india.echo_alpha)
  • Filter: ((NOT oscar_oscar.uniform_charlie) AND (oscar_oscar.juliet_charlie <> 999))
32. 70.101 70.101 ↓ 0.0 0 13,144 / 3

Index Scan using foxtrot_whiskey on tango_lima three_juliet (cost=0.43..7.12 rows=1 width=9) (actual time=0.016..0.016 rows=0 loops=13,144)

  • Index Cond: (four_bravo.xray = three_juliet.kilo_golf)
  • Buffers: shared hit=2,566 read=627
33. 2.194 10.810 ↑ 1.0 15,018 3 / 3

Hash (cost=951.18..951.18 rows=15,018 width=8) (actual time=10.810..10.810 rows=15,018 loops=3)

  • Buffers: shared hit=1,604 read=799
34. 8.616 8.616 ↑ 1.0 15,018 3 / 3

Seq Scan on zulu_tango kilo_foxtrot (cost=0.00..951.18 rows=15,018 width=8) (actual time=0.110..8.616 rows=15,018 loops=3)

  • Buffers: shared hit=1,604 read=799
35. 0.003 0.015 ↑ 1.0 2 3 / 3

Hash (cost=1.02..1.02 rows=2 width=16) (actual time=0.015..0.015 rows=2 loops=3)

  • Buffers: shared hit=3
36. 0.012 0.012 ↑ 1.0 2 3 / 3

Seq Scan on tango_romeo six_whiskey (cost=0.00..1.02 rows=2 width=16) (actual time=0.011..0.012 rows=2 loops=3)

  • Buffers: shared hit=3
37. 7.607 18.427 ↑ 1.0 35,236 3 / 3

Hash (cost=797.16..797.16 rows=35,316 width=24) (actual time=18.427..18.427 rows=35,236 loops=3)

  • Buffers: shared hit=890 read=442
38. 10.820 10.820 ↑ 1.0 35,236 3 / 3

Seq Scan on kilo_whiskey uniform_alpha (cost=0.00..797.16 rows=35,316 width=24) (actual time=1.167..10.820 rows=35,236 loops=3)

  • Buffers: shared hit=890 read=442
39. 0.657 5.306 ↑ 1.0 2,787 3 / 3

Hash (cost=119.87..119.87 rows=2,787 width=12) (actual time=5.306..5.306 rows=2,787 loops=3)

  • Buffers: shared hit=184 read=92
40. 4.649 4.649 ↑ 1.0 2,787 3 / 3

Seq Scan on charlie_delta echo_victor (cost=0.00..119.87 rows=2,787 width=12) (actual time=0.106..4.649 rows=2,787 loops=3)

  • Buffers: shared hit=184 read=92
41. 0.562 5.059 ↑ 1.0 2,787 3 / 3

Hash (cost=82.09..82.09 rows=2,787 width=8) (actual time=5.059..5.059 rows=2,787 loops=3)

  • Buffers: shared hit=21 read=11
42. 4.497 4.497 ↑ 1.0 2,787 3 / 3

Index Only Scan using hotel_uniform on charlie_delta six_sierra (cost=0.28..82.09 rows=2,787 width=8) (actual time=3.801..4.497 rows=2,787 loops=3)

  • Buffers: shared hit=21 read=11
43. 0.000 0.502 ↓ 0.0 0 3 / 3

Hash (cost=30.42..30.42 rows=271 width=8) (actual time=0.502..0.502 rows=0 loops=3)

  • Buffers: shared hit=52 read=23
44. 0.502 0.502 ↓ 0.0 0 3 / 3

Seq Scan on delta_sierra delta_sierra (cost=0.00..30.42 rows=271 width=8) (actual time=0.502..0.502 rows=0 loops=3)

  • Filter: lima(delta_sierra.zulu_victor, false)
  • Buffers: shared hit=52 read=23
45. 0.118 0.233 ↑ 1.0 542 3 / 3

Hash (cost=30.42..30.42 rows=542 width=13) (actual time=0.233..0.233 rows=542 loops=3)

  • Buffers: shared hit=75
46. 0.115 0.115 ↑ 1.0 542 3 / 3

Seq Scan on delta_sierra yankee_yankee (cost=0.00..30.42 rows=542 width=13) (actual time=0.003..0.115 rows=542 loops=3)

  • Buffers: shared hit=75
47. 414.042 414.042 ↓ 0.0 0 13,075 / 3

Index Scan using six_tango on mike_kilo bravo_tango_oscar (cost=0.29..2.95 rows=1 width=16) (actual time=0.095..0.095 rows=0 loops=13,075)

  • Index Cond: (bravo_tango_oscar.papa = juliet_kilo.romeo_three)
  • Filter: ((bravo_tango_oscar.kilo_romeo >= 'november_echo'::timestamp without time zone) AND (lima(bravo_tango_oscar.hotel_sierra, 0) <> november_november ('india_lima'::integer[])))
  • Buffers: shared hit=36,808 read=2,457
48. 1.370 1.370 ↑ 1.0 1 10

Index Scan using tango_zulu on kilo_whiskey four_foxtrot (cost=0.29..2.69 rows=1 width=24) (actual time=0.137..0.137 rows=1 loops=10)

  • Index Cond: (four_foxtrot.bravo_six = juliet_kilo.golf_victor)
  • Buffers: shared hit=25 read=5
49. 0.750 0.750 ↑ 1.0 1 10

Index Only Scan using hotel_uniform on charlie_delta victor (cost=0.28..1.10 rows=1 width=8) (actual time=0.075..0.075 rows=1 loops=10)

  • Index Cond: (victor.charlie_lima = four_foxtrot.romeo_india)
  • Buffers: shared hit=19 read=2
50. 0.000 1.060 ↑ 1.0 1 10

Index Scan using hotel_uniform on charlie_delta delta_yankee (cost=19.18..21.18 rows=1 width=12) (actual time=0.106..0.106 rows=1 loops=10)

  • Index Cond: (delta_yankee.charlie_lima = (delta_four 1))
  • Buffers: shared hit=28 read=2
51.          

SubPlan (for Index Scan)

52. 0.020 3.940 ↑ 1.0 1 10

Limit (cost=18.89..18.90 rows=1 width=16) (actual time=0.393..0.394 rows=1 loops=10)

  • Buffers: shared hit=63 read=7
53. 0.120 3.920 ↑ 1.0 1 10

Sort (cost=18.89..18.90 rows=1 width=16) (actual time=0.392..0.392 rows=1 loops=10)

  • Sort Key: golf_romeo_six.tango_mike
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=63 read=7
54. 3.800 3.800 ↑ 1.0 1 10

Seq Scan on oscar_echo golf_romeo_six (cost=0.00..18.88 rows=1 width=16) (actual time=0.324..0.380 rows=1 loops=10)

  • Filter: (((golf_romeo_six.tango_mike five_romeo NULL) OR (golf_romeo_six.tango_mike = four_bravo.charlie_five)) AND (golf_romeo_six.juliet_charlie = 0) AND (golf_romeo_six.whiskey_romeo = four_bravo.india_two))
  • Buffers: shared hit=63 read=7
55. 1.460 1.460 ↑ 1.0 1 10

Index Scan using charlie_sierra on foxtrot_yankee oscar_victor (cost=0.42..3.24 rows=1 width=12) (actual time=0.146..0.146 rows=1 loops=10)

  • Index Cond: (juliet_kilo.romeo_golf = oscar_victor.charlie_papa)
  • Buffers: shared hit=36 read=4
56. 0.950 0.950 ↑ 1.0 1 10

Index Scan using hotel_yankee on quebec_three bravo_romeo (cost=0.29..2.79 rows=1 width=16) (actual time=0.095..0.095 rows=1 loops=10)

  • Index Cond: (bravo_romeo.india_juliet = four_bravo.echo_tango)
  • Buffers: shared hit=18 read=3
57. 0.680 0.680 ↓ 0.0 0 10

Index Scan using juliet_romeo on two_tango five_five (cost=0.28..2.26 rows=1 width=8) (actual time=0.068..0.068 rows=0 loops=10)

  • Index Cond: (five_five.charlie_seven = bravo_romeo.uniform_india)
  • Filter: (NOT five_five.sierra)
  • Buffers: shared hit=18 read=3
Planning time : 90.858 ms
Execution time : 14,538.154 ms