explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ctqC

Settings
# exclusive inclusive rows x rows loops node
1. 3.388 3,800.551 ↓ 54.1 4,169 1

Unique (cost=236,145.10..236,148.18 rows=77 width=389) (actual time=3,796.567..3,800.551 rows=4,169 loops=1)

2. 22.109 3,797.163 ↓ 54.1 4,169 1

Sort (cost=236,145.10..236,145.29 rows=77 width=389) (actual time=3,796.565..3,797.163 rows=4,169 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: quicksort Memory: 1300kB
3. 15.089 3,775.054 ↓ 54.1 4,169 1

Hash Join (cost=205,043.06..236,142.69 rows=77 width=389) (actual time=2,920.052..3,775.054 rows=4,169 loops=1)

  • Hash Cond: (pro.presentation_uom_id = xu.uom_id)
4. 4.392 3,759.932 ↓ 130.3 4,169 1

Nested Loop Left Join (cost=205,041.85..236,139.22 rows=32 width=162) (actual time=2,919.984..3,759.932 rows=4,169 loops=1)

5. 3.268 3,751.371 ↓ 130.3 4,169 1

Nested Loop Left Join (cost=205,041.56..236,120.95 rows=32 width=157) (actual time=2,919.981..3,751.371 rows=4,169 loops=1)

6. 47.076 3,735.596 ↓ 130.3 4,169 1

Merge Join (cost=205,041.27..236,110.85 rows=32 width=144) (actual time=2,919.966..3,735.596 rows=4,169 loops=1)

  • Merge Cond: (xue.target_uom_id = pro.presentation_uom_id)
  • Join Filter: (pro.product_id = xue.product_id)
  • Rows Removed by Join Filter: 158422
7. 0.185 0.185 ↓ 1.0 78 1

Index Scan using uom_equivalence_uk on uom_equivalence xue (cost=0.14..13.26 rows=75 width=16) (actual time=0.026..0.185 rows=78 loops=1)

  • Index Cond: (source_uom_id = 1)
8. 11.644 3,688.335 ↓ 21.2 162,553 1

Materialize (cost=205,040.77..521,170.87 rows=7,662 width=140) (actual time=2,875.148..3,688.335 rows=162,553 loops=1)

9. 627.637 3,676.691 ↑ 1.8 4,169 1

Nested Loop (cost=205,040.77..521,151.71 rows=7,662 width=140) (actual time=2,875.144..3,676.691 rows=4,169 loops=1)

  • Join Filter: (pi.product_id = pro.product_id)
  • Rows Removed by Join Filter: 3468608
10. 1.940 1.940 ↓ 1.0 833 1

Index Scan using products_presentation_uom_id_idx on products pro (cost=0.28..74.02 rows=830 width=43) (actual time=0.089..1.940 rows=833 loops=1)

11. 179.918 3,047.114 ↑ 1.9 4,169 833

Materialize (cost=205,040.50..420,837.97 rows=8,053 width=97) (actual time=3.152..3.658 rows=4,169 loops=833)

12. 3.746 2,867.196 ↑ 1.9 4,169 1

Nested Loop Left Join (cost=205,040.50..420,797.71 rows=8,053 width=97) (actual time=2,625.332..2,867.196 rows=4,169 loops=1)

13. 16.498 2,830.122 ↑ 1.9 4,166 1

Hash Left Join (cost=205,040.06..399,303.91 rows=8,053 width=90) (actual time=2,625.281..2,830.122 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
14. 1.142 2,752.497 ↑ 14.1 7,889 1

Nested Loop Left Join (cost=201,363.45..390,276.69 rows=110,929 width=81) (actual time=2,563.232..2,752.497 rows=7,889 loops=1)

15. 99.068 2,719.799 ↑ 14.1 7,889 1

Hash Right Join (cost=201,363.01..212,758.67 rows=110,929 width=81) (actual time=2,563.178..2,719.799 rows=7,889 loops=1)

  • Hash Cond: (ihdl.product_instance_id = pi.id)
16. 66.193 66.193 ↑ 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.016..66.193 rows=324,279 loops=1)

17. 2.733 2,554.538 ↑ 16.4 4,354 1

Hash (cost=199,561.18..199,561.18 rows=71,507 width=77) (actual time=2,554.538..2,554.538 rows=4,354 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 629kB
18. 25.123 2,551.805 ↑ 16.4 4,354 1

Hash Join (cost=12,565.98..199,561.18 rows=71,507 width=77) (actual time=171.063..2,551.805 rows=4,354 loops=1)

  • Hash Cond: (dlt.product_instance_id = pi.id)
19. 20.839 2,415.550 ↑ 22.7 4,354 1

Hash Join (cost=4,141.74..186,721.54 rows=98,753 width=73) (actual time=58.828..2,415.550 rows=4,354 loops=1)

  • Hash Cond: (dlt.document_id = dt.document_id)
20. 1,172.272 2,348.720 ↑ 1.6 176,015 1

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

  • Hash Cond: (dlt.warehouse_location_id = wl.warehouse_location_id)
21. 1,167.854 1,167.854 ↓ 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.025..1,167.854 rows=7,719,859 loops=1)

22. 0.290 8.594 ↓ 1.1 1,176 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 81kB
23. 8.304 8.304 ↓ 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.016..8.304 rows=1,176 loops=1)

  • Filter: ((name)::text ~~ 'F.%'::text)
  • Rows Removed by Filter: 28068
24. 8.772 45.991 ↑ 1.0 26,721 1

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

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

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

  • Filter: ((document_id IS NULL) OR ((type)::text = 'ORDER_OF_MOVE'::text))
  • Rows Removed by Filter: 48145
26. 52.854 111.132 ↓ 1.0 211,339 1

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

  • Buckets: 131072 Batches: 4 Memory Usage: 3094kB
27. 58.278 58.278 ↓ 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.014..58.278 rows=211,339 loops=1)

28. 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)
29. 26.787 61.127 ↑ 1.0 74,866 1

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

  • Buckets: 65536 Batches: 2 Memory Usage: 2897kB
30. 34.340 34.340 ↑ 1.0 74,866 1

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

31. 33.328 33.328 ↑ 1.0 1 4,166

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.006..0.008 rows=1 loops=4,166)

  • Index Cond: (entity_id = pi.id)
  • Filter: (((entity_class)::text = 'ProductInstance'::text) AND (attributes_type_param_id = 3))
  • Rows Removed by Filter: 3
32. 12.507 12.507 ↑ 1.0 1 4,169

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.003..0.003 rows=1 loops=4,169)

  • Index Cond: (warehouse_location_id = dlt.warehouse_source_location_id)
33. 4.169 4.169 ↑ 1.0 1 4,169

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=1 loops=4,169)

  • Index Cond: (document_id = dt.document_parent_id)
34. 0.009 0.033 ↑ 1.0 9 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 0.024 0.024 ↑ 1.0 9 1

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

Planning time : 7.393 ms
Execution time : 3,801.234 ms