explain.depesz.com

PostgreSQL's explain analyze made readable

Result: glV

Settings
# exclusive inclusive rows x rows loops node
1. 113.674 28,365.312 ↓ 24.5 32,749 1

Unique (cost=97,590,665.50..97,590,698.85 rows=1,334 width=96) (actual time=27,304.118..28,365.312 rows=32,749 loops=1)

  • Buffers: shared hit=2999812 read=301811, temp read=63937 written=63440
2. 6,704.755 28,251.638 ↓ 188.2 251,104 1

Sort (cost=97,590,665.50..97,590,668.84 rows=1,334 width=96) (actual time=27,304.115..28,251.638 rows=251,104 loops=1)

  • Sort Key: ii.registration_number, ib.num, ib.invoice_type, ii.input_date, c.tin, ib.turnover_date, ii.invoice_status, ii.last_update_date, ps.total_price_with_tax
  • Sort Method: external merge Disk: 27632kB
  • Buffers: shared hit=2999812 read=301811, temp read=63937 written=63440
3. 689.396 21,546.883 ↓ 188.2 251,104 1

Hash Join (cost=546,811.67..97,590,596.26 rows=1,334 width=96) (actual time=7,993.286..21,546.883 rows=251,104 loops=1)

  • Hash Cond: (ps.invoice_body_id = c.invoice_body_id)
  • Buffers: shared hit=2999812 read=301811, temp read=60483 written=59977
4. 646.810 18,846.512 ↓ 188.1 250,978 1

Hash Join (cost=384,388.94..97,407,423.67 rows=1,334 width=91) (actual time=5,255.128..18,846.512 rows=250,978 loops=1)

  • Hash Cond: (ib.invoice_body_id = ps.invoice_body_id)
  • Buffers: shared hit=2998261 read=239228, temp read=45007 written=44627
5. 796.363 16,670.455 ↓ 188.1 250,978 1

Hash Join (cost=267,420.47..97,276,860.52 rows=1,334 width=83) (actual time=3,582.880..16,670.455 rows=250,978 loops=1)

  • Hash Cond: (ii.invoice_info_id = ib.invoice_info_id)
  • Buffers: shared hit=2997449 read=214563, temp read=29785 written=29531
6. 2,647.348 12,872.236 ↓ 188.1 250,978 1

Seq Scan on invoice_info ii (cost=0.00..96,982,319.37 rows=1,334 width=59) (actual time=570.321..12,872.236 rows=250,978 loops=1)

  • Filter: ((input_date >= to_date('10-12-2018'::text, 'dd.mm.yyyy'::text)) AND (input_date <= to_date('11-12-2018'::text, 'dd.mm.yyyy'::text)) AND (last_update_date = (SubPlan 1)))
  • Rows Removed by Filter: 3213754
  • Buffers: shared hit=2994798 read=54819
7.          

SubPlan (forSeq Scan)

8. 538.152 10,224.888 ↑ 1.0 1 269,076

Aggregate (cost=27.94..27.95 rows=1 width=4) (actual time=0.038..0.038 rows=1 loops=269,076)

  • Buffers: shared hit=2986595 read=193
9. 9,686.736 9,686.736 ↓ 1.2 7 269,076

Index Scan using invoice_info_regnum_idx on invoice_info if (cost=0.56..27.93 rows=6 width=4) (actual time=0.033..0.036 rows=7 loops=269,076)

  • Index Cond: ((registration_number)::text = (ii.registration_number)::text)
  • Buffers: shared hit=2986595 read=193
10. 1,287.432 3,001.856 ↑ 1.0 3,464,732 1

Hash (cost=197,042.32..197,042.32 rows=3,464,732 width=40) (actual time=3,001.856..3,001.856 rows=3,464,732 loops=1)

  • Buckets: 65536 Batches: 128 Memory Usage: 2523kB
  • Buffers: shared hit=2651 read=159744, temp written=26791
11. 1,714.424 1,714.424 ↑ 1.0 3,464,732 1

Seq Scan on invoice_body ib (cost=0.00..197,042.32 rows=3,464,732 width=40) (actual time=0.012..1,714.424 rows=3,464,732 loops=1)

  • Buffers: shared hit=2651 read=159744
12. 816.175 1,529.247 ↑ 1.0 3,464,732 1

Hash (cost=60,124.32..60,124.32 rows=3,464,732 width=8) (actual time=1,529.247..1,529.247 rows=3,464,732 loops=1)

  • Buckets: 131072 Batches: 64 Memory Usage: 3137kB
  • Buffers: shared hit=812 read=24665, temp written=11622
13. 713.072 713.072 ↑ 1.0 3,464,732 1

Seq Scan on product_set ps (cost=0.00..60,124.32 rows=3,464,732 width=8) (actual time=0.035..713.072 rows=3,464,732 loops=1)

  • Buffers: shared hit=812 read=24665
14. 913.843 2,010.975 ↑ 1.0 3,465,810 1

Hash (cost=98,792.10..98,792.10 rows=3,465,810 width=17) (actual time=2,010.975..2,010.975 rows=3,465,810 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 2633kB
  • Buffers: shared hit=1551 read=62583, temp written=11641
15. 1,097.132 1,097.132 ↑ 1.0 3,465,810 1

Seq Scan on customers c (cost=0.00..98,792.10 rows=3,465,810 width=17) (actual time=0.043..1,097.132 rows=3,465,810 loops=1)

  • Buffers: shared hit=1551 read=62583