explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qIoS

Settings
# exclusive inclusive rows x rows loops node
1. 639.259 21,741.290 ↓ 2.4 1,400,056 1

Merge Join (cost=2,483,033.34..2,528,957.32 rows=579,215 width=165) (actual time=19,660.458..21,741.29 rows=1,400,056 loops=1)

  • Buffers: shared hit=270846 read=554480, local hit=1 read=17164, temp read=20159 written=20153
2.          

CTE z

3. 259.898 12,818.016 ↑ 118.1 30,548 1

Unique (cost=703,503.56..739,575.66 rows=3,607,210 width=13) (actual time=11,963.439..12,818.016 rows=30,548 loops=1)

  • Buffers: shared hit=270846 read=554480, local hit=1 read=615, temp read=7779 written=7773
4. 3,552.567 12,558.118 ↑ 2.6 1,400,056 1

Sort (cost=703,503.56..712,521.59 rows=3,607,210 width=13) (actual time=11,963.436..12,558.118 rows=1,400,056 loops=1)

  • Sort Key: f.source, f.formula, pp.price
  • Sort Method: external merge Disk: 25120kB
  • Buffers: shared hit=270846 read=554480, local hit=1 read=615, temp read=7779 written=7773
5. 801.935 9,005.551 ↑ 2.6 1,400,056 1

Hash Join (cost=145,923.57..248,985.17 rows=3,607,210 width=13) (actual time=8,173.886..9,005.551 rows=1,400,056 loops=1)

  • Buffers: shared hit=270846 read=554480, local hit=1 read=615, temp read=4636 written=4630
6. 30.856 30.856 ↑ 1.1 139,136 1

Seq Scan on t_roomrates t (cost=0..2,186.8 rows=157,080 width=4) (actual time=0.011..30.856 rows=139,136 loops=1)

  • Buffers: local hit=1 read=615
7. 679.686 8,172.760 ↑ 1.0 1,400,056 1

Hash (cost=120,218.87..120,218.87 rows=1,400,056 width=17) (actual time=8,172.76..8,172.76 rows=1,400,056 loops=1)

  • Buffers: shared hit=270846 read=554480, temp written=4321
8. 830.494 7,493.074 ↑ 1.0 1,400,056 1

Merge Join (cost=13,843.8..120,218.87 rows=1,400,056 width=17) (actual time=848.672..7,493.074 rows=1,400,056 loops=1)

  • Buffers: shared hit=270846 read=554480
9. 2,703.791 2,703.791 ↑ 1.0 1,400,056 1

Index Scan using q_f_2 on q_f f (cost=0.43..82,514.41 rows=1,400,056 width=16) (actual time=0.19..2,703.791 rows=1,400,056 loops=1)

  • Index Cond: ((f.day >= '2020-01-01'::date) AND (f.day <= '2020-01-31'::date))
  • Filter: (f.hotel = 14054)
  • Buffers: shared hit=12168 read=207503
10. 3,958.789 3,958.789 ↑ 1.0 765,493 1

Index Scan using roomratesplanner_index_tips_4 on roomratesplanner pp (cost=0.43..254,704.44 rows=777,705 width=13) (actual time=0.042..3,958.789 rows=765,493 loops=1)

  • Index Cond: ((pp.day >= '2020-01-01'::date) AND (pp.day <= '2020-01-31'::date))
  • Buffers: shared hit=258678 read=346977
11.          

CTE y

12. 12,828.859 12,828.859 ↑ 118.1 30,548 1

CTE Scan on z z (cost=0..982,964.72 rows=3,607,210 width=56) (actual time=11,963.453..12,828.859 rows=30,548 loops=1)

  • Buffers: shared hit=270846 read=554480, local hit=1 read=615, temp read=7779 written=7773
13. 8.873 12,852.095 ↑ 118.1 30,548 1

Sort (cost=625,296.09..634,314.12 rows=3,607,210 width=76) (actual time=12,849.756..12,852.095 rows=30,548 loops=1)

  • Sort Key: y.source, y.formula, y.price
  • Sort Method: quicksort Memory: 2200kB
  • Buffers: shared hit=270846 read=554480, local hit=1 read=615, temp read=7779 written=7773
14. 12,843.222 12,843.222 ↑ 118.1 30,548 1

CTE Scan on y y (cost=0..72,144.2 rows=3,607,210 width=76) (actual time=11,963.458..12,843.222 rows=30,548 loops=1)

  • Buffers: shared hit=270846 read=554480, local hit=1 read=615, temp read=7779 written=7773
15. 215.986 8,249.936 ↓ 2.4 1,400,056 1

Materialize (cost=135,196.86..138,092.93 rows=579,215 width=201) (actual time=6,804.627..8,249.936 rows=1,400,056 loops=1)

  • Buffers: local read=16549, temp read=12380 written=12380
16. 7,221.228 8,033.950 ↓ 2.4 1,400,056 1

Sort (cost=135,196.86..136,644.89 rows=579,215 width=201) (actual time=6,804.619..8,033.95 rows=1,400,056 loops=1)

  • Sort Key: x.source, x.formula, x.price
  • Sort Method: external merge Disk: 99016kB
  • Buffers: local read=16549, temp read=12380 written=12380
17. 812.722 812.722 ↓ 2.4 1,400,056 1

Seq Scan on x x (cost=0..22,341.15 rows=579,215 width=201) (actual time=0.037..812.722 rows=1,400,056 loops=1)

  • Buffers: local read=16549
Planning time : 1.123 ms
Execution time : 21,850.285 ms