explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rxld

Settings
# exclusive inclusive rows x rows loops node
1. 71.756 16,316.831 ↑ 1.0 1 1

Aggregate (cost=2,533,173.08..2,533,173.09 rows=1 width=8) (actual time=16,316.831..16,316.831 rows=1 loops=1)

  • Buffers: shared hit=1713672 read=77861, temp read=10505 written=29766
2.          

CTE result

3. 69.794 8,595.456 ↓ 1.3 650,388 1

Append (cost=0.56..2,323,291.70 rows=519,320 width=48) (actual time=0.089..8,595.456 rows=650,388 loops=1)

  • Buffers: shared hit=1713672 read=77861
4. 8,525.662 8,525.662 ↓ 1.3 650,388 1

Index Scan using reizen_2018101 on distransacties_reizen_201810 r (cost=0.56..2,320,695.10 rows=519,320 width=48) (actual time=0.087..8,525.662 rows=650,388 loops=1)

  • Index Cond: ((msgreportdate >= '2018-10-01 08:00:00'::timestamp without time zone) AND (msgreportdate <= '2018-10-02 10:59:59'::timestamp without time zone))
  • Filter: ((siteid IS NOT NULL) AND (modaltype = ANY ('{2,3}'::integer[])) AND ((COALESCE(vehicleid, 3400) / 100) <> 34) AND (((transactionstatus = 1) AND (transactiontype = 30)) OR (transactiontype = 31) OR ((transactionstatus = 1) AND (transactiontype = 32)) OR (transactiontype = 33)))
  • Rows Removed by Filter: 1166870
  • Buffers: shared hit=1713672 read=77861
5.          

CTE resultaat

6. 2,352.707 13,728.521 ↓ 1.3 650,388 1

WindowAgg (cost=164,440.57..195,599.77 rows=519,320 width=108) (actual time=11,160.546..13,728.521 rows=650,388 loops=1)

  • Buffers: shared hit=1713672 read=77861, temp read=10505 written=15277
7. 572.264 11,375.814 ↓ 1.3 650,388 1

Sort (cost=164,440.57..165,738.87 rows=519,320 width=68) (actual time=11,160.475..11,375.814 rows=650,388 loops=1)

  • Sort Key: r_1.mediaserialnumberid, r_1.applicationtransactionsequencenumber, r_1.transactionstatus DESC
  • Sort Method: external merge Disk: 49656kB
  • Buffers: shared hit=1713672 read=77861, temp read=10505 written=15277
8. 955.517 10,803.550 ↓ 1.3 650,388 1

WindowAgg (cost=75,663.63..93,839.83 rows=519,320 width=68) (actual time=9,710.525..10,803.550 rows=650,388 loops=1)

  • Buffers: shared hit=1713672 read=77861, temp read=4298 written=9065
9. 699.267 9,848.033 ↓ 1.3 650,388 1

Sort (cost=75,663.63..76,961.93 rows=519,320 width=44) (actual time=9,710.500..9,848.033 rows=650,388 loops=1)

  • Sort Key: r_1.mediaserialnumberid, r_1.applicationtransactionsequencenumber, r_1.msgreportdate
  • Sort Method: external merge Disk: 34384kB
  • Buffers: shared hit=1713672 read=77861, temp read=4298 written=9065
10. 9,148.766 9,148.766 ↓ 1.3 650,388 1

CTE Scan on result r_1 (cost=0.00..10,386.40 rows=519,320 width=44) (actual time=0.097..9,148.766 rows=650,388 loops=1)

  • Buffers: shared hit=1713672 read=77861, temp written=4764
11.          

CTE alle_ci_co

12. 14,697.325 14,697.325 ↓ 318,194.0 318,194 1

CTE Scan on resultaat a (cost=0.00..14,281.32 rows=1 width=48) (actual time=11,160.558..14,697.325 rows=318,194 loops=1)

  • Filter: ((transactiontype = ANY ('{30,32}'::integer[])) AND (volgende_transactiontype = ANY ('{31,33}'::integer[])) AND (modaltype = volgende_modaltype))
  • Rows Removed by Filter: 332194
  • Buffers: shared hit=1713672 read=77861, temp read=10505 written=25280
13.          

CTE uiteindelijk

14. 16,002.734 16,002.734 ↓ 315,617.0 315,617 1

CTE Scan on alle_ci_co (cost=0.00..0.28 rows=1 width=44) (actual time=11,160.600..16,002.734 rows=315,617 loops=1)

  • Filter: ((siteid <> volgende_siteid) AND (ci_tijdstip < co_tijdstip) AND (vehicleid = volgende_vehicleid) AND (abs(tck.datediff_in_minutes(ci_tijdstip, co_tijdstip)) <= 180))
  • Rows Removed by Filter: 2577
  • Buffers: shared hit=1713672 read=77861, temp read=10505 written=27532
15. 16,245.075 16,245.075 ↓ 315,617.0 315,617 1

CTE Scan on uiteindelijk (cost=0.00..0.02 rows=1 width=0) (actual time=11,160.603..16,245.075 rows=315,617 loops=1)

  • Buffers: shared hit=1713672 read=77861, temp read=10505 written=29766