explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WIlY : Optimization for: plan #4952

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 19,042.111 4,259,003.306 ↑ 6.1 772 1

Result (cost=145,107,304.770..217,650,042.140 rows=4,683 width=275) (actual time=4,239,984.731..4,259,003.306 rows=772 loops=1)

2. 3.045 4,239,961.195 ↑ 6.1 772 1

Sort (cost=145,107,304.770..145,107,316.480 rows=4,683 width=267) (actual time=4,239,960.614..4,239,961.195 rows=772 loops=1)

  • Sort Key: (uniform_bravo((six.papa)::text))
  • Sort Method: quicksort Memory: 226kB
3. 17.071 4,239,958.150 ↑ 6.1 772 1

Group (cost=72,565,382.410..145,107,019.270 rows=4,683 width=267) (actual time=4,239,932.202..4,239,958.150 rows=772 loops=1)

  • Group Key: five_mike.juliet, five_mike.whiskey_yankee, six.papa, six.lima, six.foxtrot, six.echo, six.four5, six.four1, (CASE WHEN ((delta_four 1) five_romeo NULL) THEN six.four1 ELSE (delta_four 2) END), (CASE WHEN ((delta_four 3) five_romeo NULL) THEN six.four1 ELSE (delta_four 4) END)
4. 309.540 4,239,941.079 ↓ 7.6 35,814 1

Sort (cost=72,565,382.410..72,565,394.120 rows=4,683 width=139) (actual time=4,239,932.193..4,239,941.079 rows=35,814 loops=1)

  • Sort Key: five_mike.juliet, six.papa, six.lima, six.foxtrot, six.echo, six.four5, six.four1, (CASE WHEN ((delta_four 1) five_romeo NULL) THEN six.four1 ELSE (delta_four 2) END), (CASE WHEN ((delta_four 3) five_romeo NULL) THEN six.four1 ELSE (delta_four 4) END)
  • Sort Method: external merge Disk: 3424kB
5. 824.595 4,239,631.539 ↓ 7.6 35,814 1

Hash Join (cost=252.650..72,565,096.900 rows=4,683 width=139) (actual time=273.639..4,239,631.539 rows=35,814 loops=1)

  • Hash Cond: (five_mike.juliet = six.november_papa)
6. 1,095,902.546 1,095,902.546 ↓ 2.8 37,753 1

Seq Scan on five_mike (cost=0.000..23,358.790 rows=13,671 width=8) (actual time=170.198..1,095,902.546 rows=37,753 loops=1)

  • Filter: ((yankee five_romeo NULL) AND (tango <= 'xray_hotel'::timestamp without time zone) AND (whiskey_yankee = 1) AND (seven6020oscar = ANY ('uniform_mike'::integer[])) AND (whiskey_xray(juliet, whiskey_yankee, 'xray_oscar'::date) > 'november_golf'::double precision))
  • Rows Removed by Filter: 37194
7. 0.373 1.808 ↑ 1.1 1,295 1

Hash (cost=234.970..234.970 rows=1,415 width=75) (actual time=1.808..1.808 rows=1,295 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 162kB
8. 1.435 1.435 ↑ 1.1 1,295 1

Seq Scan on six (cost=0.000..234.970 rows=1,415 width=75) (actual time=0.009..1.435 rows=1,295 loops=1)

  • Filter: ((charlie five_romeo NULL) AND (five_papa = 1) AND (golf = 1))
  • Rows Removed by Filter: 2836
9.          

SubPlan (forHash Join)

10. 143.256 792,707.076 ↑ 1.0 1 35,814

Limit (cost=3,872.590..3,872.600 rows=1 width=14) (actual time=22.133..22.134 rows=1 loops=35,814)

11. 966.978 792,563.820 ↑ 4.0 1 35,814

Sort (cost=3,872.590..3,872.600 rows=4 width=14) (actual time=22.130..22.130 rows=1 loops=35,814)

  • Sort Key: zulu1.tango DESC
  • Sort Method: top-N heapsort Memory: 25kB
12. 791,596.842 791,596.842 ↓ 2.5 10 35,814

Seq Scan on five_mike whiskey_tango (cost=0.000..3,872.570 rows=4 width=14) (actual time=4.048..22.103 rows=10 loops=35,814)

  • Filter: ((tango <= 'xray_hotel'::timestamp without time zone) AND (seven6020oscar = 2) AND (juliet = six.november_papa))
  • Rows Removed by Filter: 74937
13. 175.820 778,284.812 ↑ 1.0 1 35,164

Limit (cost=3,872.590..3,872.600 rows=1 width=14) (actual time=22.132..22.133 rows=1 loops=35,164)

14. 949.428 778,108.992 ↑ 4.0 1 35,164

Sort (cost=3,872.590..3,872.600 rows=4 width=14) (actual time=22.128..22.128 rows=1 loops=35,164)

  • Sort Key: zulu2.tango DESC
  • Sort Method: top-N heapsort Memory: 25kB
15. 777,159.564 777,159.564 ↓ 2.5 10 35,164

Seq Scan on five_mike victor (cost=0.000..3,872.570 rows=4 width=14) (actual time=3.719..22.101 rows=10 loops=35,164)

  • Filter: ((tango <= 'xray_hotel'::timestamp without time zone) AND (seven6020oscar = 2) AND (juliet = six.november_papa))
  • Rows Removed by Filter: 74937
16. 179.070 792,957.774 ↑ 1.0 1 35,814

Limit (cost=3,872.590..3,872.600 rows=1 width=14) (actual time=22.139..22.141 rows=1 loops=35,814)

17. 966.978 792,778.704 ↑ 4.0 1 35,814

Sort (cost=3,872.590..3,872.600 rows=4 width=14) (actual time=22.136..22.136 rows=1 loops=35,814)

  • Sort Key: zulu3.tango DESC
  • Sort Method: top-N heapsort Memory: 25kB
18. 791,811.726 791,811.726 ↓ 2.5 10 35,814

Seq Scan on five_mike india (cost=0.000..3,872.570 rows=4 width=14) (actual time=4.043..22.109 rows=10 loops=35,814)

  • Filter: ((tango <= 'xray_hotel'::timestamp without time zone) AND (seven6020oscar = 2) AND (juliet = six.november_papa))
  • Rows Removed by Filter: 74937
19. 175.820 778,952.928 ↑ 1.0 1 35,164

Limit (cost=3,872.590..3,872.600 rows=1 width=14) (actual time=22.150..22.152 rows=1 loops=35,164)

20. 949.428 778,777.108 ↑ 4.0 1 35,164

Sort (cost=3,872.590..3,872.600 rows=4 width=14) (actual time=22.147..22.147 rows=1 loops=35,164)

  • Sort Key: zulu4.tango DESC
  • Sort Method: top-N heapsort Memory: 25kB
21. 777,827.680 777,827.680 ↓ 2.5 10 35,164

Seq Scan on five_mike delta_hotel (cost=0.000..3,872.570 rows=4 width=14) (actual time=3.711..22.120 rows=10 loops=35,164)

  • Filter: ((tango <= 'xray_hotel'::timestamp without time zone) AND (seven6020oscar = 2) AND (juliet = six.november_papa))
  • Rows Removed by Filter: 74937