explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PG3Q

Settings
# exclusive inclusive rows x rows loops node
1. 21.137 1,223.508 ↓ 2.3 12,415 1

Sort (cost=33,055.18..33,068.70 rows=5,409 width=1,916) (actual time=1,223.045..1,223.508 rows=12,415 loops=1)

  • Sort Key: (naturalsort((despatch_history.despatch_document_number)::text))
  • Sort Method: quicksort Memory: 4923kB
2. 402.005 1,202.371 ↓ 2.3 12,415 1

Hash Left Join (cost=28,569.02..30,087.59 rows=5,409 width=1,916) (actual time=771.041..1,202.371 rows=12,415 loops=1)

  • Hash Cond: (despatch_history.despatch_document_id = _pivot_output.document_id)
3. 12.969 800.182 ↓ 2.3 12,415 1

Unique (cost=28,555.87..28,609.96 rows=5,409 width=1,348) (actual time=770.538..800.182 rows=12,415 loops=1)

4.          

CTE document_features

5. 1.141 58.105 ↑ 3.8 6,138 1

Unique (cost=12,242.49..12,559.49 rows=23,449 width=40) (actual time=56.569..58.105 rows=6,138 loops=1)

6. 2.515 56.964 ↑ 6.9 6,140 1

Sort (cost=12,242.49..12,348.16 rows=42,267 width=40) (actual time=56.568..56.964 rows=6,140 loops=1)

  • Sort Key: feature.entity_id, feature.definition_id
  • Sort Method: quicksort Memory: 616kB
7. 25.360 54.449 ↑ 6.9 6,140 1

Hash Join (cost=4,631.34..8,994.85 rows=42,267 width=40) (actual time=16.647..54.449 rows=6,140 loops=1)

  • Hash Cond: (feature.value_id = feature_value.id)
8. 13.401 13.401 ↓ 1.0 42,618 1

Seq Scan on dynamic_valuable_feature feature (cost=0.00..2,224.88 rows=42,480 width=12) (actual time=0.023..13.401 rows=42,618 loops=1)

  • Filter: ((NOT is_deleted) AND (NOT is_deleted) AND (entity_name = 'Feniks.Domain.WarehouseDocument'::text))
  • Rows Removed by Filter: 41932
9. 2.899 15.688 ↑ 7.7 10,980 1

Hash (cost=2,921.75..2,921.75 rows=84,127 width=38) (actual time=15.688..15.688 rows=10,980 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 1273kB
10. 12.789 12.789 ↑ 7.7 10,980 1

Seq Scan on dynamic_valuable_feature_value feature_value (cost=0.00..2,921.75 rows=84,127 width=38) (actual time=0.017..12.789 rows=10,980 loops=1)

  • Filter: (COALESCE(value_text, (value_integer)::text, (value_decimal)::text, (value_bool)::text, ''::text) <> ''::text)
  • Rows Removed by Filter: 73570
11. 110.605 787.213 ↓ 6.7 36,444 1

Sort (cost=15,996.38..16,009.90 rows=5,409 width=1,348) (actual time=770.537..787.213 rows=36,444 loops=1)

  • Sort Key: despatch_history.despatch_document_id, despatch_history.warehouse_id, despatch_history.assortment_owner_id
  • Sort Method: external sort Disk: 8768kB
12. 36.862 676.608 ↓ 6.7 36,444 1

WindowAgg (cost=13,621.75..13,797.54 rows=5,409 width=1,348) (actual time=626.281..676.608 rows=36,444 loops=1)

13. 74.358 639.746 ↓ 6.7 36,444 1

Sort (cost=13,621.75..13,635.27 rows=5,409 width=1,265) (actual time=626.270..639.746 rows=36,444 loops=1)

  • Sort Key: despatch_history.despatch_document_id, despatch_history.assortment_id DESC
  • Sort Method: external sort Disk: 8936kB
14. 30.145 565.388 ↓ 6.7 36,444 1

WindowAgg (cost=11,423.38..11,531.56 rows=5,409 width=1,265) (actual time=527.565..565.388 rows=36,444 loops=1)

15. 72.885 535.243 ↓ 6.7 36,444 1

Sort (cost=11,423.38..11,436.90 rows=5,409 width=1,257) (actual time=527.552..535.243 rows=36,444 loops=1)

  • Sort Key: despatch_history.despatch_document_id, despatch_history.assortment_id
  • Sort Method: external merge Disk: 8584kB
16. 37.413 462.358 ↓ 6.7 36,444 1

Hash Right Join (cost=8,673.81..9,343.44 rows=5,409 width=1,257) (actual time=422.777..462.358 rows=36,444 loops=1)

  • Hash Cond: (features.entity_id = doc.id)
17. 59.177 59.177 ↑ 3.8 6,138 1

CTE Scan on document_features features (cost=0.00..468.98 rows=23,449 width=40) (actual time=56.573..59.177 rows=6,138 loops=1)

18. 29.520 365.768 ↓ 7.3 34,549 1

Hash (cost=8,615.01..8,615.01 rows=4,704 width=1,225) (actual time=365.768..365.768 rows=34,549 loops=1)

  • Buckets: 32768 (originally 8192) Batches: 2 (originally 1) Memory Usage: 5861kB
19. 48.321 336.248 ↓ 7.3 34,549 1

Hash Right Join (cost=7,386.59..8,615.01 rows=4,704 width=1,225) (actual time=283.189..336.248 rows=34,549 loops=1)

  • Hash Cond: (contact_person.id = a_cont.contact_id)
20. 5.170 5.170 ↑ 1.0 37,198 1

Seq Scan on contact contact_person (cost=0.00..996.29 rows=37,219 width=13) (actual time=0.022..5.170 rows=37,198 loops=1)

  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 831
21. 29.326 282.757 ↓ 7.3 34,549 1

Hash (cost=7,327.79..7,327.79 rows=4,704 width=1,220) (actual time=282.757..282.757 rows=34,549 loops=1)

  • Buckets: 32768 (originally 8192) Batches: 2 (originally 1) Memory Usage: 5861kB
22. 43.267 253.431 ↓ 7.3 34,549 1

Hash Right Join (cost=6,653.61..7,327.79 rows=4,704 width=1,220) (actual time=208.269..253.431 rows=34,549 loops=1)

  • Hash Cond: (a_cont.address_id = address.id)
23. 2.405 2.405 ↑ 1.0 34,747 1

Seq Scan on address_contact a_cont (cost=0.00..501.47 rows=34,747 width=8) (actual time=0.019..2.405 rows=34,747 loops=1)

24. 30.706 207.759 ↓ 7.3 34,549 1

Hash (cost=6,594.81..6,594.81 rows=4,704 width=1,220) (actual time=207.759..207.759 rows=34,549 loops=1)

  • Buckets: 32768 (originally 8192) Batches: 2 (originally 1) Memory Usage: 5861kB
25. 20.819 177.053 ↓ 7.3 34,549 1

Hash Join (cost=4,843.70..6,594.81 rows=4,704 width=1,220) (actual time=152.258..177.053 rows=34,549 loops=1)

  • Hash Cond: (despatch_history.despatch_document_id = doc.id)
26. 4.042 4.042 ↑ 1.0 34,871 1

Seq Scan on allocation_despatch_history despatch_history (cost=0.00..1,529.71 rows=34,871 width=33) (actual time=0.047..4.042 rows=34,871 loops=1)

27. 7.308 152.192 ↓ 4.6 12,648 1

Hash (cost=4,809.31..4,809.31 rows=2,751 width=1,187) (actual time=152.192..152.192 rows=12,648 loops=1)

  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 2886kB
28. 3.583 144.884 ↓ 4.6 12,648 1

Hash Left Join (cost=3,875.23..4,809.31 rows=2,751 width=1,187) (actual time=122.049..144.884 rows=12,648 loops=1)

  • Hash Cond: (doc.type_definition_id = doc_type_definition.id)
29. 3.392 141.260 ↓ 4.6 12,648 1

Hash Join (cost=3,873.51..4,773.63 rows=2,751 width=675) (actual time=121.994..141.260 rows=12,648 loops=1)

  • Hash Cond: (shipping_parameters.shipping_definition_id = shipping_definition.id)
30. 3.381 137.820 ↓ 4.6 12,648 1

Hash Join (cost=3,871.56..4,737.45 rows=2,751 width=163) (actual time=121.939..137.820 rows=12,648 loops=1)

  • Hash Cond: (doc.contractor_id = contractor.id)
31. 10.032 126.867 ↓ 4.6 12,648 1

Hash Join (cost=3,413.50..4,244.65 rows=2,751 width=143) (actual time=114.323..126.867 rows=12,648 loops=1)

  • Hash Cond: (shipping_parameters.id = shipping_order.shipping_parameters_id)
32. 2.757 2.757 ↑ 1.0 32,192 1

Seq Scan on shipping_parameters (cost=0.00..682.92 rows=32,192 width=8) (actual time=0.024..2.757 rows=32,192 loops=1)

33. 11.312 114.078 ↓ 4.6 12,648 1

Hash (cost=3,379.12..3,379.12 rows=2,751 width=143) (actual time=114.078..114.078 rows=12,648 loops=1)

  • Buckets: 16384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 2500kB
34. 11.258 102.766 ↓ 4.6 12,648 1

Merge Join (cost=0.86..3,379.12 rows=2,751 width=143) (actual time=0.082..102.766 rows=12,648 loops=1)

  • Merge Cond: (doc.shipping_order_id = shipping_order.id)
35. 12.956 84.822 ↓ 4.6 12,649 1

Nested Loop Left Join (cost=0.58..4,602.09 rows=2,751 width=119) (actual time=0.062..84.822 rows=12,649 loops=1)

36. 33.919 33.919 ↓ 4.6 12,649 1

Index Scan using fki_collective_documents_shipping_order_id_fkey on warehouse_document doc (cost=0.29..2,682.56 rows=2,751 width=64) (actual time=0.039..33.919 rows=12,649 loops=1)

  • Filter: ((NOT is_deleted) AND (warehouse_document_type_id = 3) AND CASE COALESCE(('{}'::integer[])[1], 1) WHEN 1 THEN (create_date >= '1001-01-01'::date) WHEN 2 THEN (commit_date >= '1001-01-01'::date) ELSE NULL::boolean END AND CASE COALESCE(('{}'::integer[])[1], 1) WHEN 1 THEN (create_date <= ((date_trunc('day'::text, (COALESCE((now())::date))::timestamp with time zone) + '1 day'::interval) - '00:00:01'::interval)) WHEN 2 THEN (commit_date <= ((date_trunc('day'::text, (COALESCE((now())::date))::timestamp with time zone) + '1 day'::interval) - '00:00:01'::interval)) ELSE NULL::boolean END)
  • Rows Removed by Filter: 89
37. 37.947 37.947 ↑ 1.0 1 12,649

Index Scan using address_pkey on address (cost=0.29..0.70 rows=1 width=63) (actual time=0.003..0.003 rows=1 loops=12,649)

  • Index Cond: (doc.shipping_to_address_id = id)
  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 0
38. 6.686 6.686 ↑ 1.0 12,638 1

Index Scan using shipping_order_pkey on shipping_order (cost=0.29..426.80 rows=12,638 width=32) (actual time=0.014..6.686 rows=12,638 loops=1)

39. 3.970 7.572 ↑ 1.0 11,336 1

Hash (cost=316.36..316.36 rows=11,336 width=28) (actual time=7.572..7.572 rows=11,336 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 822kB
40. 3.602 3.602 ↑ 1.0 11,336 1

Seq Scan on contractor (cost=0.00..316.36 rows=11,336 width=28) (actual time=0.022..3.602 rows=11,336 loops=1)

41. 0.015 0.048 ↑ 1.0 42 1

Hash (cost=1.42..1.42 rows=42 width=520) (actual time=0.048..0.048 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
42. 0.033 0.033 ↑ 1.0 42 1

Seq Scan on shipping_definition (cost=0.00..1.42 rows=42 width=520) (actual time=0.022..0.033 rows=42 loops=1)

43. 0.012 0.041 ↑ 1.0 32 1

Hash (cost=1.32..1.32 rows=32 width=520) (actual time=0.041..0.041 rows=32 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
44. 0.029 0.029 ↑ 1.0 32 1

Seq Scan on warehouse_document_type_definition doc_type_definition (cost=0.00..1.32 rows=32 width=520) (actual time=0.024..0.029 rows=32 loops=1)

45. 0.102 0.184 ↓ 6.6 926 1

Hash (cost=11.40..11.40 rows=140 width=548) (actual time=0.184..0.184 rows=926 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 53kB
46. 0.082 0.082 ↓ 6.6 926 1

Seq Scan on _pivot_output (cost=0.00..11.40 rows=140 width=548) (actual time=0.022..0.082 rows=926 loops=1)