explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aE8k

Settings
# exclusive inclusive rows x rows loops node
1. 200.418 11,349.893 ↑ 1.9 4,169 1

Unique (cost=601,481.34..601,795.46 rows=7,853 width=389) (actual time=9,992.887..11,349.893 rows=4,169 loops=1)

2. 3,023.402 11,149.475 ↓ 29.0 227,624 1

Sort (cost=601,481.34..601,500.97 rows=7,853 width=389) (actual time=9,992.885..11,149.475 rows=227,624 loops=1)

  • Sort Key: dt.observations, dt.created, dt.pos, dt.doc_number, dlt.cant, wl.name, wl._type, av.value_string, pro.product_client_id, pro.name, wl_source.name, ((((xue.rate * (dlt.cant)::double precision))::text || (xu.uomsymbol)::text)), wl_source._type, (((lpad((ir.pos)::text, 4, '0'::text) || '-'::text) || lpad((ir.doc_number)::text, 8, '0'::text))), d_parent.doc_number
  • Sort Method: external merge Disk: 46272kB
3. 809.911 8,126.073 ↓ 29.0 227,624 1

Hash Join (cost=399,989.94..600,973.28 rows=7,853 width=389) (actual time=2,863.166..8,126.073 rows=227,624 loops=1)

  • Hash Cond: (pro.presentation_uom_id = xu.uom_id)
4. 56.388 7,316.141 ↓ 70.1 227,624 1

Nested Loop Left Join (cost=399,988.74..600,740.77 rows=3,249 width=162) (actual time=2,863.105..7,316.141 rows=227,624 loops=1)

5. 196.314 7,032.129 ↓ 70.1 227,624 1

Nested Loop Left Join (cost=399,988.45..598,885.68 rows=3,249 width=157) (actual time=2,863.102..7,032.129 rows=227,624 loops=1)

6. 214.239 6,380.567 ↓ 70.1 227,624 1

Nested Loop Left Join (cost=399,988.16..597,860.21 rows=3,249 width=144) (actual time=2,863.094..6,380.567 rows=227,624 loops=1)

7. 105.291 5,028.388 ↓ 70.0 227,588 1

Hash Join (cost=399,987.72..589,188.49 rows=3,249 width=137) (actual time=2,863.073..5,028.388 rows=227,588 loops=1)

  • Hash Cond: (pi.product_id = pro.product_id)
8. 950.396 4,922.761 ↑ 3.6 227,588 1

Hash Join (cost=399,954.57..586,027.66 rows=826,469 width=90) (actual time=2,862.729..4,922.761 rows=227,588 loops=1)

  • Hash Cond: (dl_of_doc.document_id = dt.document_id)
9. 1,112.934 1,112.934 ↓ 1.0 7,719,859 1

Seq Scan on document_line dl_of_doc (cost=0.00..148,875.82 rows=7,715,482 width=4) (actual time=0.019..1,112.934 rows=7,719,859 loops=1)

10. 2.720 2,859.431 ↑ 1.9 4,166 1

Hash (cost=399,853.91..399,853.91 rows=8,053 width=98) (actual time=2,859.431..2,859.431 rows=4,166 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 575kB
11. 14.791 2,856.711 ↑ 1.9 4,166 1

Hash Left Join (cost=205,304.06..399,853.91 rows=8,053 width=98) (actual time=2,627.509..2,856.711 rows=4,166 loops=1)

  • Hash Cond: (dl.document_id = ir.document_id)
  • Filter: ((ir.document_id IS NULL) OR ((ir.type)::text = 'INREFER'::text))
  • Rows Removed by Filter: 3723
12. 1.731 2,786.782 ↑ 14.1 7,889 1

Nested Loop Left Join (cost=201,627.45..390,610.69 rows=110,929 width=89) (actual time=2,571.588..2,786.782 rows=7,889 loops=1)

13. 97.348 2,753.495 ↑ 14.1 7,889 1

Hash Right Join (cost=201,627.01..213,092.67 rows=110,929 width=89) (actual time=2,571.540..2,753.495 rows=7,889 loops=1)

  • Hash Cond: (ihdl.product_instance_id = pi.id)
14. 93.128 93.128 ↑ 1.0 324,279 1

Seq Scan on instances_has_document_lines ihdl (cost=0.00..5,607.36 rows=326,136 width=8) (actual time=0.014..93.128 rows=324,279 loops=1)

15. 2.721 2,563.019 ↑ 16.4 4,354 1

Hash (cost=199,755.18..199,755.18 rows=71,507 width=85) (actual time=2,563.019..2,563.019 rows=4,354 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 378kB
16. 24.639 2,560.298 ↑ 16.4 4,354 1

Hash Join (cost=12,565.98..199,755.18 rows=71,507 width=85) (actual time=174.372..2,560.298 rows=4,354 loops=1)

  • Hash Cond: (dlt.product_instance_id = pi.id)
17. 22.002 2,424.244 ↑ 22.7 4,354 1

Hash Join (cost=4,141.74..186,721.54 rows=98,753 width=81) (actual time=61.945..2,424.244 rows=4,354 loops=1)

  • Hash Cond: (dlt.document_id = dt.document_id)
18. 1,181.870 2,354.133 ↑ 1.6 176,015 1

Hash Join (cost=1,393.66..181,954.10 rows=275,156 width=33) (actual time=10.127..2,354.133 rows=176,015 loops=1)

  • Hash Cond: (dlt.warehouse_location_id = wl.warehouse_location_id)
19. 1,162.598 1,162.598 ↓ 1.0 7,719,859 1

Seq Scan on document_line dlt (cost=0.00..148,875.82 rows=7,715,482 width=20) (actual time=0.014..1,162.598 rows=7,719,859 loops=1)

20. 0.338 9.665 ↓ 1.1 1,176 1

Hash (cost=1,379.69..1,379.69 rows=1,118 width=21) (actual time=9.665..9.665 rows=1,176 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
21. 9.327 9.327 ↓ 1.1 1,176 1

Seq Scan on warehouse_locations wl (cost=0.00..1,379.69 rows=1,118 width=21) (actual time=0.014..9.327 rows=1,176 loops=1)

  • Filter: ((name)::text ~~ 'F.%'::text)
  • Rows Removed by Filter: 28068
22. 9.055 48.109 ↑ 1.0 26,721 1

Hash (cost=2,410.79..2,410.79 rows=26,983 width=48) (actual time=48.109..48.109 rows=26,721 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1982kB
23. 39.054 39.054 ↑ 1.0 26,721 1

Seq Scan on document dt (cost=0.00..2,410.79 rows=26,983 width=48) (actual time=0.018..39.054 rows=26,721 loops=1)

  • Filter: ((document_id IS NULL) OR ((type)::text = 'ORDER_OF_MOVE'::text))
  • Rows Removed by Filter: 48144
24. 53.883 111.415 ↓ 1.0 211,339 1

Hash (cost=4,974.33..4,974.33 rows=210,233 width=8) (actual time=111.415..111.415 rows=211,339 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 3094kB
25. 57.532 57.532 ↓ 1.0 211,339 1

Seq Scan on product_instances pi (cost=0.00..4,974.33 rows=210,233 width=8) (actual time=0.013..57.532 rows=211,339 loops=1)

26. 31.556 31.556 ↑ 1.0 1 7,889

Index Scan using document_line_pk on document_line dl (cost=0.43..1.59 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=7,889)

  • Index Cond: (id = ihdl.document_line_id)
27. 24.416 55.138 ↑ 1.0 74,865 1

Hash (cost=2,222.83..2,222.83 rows=75,183 width=31) (actual time=55.138..55.138 rows=74,865 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 2897kB
28. 30.722 30.722 ↑ 1.0 74,865 1

Seq Scan on document ir (cost=0.00..2,222.83 rows=75,183 width=31) (actual time=0.019..30.722 rows=74,865 loops=1)

29. 0.026 0.336 ↓ 26.0 78 1

Hash (cost=33.12..33.12 rows=3 width=59) (actual time=0.336..0.336 rows=78 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
30. 0.120 0.310 ↓ 26.0 78 1

Hash Join (cost=5.49..33.12 rows=3 width=59) (actual time=0.110..0.310 rows=78 loops=1)

  • Hash Cond: ((pro.product_id = xue.product_id) AND (pro.presentation_uom_id = xue.target_uom_id))
31. 0.104 0.104 ↓ 1.0 833 1

Seq Scan on products pro (cost=0.00..19.30 rows=830 width=43) (actual time=0.007..0.104 rows=833 loops=1)

32. 0.013 0.086 ↓ 1.0 78 1

Hash (cost=4.36..4.36 rows=75 width=16) (actual time=0.085..0.086 rows=78 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
33. 0.073 0.073 ↓ 1.0 78 1

Seq Scan on uom_equivalence xue (cost=0.00..4.36 rows=75 width=16) (actual time=0.012..0.073 rows=78 loops=1)

  • Filter: (source_uom_id = 1)
  • Rows Removed by Filter: 117
34. 1,137.940 1,137.940 ↑ 1.0 1 227,588

Index Scan using attributes_values_only_entity_id_idx on attributes_values av (cost=0.43..2.66 rows=1 width=15) (actual time=0.004..0.005 rows=1 loops=227,588)

  • Index Cond: (entity_id = pi.id)
  • Filter: (((entity_class)::text = 'ProductInstance'::text) AND (attributes_type_param_id = 3))
  • Rows Removed by Filter: 3
35. 455.248 455.248 ↑ 1.0 1 227,624

Index Scan using warehouse_locations_warehouse_location_id_idx on warehouse_locations wl_source (cost=0.29..0.31 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=227,624)

  • Index Cond: (warehouse_location_id = dlt.warehouse_source_location_id)
36. 227.624 227.624 ↓ 0.0 0 227,624

Index Scan using document_id_idx on document d_parent (cost=0.29..0.56 rows=1 width=13) (actual time=0.001..0.001 rows=0 loops=227,624)

  • Index Cond: (document_id = dt.document_parent_id)
37. 0.008 0.021 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=42) (actual time=0.021..0.021 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
38. 0.013 0.013 ↑ 1.0 9 1

Seq Scan on uom xu (cost=0.00..1.09 rows=9 width=42) (actual time=0.010..0.013 rows=9 loops=1)

Planning time : 10.401 ms
Execution time : 11,362.426 ms