explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9rev

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 628.690 ↓ 0.0 0 1

Result (cost=1,417.04..1,428.85 rows=45 width=12) (actual time=628.689..628.690 rows=0 loops=1)

2.          

CTE ids

3. 0.003 0.758 ↑ 1.0 1 1

Aggregate (cost=41.87..41.88 rows=1 width=8) (actual time=0.758..0.758 rows=1 loops=1)

4. 0.003 0.755 ↑ 1.0 2 1

Unique (cost=41.84..41.85 rows=2 width=8) (actual time=0.753..0.755 rows=2 loops=1)

5. 0.014 0.752 ↑ 1.0 2 1

Sort (cost=41.84..41.84 rows=2 width=8) (actual time=0.751..0.752 rows=2 loops=1)

  • Sort Key: (COALESCE(max(qiss_devicemappingconfigurationinvalidmessages.qactivemessageid), '0'::bigint))
  • Sort Method: quicksort Memory: 25kB
6. 0.003 0.738 ↑ 1.0 2 1

Append (cost=2.96..41.83 rows=2 width=8) (actual time=0.073..0.738 rows=2 loops=1)

7. 0.036 0.071 ↑ 1.0 1 1

Aggregate (cost=2.96..2.97 rows=1 width=8) (actual time=0.071..0.071 rows=1 loops=1)

8. 0.035 0.035 ↓ 1.1 85 1

Seq Scan on qiss_devicemappingconfigurationinvalidmessages (cost=0.00..2.77 rows=77 width=8) (actual time=0.021..0.035 rows=85 loops=1)

9. 0.410 0.664 ↑ 1.0 1 1

Aggregate (cost=38.83..38.84 rows=1 width=8) (actual time=0.663..0.664 rows=1 loops=1)

10. 0.254 0.254 ↑ 1.0 1,106 1

Seq Scan on qiss_deviceactualmappingconfigurationhistory (cost=0.00..36.06 rows=1,106 width=8) (actual time=0.007..0.254 rows=1,106 loops=1)

11.          

CTE step1

12. 0.000 628.671 ↓ 0.0 0 1

Hash Anti Join (cost=1,357.95..1,372.12 rows=90 width=507) (actual time=628.671..628.671 rows=0 loops=1)

  • Hash Cond: (t.qactivemessageid = tbl_deviceconfigurationbatchrecord.faildrecordid)
13.          

Initplan (forHash Anti Join)

14. 0.763 0.763 ↑ 1.0 1 1

CTE Scan on ids (cost=0.00..0.02 rows=1 width=8) (actual time=0.761..0.763 rows=1 loops=1)

15. 0.978 628.670 ↓ 0.0 0 1

Subquery Scan on t (cost=1,326.10..1,334.19 rows=180 width=507) (actual time=628.669..628.670 rows=0 loops=1)

  • Filter: (t.qactivemessageid > $1)
  • Rows Removed by Filter: 1000
16. 0.272 627.692 ↓ 1.9 1,000 1

Limit (cost=1,326.10..1,327.45 rows=539 width=507) (actual time=627.168..627.692 rows=1,000 loops=1)

17. 4.605 627.420 ↓ 1.9 1,000 1

Sort (cost=1,326.10..1,327.45 rows=539 width=507) (actual time=627.166..627.420 rows=1,000 loops=1)

  • Sort Key: tbl_qactivemessages.qactivemessageid
  • Sort Method: top-N heapsort Memory: 1107kB
18. 622.815 622.815 ↓ 7.8 4,196 1

Seq Scan on tbl_qactivemessages (cost=0.00..1,301.65 rows=539 width=507) (actual time=0.231..622.815 rows=4,196 loops=1)

  • Filter: (((unit)::text !~~* '%Unknown%'::text) AND (qgatemessage ~~* '%size="2"%'::text) AND (qgatemessage ~~* '%Field nr="36"%'::text) AND (qgatemessage ~~* '%Field nr="41"%'::text) AND ((messagetype)::text = 'FORM'::text) AND (messagenumber = 6))
  • Rows Removed by Filter: 8336
19. 0.000 0.000 ↓ 0.0 0

Hash (cost=19.70..19.70 rows=970 width=8) (never executed)

20. 0.000 0.000 ↓ 0.0 0

Seq Scan on tbl_deviceconfigurationbatchrecord (cost=0.00..19.70 rows=970 width=8) (never executed)

21. 0.015 628.688 ↓ 0.0 0 1

Sort (cost=3.04..3.15 rows=45 width=40) (actual time=628.688..628.688 rows=0 loops=1)

  • Sort Key: step1.qactivemessageid
  • Sort Method: quicksort Memory: 25kB
22. 628.673 628.673 ↓ 0.0 0 1

CTE Scan on step1 (cost=0.00..1.80 rows=45 width=40) (actual time=628.672..628.673 rows=0 loops=1)

  • Filter: formation
Planning time : 17.115 ms
Execution time : 629.088 ms