explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Owq5 : Optimization for: plan #Qrml

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.007 146,600.422 ↑ 1.0 50 1

Limit (cost=15,446,904.81..15,446,904.94 rows=50 width=752) (actual time=146,600.408..146,600.422 rows=50 loops=1)

2. 9.582 146,600.415 ↑ 9.0 50 1

Sort (cost=15,446,904.81..15,446,905.94 rows=450 width=752) (actual time=146,600.404..146,600.415 rows=50 loops=1)

  • Sort Key: d.received_date DESC, edi.document_id
  • Sort Method: top-N heapsort Memory: 46kB
3. 4.666 146,590.833 ↓ 15.8 7,088 1

Nested Loop Left Join (cost=4,649.04..15,446,889.86 rows=450 width=752) (actual time=21,101.380..146,590.833 rows=7,088 loops=1)

  • Join Filter: (ln.parent_document_id = edi.document_id)
4. 497.539 146,586.167 ↓ 15.8 7,088 1

Hash Join (cost=4,649.04..15,445,121.61 rows=450 width=478) (actual time=21,101.354..146,586.167 rows=7,088 loops=1)

  • Hash Cond: (edi.document_id = d.id)
5. 145,205.195 145,944.658 ↓ 1.9 747,030 1

Subquery Scan on edi (cost=0.00..15,438,987.86 rows=394,726 width=396) (actual time=6.867..145,944.658 rows=747,030 loops=1)

  • Filter: (SubPlan 1)
  • Rows Removed by Filter: 37594
  • -> Index Only Scan using ls_user_to_document_invisibility_user_id_document_id_key on ls_user_to_document_visibility usertodocu1_ (cost=0.57..38.32 rows=38 width=8) (actual time
  • Index Cond: ((user_id = 24) AND (document_id = edi.document_id))
  • Heap Fetches: 747030
6. 91.460 739.463 ↑ 1.0 784,624 1

Append (cost=0.00..36,994.04 rows=789,452 width=404) (actual time=0.598..739.463 rows=784,624 loops=1)

7. 90.024 90.024 ↑ 1.0 88,455 1

Seq Scan on ls_document_edi_desadv e (cost=0.00..3,830.93 rows=89,693 width=230) (actual time=0.597..90.024 rows=88,455 loops=1)

8. 164.774 164.774 ↑ 1.0 231,516 1

Seq Scan on ls_document_edi_order e_1 (cost=0.00..6,676.11 rows=232,311 width=164) (actual time=0.719..164.774 rows=231,516 loops=1)

9. 118.431 118.431 ↑ 1.0 115,120 1

Seq Scan on ls_document_edi_ordrsp e_2 (cost=0.00..5,487.21 rows=116,021 width=316) (actual time=0.547..118.431 rows=115,120 loops=1)

10. 274.733 274.733 ↑ 1.0 349,533 1

Seq Scan on ls_document_edi_recadv e_3 (cost=0.00..13,023.67 rows=350,267 width=166) (actual time=0.586..274.733 rows=349,533 loops=1)

11. 0.024 0.024 ↓ 0.0 0 1

Seq Scan on ls_document_edi_invoice e_4 (cost=0.00..11.80 rows=180 width=404) (actual time=0.024..0.024 rows=0 loops=1)

12. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on ls_document_edi_insdes e_5 (cost=0.00..11.80 rows=180 width=404) (actual time=0.002..0.002 rows=0 loops=1)

13. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on ls_document_edi_invrpt e_6 (cost=0.00..11.80 rows=180 width=404) (actual time=0.002..0.002 rows=0 loops=1)

14. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on ls_document_edi_slsrpt e_7 (cost=0.00..12.10 rows=210 width=404) (actual time=0.002..0.002 rows=0 loops=1)

15. 0.001 0.006 ↓ 0.0 0 1

Subquery Scan on *SELECT* 9 (cost=0.00..17.40 rows=370 width=404) (actual time=0.006..0.006 rows=0 loops=1)

16. 0.005 0.005 ↓ 0.0 0 1

Seq Scan on ls_document_edi_aperak e_8 (cost=0.00..13.70 rows=370 width=404) (actual time=0.005..0.005 rows=0 loops=1)

  • Filter: ((base_document_number IS NOT NULL) OR (base_document_date IS NOT NULL))
17. 0.000 0.003 ↓ 0.0 0 1

Subquery Scan on *SELECT* 10 (cost=0.00..10.40 rows=20 width=404) (actual time=0.003..0.003 rows=0 loops=1)

18. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on ls_document_edi_deljit e_9 (cost=0.00..10.20 rows=20 width=3,308) (actual time=0.003..0.003 rows=0 loops=1)

19. 0.000 0.002 ↓ 0.0 0 1

Subquery Scan on *SELECT* 11 (cost=0.00..10.40 rows=20 width=404) (actual time=0.002..0.002 rows=0 loops=1)

20. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on ls_document_edi_iftmbf e_10 (cost=0.00..10.20 rows=20 width=3,308) (actual time=0.002..0.002 rows=0 loops=1)

21. 23.730 143.970 ↓ 4.5 50,598 1

Hash (cost=4,509.00..4,509.00 rows=11,203 width=90) (actual time=143.970..143.970 rows=50,598 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 6698kB
22. 120.240 120.240 ↓ 4.5 50,598 1

Index Scan using idx_ls_document_dd on ls_document d (cost=0.43..4,509.00 rows=11,203 width=90) (actual time=48.789..120.240 rows=50,598 loops=1)

  • Index Cond: ((received_date <= '2019-11-28 23:59:58'::timestamp without time zone) AND (received_date >= '2019-11-28 00:00:00'::timestamp without time zone))
  • Filter: (trash_timestamp IS NULL)
23. 0.000 0.000 ↓ 0.0 0 7,088

Materialize (cost=0.00..13.90 rows=260 width=274) (actual time=0.000..0.000 rows=0 loops=7,088)

24. 0.006 0.006 ↓ 0.0 0 1

Seq Scan on ls_last_notification_for_document ln (cost=0.00..12.60 rows=260 width=274) (actual time=0.006..0.006 rows=0 loops=1)

Planning time : 63.792 ms
Execution time : 146,602.202 ms