explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MuV2 : Optimization for: plan #KvKn

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.480 949.930 ↓ 0.0 0 1

Hash Join (cost=60,657.970..80,612.690 rows=1 width=37) (actual time=949.930..949.930 rows=0 loops=1)

  • Buffers: shared hit=341736 read=28034
2. 52.097 244.978 ↓ 25.2 4,078 1

Hash Join (cost=9,872.680..29,826.780 rows=162 width=114) (actual time=132.572..244.978 rows=4,078 loops=1)

  • Buffers: shared hit=7869 read=9398
3. 98.973 98.973 ↑ 1.0 420,268 1

Seq Scan on uniform_alpha uniform_alpha (cost=0.000..18,835.850 rows=425,885 width=76) (actual time=0.007..98.973 rows=420,268 loops=1)

  • Buffers: shared hit=5179 read=9398
4. 0.070 93.908 ↓ 47.5 285 1

Hash (cost=9,872.610..9,872.610 rows=6 width=38) (actual time=93.908..93.908 rows=285 loops=1)

  • Buffers: shared hit=2690
5. 0.126 93.838 ↓ 47.5 285 1

Unique (cost=9,867.920..9,872.550 rows=6 width=38) (actual time=88.307..93.838 rows=285 loops=1)

  • Buffers: shared hit=2690
6. 0.000 93.712 ↓ 188.7 1,132 1

Gather Merge (cost=9,867.920..9,872.530 rows=6 width=38) (actual time=88.306..93.712 rows=1,132 loops=1)

  • Buffers: shared hit=5084
7. 7.818 180.940 ↓ 141.5 566 2

Merge Join (cost=8,867.910..8,871.850 rows=4 width=38) (actual time=85.439..90.470 rows=566 loops=2)

  • Buffers: shared hit=5084
8. 78.084 146.242 ↓ 24.8 14,878 2

Sort (cost=7,081.140..7,082.640 rows=599 width=38) (actual time=71.925..73.121 rows=14,878 loops=2)

  • Sort Key: uniform_yankee.zulu
  • Sort Method: quicksort Memory: 1581kB
  • Buffers: shared hit=2866
9. 68.158 68.158 ↓ 25.0 14,994 2

Seq Scan on uniform_yankee uniform_yankee (cost=0.000..7,053.510 rows=599 width=38) (actual time=23.738..34.079 rows=14,994 loops=2)

  • Filter: (((papa(((uniform_yankee."india_three" / 1000))::double precision))::date >= 'delta_juliet'::date) AND ((papa(((uniform_yankee."india_three" / 1000))::double precision))::date <= 'delta_two'::date))
  • Buffers: shared hit=2861
10. 5.028 26.880 ↓ 2.8 805 2

Sort (cost=1,786.760..1,787.480 rows=288 width=39) (actual time=13.371..13.440 rows=805 loops=2)

  • Sort Key: "five_hotel".zulu
  • Sort Method: quicksort Memory: 87kB
  • Buffers: shared hit=2218
11. 21.852 21.852 ↓ 2.8 805 2

Seq Scan on charlie_lima charlie_lima (cost=0.000..1,775.000 rows=288 width=39) (actual time=0.080..10.926 rows=805 loops=2)

  • Filter: (("five_hotel".seven five_romeo NOT NULL) AND (("five_hotel".romeo)::text = 'sierra'::text))
  • Buffers: shared hit=2218
12. 0.010 704.472 ↑ 5.4 9 1

Hash (cost=50,784.680..50,784.680 rows=49 width=78) (actual time=704.472..704.472 rows=9 loops=1)

  • Buffers: shared hit=333867 read=18636
13. 0.785 704.462 ↑ 5.4 9 1

Merge Join (cost=50,754.000..50,784.680 rows=49 width=78) (actual time=697.473..704.462 rows=9 loops=1)

  • Buffers: shared hit=333867 read=18636
14. 4.074 204.784 ↓ 1.5 2,530 1

Unique (cost=22,499.110..22,507.810 rows=1,650 width=39) (actual time=197.676..204.784 rows=2,530 loops=1)

  • Buffers: shared hit=5211 read=9366
15. 56.746 200.710 ↓ 18.3 31,789 1

Sort (cost=22,499.110..22,503.460 rows=1,740 width=39) (actual time=197.675..200.710 rows=31,789 loops=1)

  • Sort Key: india_india1.zulu
  • Sort Method: quicksort Memory: 3265kB
  • Buffers: shared hit=5211 read=9366
16. 4.620 143.964 ↓ 18.4 31,953 1

Gather (cost=1,000.000..22,405.450 rows=1,740 width=39) (actual time=0.265..143.964 rows=31,953 loops=1)

  • Buffers: shared hit=5211 read=9366
17. 139.344 139.344 ↓ 14.7 10,651 3

Seq Scan on uniform_alpha four (cost=0.000..21,231.450 rows=725 width=39) (actual time=0.763..139.344 rows=10,651 loops=3)

  • Filter: (((india_india1.xray)::text = 'whiskey'::text) AND ((papa(((india_india1."india_three" / 1000))::double precision))::date >= 'delta_juliet'::date) AND ((papa(((india_india1."india_three" / 1000))::double precision))::date <= 'delta_two'::date))
  • Buffers: shared hit=5211 read=9366
18. 0.115 498.893 ↑ 2.5 20 1

Unique (cost=28,254.900..28,255.140 rows=49 width=39) (actual time=498.713..498.893 rows=20 loops=1)

  • Buffers: shared hit=328656 read=9270
19. 0.452 498.778 ↓ 21.2 1,037 1

Sort (cost=28,254.900..28,255.020 rows=49 width=39) (actual time=498.712..498.778 rows=1,037 loops=1)

  • Sort Key: india_india2.zulu
  • Sort Method: quicksort Memory: 130kB
  • Buffers: shared hit=328656 read=9270
20. 5.679 498.326 ↓ 21.2 1,037 1

Gather (cost=1,000.420..28,253.520 rows=49 width=39) (actual time=58.944..498.326 rows=1,037 loops=1)

  • Buffers: shared hit=328656 read=9270
21. 345.083 492.647 ↓ 17.3 346 3

Nested Loop (cost=0.420..27,248.620 rows=20 width=39) (actual time=56.586..492.647 rows=346 loops=3)

  • Buffers: shared hit=328656 read=9270
22. 147.552 147.552 ↓ 30.2 26,776 3

Seq Scan on uniform_alpha five_three (cost=0.000..20,787.820 rows=887 width=76) (actual time=1.050..147.552 rows=26,776 loops=3)

  • Filter: (((papa(((india_india2."india_three" / 1000))::double precision))::date >= 'delta_juliet'::date) AND ((papa(((india_india2."india_three" / 1000))::double precision))::date <= 'delta_two'::date))
  • Buffers: shared hit=5307 read=9270
23. 0.012 0.012 ↓ 0.0 0 80,327

Index Scan using bravo on lima lima (cost=0.420..7.270 rows=1 width=37) (actual time=0.012..0.012 rows=0 loops=80,327)

  • Index Cond: ((lima.mike)::text = (india_india2.mike)::text)
  • Filter: ((lima.six)::text = 'charlie_quebec'::text)
  • Buffers: shared hit=323349