explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8gzQ

Settings
# exclusive inclusive rows x rows loops node
1. 61.745 394.415 ↓ 1.1 25,107 1
→

Hash Anti Join (cost=6,003.53..6,804.65 rows=23,617 width=206) (actual time=302.514..394.415 rows=25,107 loops=1)

  • Hash Cond: ((l."Id")::text = (ps2."ServiceLineId")::text)
  • Join Filter: ((ps2."Date" <= tp."Date") AND (ps2."Date" > ps."Date"))
  • Rows Removed by Join Filter: 1619
2.          

CTE with_line_dates
3. 51.737 117.034 ↓ 1.0 25,250 1
→

HashAggregate (cost=2,362.99..2,614.31 rows=25,132 width=102) (actual time=88.800..117.034 rows=25,250 loops=1)

  • Group Key: l_1."Id", (LEAST((l_1."From")::date, (l_1."CreatedOn")::date))
4. 25.107 65.297 ↓ 1.0 25,457 1
→

Append (cost=0.00..2,237.33 rows=25,132 width=102) (actual time=0.020..65.297 rows=25,457 loops=1)

5. 36.400 36.400 ↑ 1.0 24,720 1
→

Seq Scan on "InvoiceServiceLine" l_1 (cost=0.00..1,902.80 rows=24,720 width=39) (actual time=0.019..36.400 rows=24,720 loops=1)

6. 2.054 3.790 ↓ 1.8 737 1
→

HashAggregate (cost=78.06..83.21 rows=412 width=39) (actual time=3.062..3.790 rows=737 loops=1)

  • Group Key: t."ServiceLineId", (t."Date")::date
7. 1.736 1.736 ↑ 1.0 1,375 1
→

Seq Scan on "InvoiceTransaction" t (cost=0.00..71.19 rows=1,375 width=39) (actual time=0.014..1.736 rows=1,375 loops=1)

  • Filter: (NOT "Archived")
8.          

CTE with_line_statistics
9. 3.798 5.061 ↓ 1.8 737 1
→

HashAggregate (cost=119.31..128.58 rows=412 width=167) (actual time=4.069..5.061 rows=737 loops=1)

  • Group Key: tp_1."ServiceLineId", (tp_1."Date")::date
10. 1.263 1.263 ↑ 1.0 1,375 1
→

Seq Scan on "InvoiceTransaction" tp_1 (cost=0.00..71.19 rows=1,375 width=52) (actual time=0.018..1.263 rows=1,375 loops=1)

11. 29.735 329.998 ↓ 1.0 25,383 1
→

Hash Right Join (cost=3,247.25..3,262.24 rows=25,006 width=210) (actual time=299.794..329.998 rows=25,383 loops=1)

  • Hash Cond: ((ps."ServiceLineId")::text = (l."Id")::text)
  • Join Filter: (ps."Date" <= tp."Date")
  • Rows Removed by Join Filter: 398
12. 4.567 4.567 ↓ 1.8 737 1
→

CTE Scan on with_line_statistics ps (cost=0.00..8.24 rows=412 width=230) (actual time=4.074..4.567 rows=737 loops=1)

13. 27.950 295.696 ↓ 1.0 25,107 1
→

Hash (cost=2,934.67..2,934.67 rows=25,006 width=78) (actual time=295.695..295.696 rows=25,107 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 3004kB
14. 64.083 267.746 ↓ 1.0 25,107 1
→

Hash Join (cost=2,088.20..2,934.67 rows=25,006 width=78) (actual time=133.860..267.746 rows=25,107 loops=1)

  • Hash Cond: ((tp."ServiceLineId")::text = (l."Id")::text)
15. 158.642 158.642 ↓ 1.0 25,250 1
→

CTE Scan on with_line_dates tp (cost=0.00..502.64 rows=25,006 width=102) (actual time=88.806..158.642 rows=25,250 loops=1)

  • Filter: ("Date" IS NOT NULL)
16. 23.041 45.021 ↑ 1.0 24,720 1
→

Hash (cost=1,779.20..1,779.20 rows=24,720 width=74) (actual time=45.020..45.021 rows=24,720 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2820kB
17. 21.980 21.980 ↑ 1.0 24,720 1
→

Seq Scan on "InvoiceServiceLine" l (cost=0.00..1,779.20 rows=24,720 width=74) (actual time=0.016..21.980 rows=24,720 loops=1)

18. 0.543 2.672 ↓ 1.4 594 1
→

Hash (cost=8.24..8.24 rows=412 width=102) (actual time=2.672..2.672 rows=594 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 50kB
19. 2.129 2.129 ↓ 1.8 737 1
→

CTE Scan on with_line_statistics ps2 (cost=0.00..8.24 rows=412 width=102) (actual time=0.002..2.129 rows=737 loops=1)