PgHero
No long running queries
Connections healthy 264
Vacuuming healthy
No columns near integer overflow (14 unreadable sequences)
No invalid indexes or constraints
106 duplicate indexes
No suggested indexes
25 slow queries

Duplicate Indexes

These indexes exist, but aren’t needed. Remove them for faster writes. 

rails generate migration remove_unneeded_indexes

And paste

remove_index :connexx_shipment_packages, name: "idx_csp_shipment_id", column: :shipment_id
remove_index :connexx_shipment_packages, name: "idx_tracking_code_trgm", column: :"tracking_code gin_trgm_ops"
remove_index :connexx_shipments, name: "connexx_shipments_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2022_10, name: "connexx_shipments_new_2022_10_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2022_10, name: "connexx_shipments_new_2022_10_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2022_10, name: "connexx_shipments_new_2022_10_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2022_11, name: "connexx_shipments_new_2022_11_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2022_11, name: "connexx_shipments_new_2022_11_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2022_11, name: "connexx_shipments_new_2022_11_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2022_12, name: "connexx_shipments_new_2022_12_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2022_12, name: "connexx_shipments_new_2022_12_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2022_12, name: "connexx_shipments_new_2022_12_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2023_01, name: "connexx_shipments_new_2023_01_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2023_01, name: "connexx_shipments_new_2023_01_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2023_01, name: "connexx_shipments_new_2023_01_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2023_02, name: "connexx_shipments_new_2023_02_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2023_02, name: "connexx_shipments_new_2023_02_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2023_02, name: "connexx_shipments_new_2023_02_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2023_03, name: "connexx_shipments_new_2023_03_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2023_03, name: "connexx_shipments_new_2023_03_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2023_03, name: "connexx_shipments_new_2023_03_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2023_04, name: "connexx_shipments_new_2023_04_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2023_04, name: "connexx_shipments_new_2023_04_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2023_04, name: "connexx_shipments_new_2023_04_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2023_05, name: "connexx_shipments_new_2023_05_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2023_05, name: "connexx_shipments_new_2023_05_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2023_05, name: "connexx_shipments_new_2023_05_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2023_06, name: "connexx_shipments_new_2023_06_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2023_06, name: "connexx_shipments_new_2023_06_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2023_06, name: "connexx_shipments_new_2023_06_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2023_07, name: "connexx_shipments_new_2023_07_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2023_07, name: "connexx_shipments_new_2023_07_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2023_07, name: "connexx_shipments_new_2023_07_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2023_08, name: "connexx_shipments_new_2023_08_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2023_08, name: "connexx_shipments_new_2023_08_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2023_08, name: "connexx_shipments_new_2023_08_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2023_09, name: "connexx_shipments_new_2023_09_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2023_09, name: "connexx_shipments_new_2023_09_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2023_09, name: "connexx_shipments_new_2023_09_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2023_10, name: "connexx_shipments_new_2023_10_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2023_10, name: "connexx_shipments_new_2023_10_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2023_10, name: "connexx_shipments_new_2023_10_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2023_11, name: "connexx_shipments_new_2023_11_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2023_11, name: "connexx_shipments_new_2023_11_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2023_11, name: "connexx_shipments_new_2023_11_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2023_12, name: "connexx_shipments_new_2023_12_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2023_12, name: "connexx_shipments_new_2023_12_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2023_12, name: "connexx_shipments_new_2023_12_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2024_01, name: "connexx_shipments_new_2024_01_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2024_01, name: "connexx_shipments_new_2024_01_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2024_01, name: "connexx_shipments_new_2024_01_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2024_02, name: "connexx_shipments_new_2024_02_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2024_02, name: "connexx_shipments_new_2024_02_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2024_02, name: "connexx_shipments_new_2024_02_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2024_03, name: "connexx_shipments_new_2024_03_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2024_03, name: "connexx_shipments_new_2024_03_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2024_03, name: "connexx_shipments_new_2024_03_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2024_04, name: "connexx_shipments_new_2024_04_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2024_04, name: "connexx_shipments_new_2024_04_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2024_04, name: "connexx_shipments_new_2024_04_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2024_05, name: "connexx_shipments_new_2024_05_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2024_05, name: "connexx_shipments_new_2024_05_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2024_05, name: "connexx_shipments_new_2024_05_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2024_06, name: "connexx_shipments_new_2024_06_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2024_06, name: "connexx_shipments_new_2024_06_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2024_06, name: "connexx_shipments_new_2024_06_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2024_07, name: "connexx_shipments_new_2024_07_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2024_07, name: "connexx_shipments_new_2024_07_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2024_07, name: "connexx_shipments_new_2024_07_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2024_08, name: "connexx_shipments_new_2024_08_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2024_08, name: "connexx_shipments_new_2024_08_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2024_08, name: "connexx_shipments_new_2024_08_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2024_09, name: "connexx_shipments_new_2024_09_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2024_09, name: "connexx_shipments_new_2024_09_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2024_09, name: "connexx_shipments_new_2024_09_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2024_10, name: "connexx_shipments_new_2024_10_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2024_10, name: "connexx_shipments_new_2024_10_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2024_10, name: "connexx_shipments_new_2024_10_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2024_11, name: "connexx_shipments_new_2024_11_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2024_11, name: "connexx_shipments_new_2024_11_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2024_11, name: "connexx_shipments_new_2024_11_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2024_12, name: "connexx_shipments_new_2024_12_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2024_12, name: "connexx_shipments_new_2024_12_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2024_12, name: "connexx_shipments_new_2024_12_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :connexx_shipments_new_2025_01, name: "connexx_shipments_new_2025_01_id_idx1", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments_new_2025_01, name: "connexx_shipments_new_2025_01_lower_idx", column: :"lower((order_number)::text)"
remove_index :connexx_shipments_new_2025_01, name: "connexx_shipments_new_2025_01_sales_account_id_created_date_idx", column: [:sales_account_id, :created_date]
remove_index :consignment_imports, name: "ci_sales_account_id_idx", column: :sales_account_id
remove_index :currency, name: "idx_currency_id", column: :id
remove_index :currency, name: "idx_currency", column: :id
remove_index :financial_company, name: "idx_financial_company_id", column: :id
remove_index :flattened_shipments, name: "idx_flattened_created_day", column: :created_date_day
remove_index :flattened_shipments, name: "idx_flattened_created_month", column: :created_date_month
remove_index :flattened_shipments, name: "idx_flattened_created_week", column: :created_date_week
remove_index :intl_consignments_surcharges_transactions, name: "icst_supplier_linked_invoice_id_index", column: :supplier_linked_invoice_id
remove_index :purchase_account_transactions, name: "index_pth_transaction_date", column: :transaction_date
remove_index :sales_account, name: "idx_sa_manager_structure", column: :account_manager_structure_id
remove_index :sales_account_shipping_whitelist, name: "sales_account_shipping_whitelist_sales_account_id_index", column: :sales_account_id
remove_index :sales_account_transactions, name: "slt_sales_acc_ind", column: :sales_account_id
remove_index :surcharge_types, name: "idx_surcharge_types_code", column: :code
remove_index :system_user, name: "idx_system_user_id", column: :id
remove_index :uk_ecommerce_consignment_surcharges, name: "ukecs_supplier_invoice_id_index", column: :supplier_linked_invoice_id
remove_index :uk_ecommerce_consignments, name: "dom_con_sa_id_idx", column: :sales_account_id
remove_index :uk_ecommerce_consignments, name: "dom_con_supplier_invoice_id_idx", column: :supplier_linked_invoice_id
remove_index :uk_ecommerce_supplier_invoice, name: "idx_ukesi_supplier_account_id", column: :supplier_account_id
remove_index :uk_postcodes_april_2025, name: "idx_postcodes_latlng_group", column: [:lat_group, :lng_group]
Details
On connexx_shipment_packages 
idx_csp_shipment_id (shipment_id)
is covered by 
idx_on_packages_shipment_id_created_date (shipment_id, created_date DESC)
On connexx_shipment_packages 
idx_tracking_code_trgm (tracking_code gin_trgm_ops)
is covered by 
idx_tracking_code_partial (tracking_code gin_trgm_ops)
On connexx_shipments 
connexx_shipments_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
idx_connexx_shipments_filter (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2022_10 
connexx_shipments_new_2022_10_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2022_10_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2022_10 
connexx_shipments_new_2022_10_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2022_10_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2022_10 
connexx_shipments_new_2022_10_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2022_10_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2022_11 
connexx_shipments_new_2022_11_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2022_11_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2022_11 
connexx_shipments_new_2022_11_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2022_11_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2022_11 
connexx_shipments_new_2022_11_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2022_11_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2022_12 
connexx_shipments_new_2022_12_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2022_12_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2022_12 
connexx_shipments_new_2022_12_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2022_12_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2022_12 
connexx_shipments_new_2022_12_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2022_12_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2023_01 
connexx_shipments_new_2023_01_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2023_01_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2023_01 
connexx_shipments_new_2023_01_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2023_01_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2023_01 
connexx_shipments_new_2023_01_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2023_01_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2023_02 
connexx_shipments_new_2023_02_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2023_02_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2023_02 
connexx_shipments_new_2023_02_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2023_02_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2023_02 
connexx_shipments_new_2023_02_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2023_02_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2023_03 
connexx_shipments_new_2023_03_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2023_03_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2023_03 
connexx_shipments_new_2023_03_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2023_03_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2023_03 
connexx_shipments_new_2023_03_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2023_03_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2023_04 
connexx_shipments_new_2023_04_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2023_04_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2023_04 
connexx_shipments_new_2023_04_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2023_04_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2023_04 
connexx_shipments_new_2023_04_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2023_04_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2023_05 
connexx_shipments_new_2023_05_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2023_05_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2023_05 
connexx_shipments_new_2023_05_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2023_05_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2023_05 
connexx_shipments_new_2023_05_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2023_05_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2023_06 
connexx_shipments_new_2023_06_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2023_06_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2023_06 
connexx_shipments_new_2023_06_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2023_06_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2023_06 
connexx_shipments_new_2023_06_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2023_06_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2023_07 
connexx_shipments_new_2023_07_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2023_07_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2023_07 
connexx_shipments_new_2023_07_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2023_07_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2023_07 
connexx_shipments_new_2023_07_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2023_07_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2023_08 
connexx_shipments_new_2023_08_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2023_08_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2023_08 
connexx_shipments_new_2023_08_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2023_08_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2023_08 
connexx_shipments_new_2023_08_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2023_08_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2023_09 
connexx_shipments_new_2023_09_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2023_09_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2023_09 
connexx_shipments_new_2023_09_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2023_09_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2023_09 
connexx_shipments_new_2023_09_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2023_09_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2023_10 
connexx_shipments_new_2023_10_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2023_10_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2023_10 
connexx_shipments_new_2023_10_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2023_10_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2023_10 
connexx_shipments_new_2023_10_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2023_10_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2023_11 
connexx_shipments_new_2023_11_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2023_11_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2023_11 
connexx_shipments_new_2023_11_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2023_11_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2023_11 
connexx_shipments_new_2023_11_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2023_11_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2023_12 
connexx_shipments_new_2023_12_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2023_12_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2023_12 
connexx_shipments_new_2023_12_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2023_12_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2023_12 
connexx_shipments_new_2023_12_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2023_12_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2024_01 
connexx_shipments_new_2024_01_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2024_01_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2024_01 
connexx_shipments_new_2024_01_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2024_01_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2024_01 
connexx_shipments_new_2024_01_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2024_01_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2024_02 
connexx_shipments_new_2024_02_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2024_02_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2024_02 
connexx_shipments_new_2024_02_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2024_02_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2024_02 
connexx_shipments_new_2024_02_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2024_02_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2024_03 
connexx_shipments_new_2024_03_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2024_03_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2024_03 
connexx_shipments_new_2024_03_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2024_03_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2024_03 
connexx_shipments_new_2024_03_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2024_03_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2024_04 
connexx_shipments_new_2024_04_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2024_04_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2024_04 
connexx_shipments_new_2024_04_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2024_04_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2024_04 
connexx_shipments_new_2024_04_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2024_04_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2024_05 
connexx_shipments_new_2024_05_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2024_05_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2024_05 
connexx_shipments_new_2024_05_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2024_05_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2024_05 
connexx_shipments_new_2024_05_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2024_05_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2024_06 
connexx_shipments_new_2024_06_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2024_06_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2024_06 
connexx_shipments_new_2024_06_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2024_06_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2024_06 
connexx_shipments_new_2024_06_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2024_06_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2024_07 
connexx_shipments_new_2024_07_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2024_07_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2024_07 
connexx_shipments_new_2024_07_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2024_07_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2024_07 
connexx_shipments_new_2024_07_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2024_07_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2024_08 
connexx_shipments_new_2024_08_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2024_08_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2024_08 
connexx_shipments_new_2024_08_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2024_08_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2024_08 
connexx_shipments_new_2024_08_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2024_08_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2024_09 
connexx_shipments_new_2024_09_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2024_09_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2024_09 
connexx_shipments_new_2024_09_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2024_09_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2024_09 
connexx_shipments_new_2024_09_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2024_09_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2024_10 
connexx_shipments_new_2024_10_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2024_10_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2024_10 
connexx_shipments_new_2024_10_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2024_10_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2024_10 
connexx_shipments_new_2024_10_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2024_10_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2024_11 
connexx_shipments_new_2024_11_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2024_11_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2024_11 
connexx_shipments_new_2024_11_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2024_11_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2024_11 
connexx_shipments_new_2024_11_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2024_11_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2024_12 
connexx_shipments_new_2024_12_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2024_12_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2024_12 
connexx_shipments_new_2024_12_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2024_12_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2024_12 
connexx_shipments_new_2024_12_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2024_12_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On connexx_shipments_new_2025_01 
connexx_shipments_new_2025_01_id_idx1 (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
is covered by 
connexx_shipments_new_2025_01_id_idx (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
On connexx_shipments_new_2025_01 
connexx_shipments_new_2025_01_lower_idx (lower((order_number)::text))
is covered by 
connexx_shipments_new_2025_01_lower_shipment_source_name_sa_idx (lower((order_number)::text), shipment_source_name, sales_account_id, status)
On connexx_shipments_new_2025_01 
connexx_shipments_new_2025_01_sales_account_id_created_date_idx (sales_account_id, created_date)
is covered by 
connexx_shipments_new_2025_01_sales_account_id_created_dat_idx1 (sales_account_id, created_date, shipment_type, status)
On consignment_imports 
ci_sales_account_id_idx (sales_account_id)
is covered by 
idx_ci_sa_created_date (sales_account_id, created_date_date)
On currency 
idx_currency_id (id)
is covered by 
currency_pk (id)
On currency 
idx_currency (id)
is covered by 
currency_pk (id)
On financial_company 
idx_financial_company_id (id)
is covered by 
fin_comp_pk (id)
On flattened_shipments 
idx_flattened_created_day (created_date_day)
is covered by 
idx_flattened_day_account_status (created_date_day, sales_account_id, status)
On flattened_shipments 
idx_flattened_created_month (created_date_month)
is covered by 
idx_created_month_status_customer (created_date_month, status, customer_name)
On flattened_shipments 
idx_flattened_created_week (created_date_week)
is covered by 
idx_created_week_status_customer (created_date_week, status, customer_name)
On intl_consignments_surcharges_transactions 
icst_supplier_linked_invoice_id_index (supplier_linked_invoice_id)
is covered by 
idx_icst_siid_cons_surch (supplier_linked_invoice_id, consignment_id, consignment_surcharge_id)
On purchase_account_transactions 
index_pth_transaction_date (transaction_date)
is covered by 
idx_pat_txdate_flags (transaction_date, reversal_user, status, financial_company)
On sales_account 
idx_sa_manager_structure (account_manager_structure_id)
is covered by 
idx_sales_account_structure (account_manager_structure_id, sales_person_structure_id)
On sales_account_shipping_whitelist 
sales_account_shipping_whitelist_sales_account_id_index (sales_account_id)
is covered by 
idx_sales_account_whitelist_multi (sales_account_id, status, from_weight, to_weight, carrier_id, supplier_id, tariff_id, service_id)
On sales_account_transactions 
slt_sales_acc_ind (sales_account_id)
is covered by 
active_sat_index (sales_account_id, financial_company, status, reversal_user, reversed_by, original_id, transaction_category)
On surcharge_types 
idx_surcharge_types_code (code)
is covered by 
surcharge_types_code_key (code)
On system_user 
idx_system_user_id (id)
is covered by 
system_users_pk (id)
On uk_ecommerce_consignment_surcharges 
ukecs_supplier_invoice_id_index (supplier_linked_invoice_id)
is covered by 
idx_ukecs_siid_charge_client_tariff (supplier_linked_invoice_id, charge_type, client, tariff_id)
On uk_ecommerce_consignments 
dom_con_sa_id_idx (sales_account_id)
is covered by 
idx_ukec_sa_svc_created_desc (sales_account_id, service_id, created_date DESC) INCLUDE (sales_price)
On uk_ecommerce_consignments 
dom_con_supplier_invoice_id_idx (supplier_linked_invoice_id)
is covered by 
idx_ukec_siid_client (supplier_linked_invoice_id, client)
On uk_ecommerce_supplier_invoice 
idx_ukesi_supplier_account_id (supplier_account_id)
is covered by 
idx_ukesi_supplier_account_purchase_tx (supplier_account_id, purchase_transaction_id)
On uk_postcodes_april_2025 
idx_postcodes_latlng_group (lat_group, lng_group)
is covered by 
idx_uk_postcodes_grouping (lat_group, lng_group, postcode_prefix)

Slow Queries

Slow queries take 20 ms or more on average and have been called at least 100 times.

Explain queries to see where to add indexes.

Total Time Average Time Calls
1,018 min 16% 619 ms 98,718 upadmin 
SELECT "public"."connexx_shipments"."id", "public"."connexx_shipments"."uuid", "public"."connexx_shipments"."created_by", "public"."connexx_shipments"."created_date", "public"."connexx_shipments"."last_modified_by", "public"."connexx_shipments"."last_modified_date", "public"."connexx_shipments"."duty_or_tax_paid_by", "public"."connexx_shipments"."customer_reference", "public"."connexx_shipments"."shipment_bulk_upload_id", "public"."connexx_shipments"."applied_rule_id", "public"."connexx_shipments"."from_address_first_name", "public"."connexx_shipments"."from_address_last_name", "public"."connexx_shipments"."from_address_company", "public"."connexx_shipments"."from_address_email", "public"."connexx_shipments"."from_address_phone", "public"."connexx_shipments"."from_address_street_1", "public"."connexx_shipments"."from_address_street_2", "public"."connexx_shipments"."from_address_street_3", "public"."connexx_shipments"."from_address_city", "public"."connexx_shipments"."from_address_county_state", "public"."connexx_shipments"."from_address_country_iso", "public"."connexx_shipments"."from_address_zip", "public"."connexx_shipments"."to_address_first_name", "public"."connexx_shipments"."to_address_last_name", "public"."connexx_shipments"."to_address_company", "public"."connexx_shipments"."to_address_email", "public"."connexx_shipments"."to_address_phone", "public"."connexx_shipments"."to_address_street_1", "public"."connexx_shipments"."to_address_street_2", "public"."connexx_shipments"."to_address_street_3", "public"."connexx_shipments"."to_address_city", "public"."connexx_shipments"."to_address_county_state", "public"."connexx_shipments"."to_address_country_iso", "public"."connexx_shipments"."to_address_zip", "public"."connexx_shipments"."sales_account_id", "public"."connexx_shipments"."job_number", "public"."connexx_shipments"."generated_by_admin", "public"."connexx_shipments"."date_converted_to_shipment", "public"."connexx_shipments"."shipment_type", "public"."connexx_shipments"."deleted_date", "public"."connexx_shipments"."deleted_by", "public"."connexx_shipments"."status", "public"."connexx_shipments"."rates_sql_query_output", "public"."connexx_shipments"."rates_sql_query_input", "public"."connexx_shipments"."errors", "public"."connexx_shipments"."reason_for_export", "public"."connexx_shipments"."combined_label_url", "public"."connexx_shipments"."bulk_upload_row_id", "public"."connexx_shipments"."original_shipment_id", "public"."connexx_shipments"."_remove_selected_quote_id", "public"."connexx_shipments"."shipping_whitelist_id", "public"."connexx_shipments"."from_address_vat_number", "public"."connexx_shipments"."from_address_eori_number", "public"."connexx_shipments"."to_address_vat_number", "public"."connexx_shipments"."to_address_eori_number", "public"."connexx_shipments"."virtual_warehouse_id", "public"."connexx_shipments"."service_options", "public"."connexx_shipments"."consignment_number", "public"."connexx_shipments"."commercial_invoice_pdf_url", "public"."connexx_shipments"."external_customer_id", "public"."connexx_shipments"."carrier_depot_id", "public"."connexx_shipments"."base64_eltron", "public"."connexx_shipments"."base64_citizen", "public"."connexx_shipments"."label_type"::text, "public"."connexx_shipments"."carrier_shipment_id", "public"."connexx_shipments"."invoice_date", "public"."connexx_shipments"."invoice_number", "public"."connexx_shipments"."order_number", "public"."connexx_shipments"."carrier_label_errors", "public"."connexx_shipments"."commercial_invoice_base64", "public"."connexx_shipments"."should_generate_commercial_invoice", "public"."connexx_shipments"."use_insurance", "public"."connexx_shipments"."insurance_amount", "public"."connexx_shipments"."insurance_amount_currency_code", "public"."connexx_shipments"."fedex_pickup_request_form_base64", "public"."connexx_shipments"."ddp", "public"."connexx_shipments"."should_pick_up", "public"."connexx_shipments"."pick_up_from_date_local_time", "public"."connexx_shipments"."pick_up_to_date_local_time", "public"."connexx_shipments"."pick_up_instructions", "public"."connexx_shipments"."carrier_api_request_json", "public"."connexx_shipments"."carrier_api_request", "public"."connexx_shipments"."original_shipping_whitelist_id", "public"."connexx_shipments"."uploaded_commercial_invoice_base64", "public"."connexx_shipments"."ioss_number", "public"."connexx_shipments"."future_label_date", "public"."connexx_shipments"."uploaded_proforma_invoice_base64", "public"."connexx_shipments"."uploaded_customs_declaration_base64", "public"."connexx_shipments"."uploaded_certificate_of_origin_base64", "public"."connexx_shipments"."uploaded_packing_listadr_base64", "public"."connexx_shipments"."uploaded_bank_documents_base64", "public"."connexx_shipments"."uploaded_dg_goods_declaration_base64", "public"."connexx_shipments"."uploaded_other_documents_base64", "public"."connexx_shipments"."uploaded_pickup_order_base64", "public"."connexx_shipments"."uploaded_export_declaration_base64", "public"."connexx_shipments"."uploaded_export_attachment_base64", "public"."connexx_shipments"."display_address_line_3", "public"."connexx_shipments"."discount_amount_multiplied", "public"."connexx_shipments"."shipment_source_name", "public"."connexx_shipments"."shipment_integration_id", "public"."connexx_shipments"."api_id", "public"."connexx_shipments"."ups_pickup_prn_number", "public"."connexx_shipments"."created_date_date", "public"."connexx_shipments"."dangerous_goods", "public"."connexx_shipments"."estimated_delivery_date", "public"."connexx_shipments"."packing_slip_base64", "public"."connexx_shipments"."should_split_duty_and_vat", "public"."connexx_shipments"."fedex_pickup_prn_number", "public"."connexx_shipments"."shipment_info", "public"."connexx_shipments"."integration_store_id", "public"."connexx_shipments"."store_integration_id", "public"."connexx_shipments"."dropoff_info", "public"."connexx_shipments"."ups_pickup_request_body", "public"."connexx_shipments"."fedex_pickup_location", "public"."connexx_shipments"."historic_prns", "public"."connexx_shipments"."cn22_base64", "public"."connexx_shipments"."customer_reference_2", "public"."connexx_shipments"."custom_shipment_data", "public"."connexx_shipments"."ukims_number", "public"."connexx_shipments"."delivery_instructions", "public"."connexx_shipments"."integration_created_date", "public"."connexx_shipments"."importer_address_first_name", "public"."connexx_shipments"."importer_address_last_name", "public"."connexx_shipments"."importer_address_company", "public"."connexx_shipments"."importer_address_email", "public"."connexx_shipments"."importer_address_phone", "public"."connexx_shipments"."importer_address_street_1", "public"."connexx_shipments"."importer_address_street_2", "public"."connexx_shipments"."importer_address_street_3", "public"."connexx_shipments"."importer_address_city", "public"."connexx_shipments"."importer_address_county_state", "public"."connexx_shipments"."importer_address_country_iso", "public"."connexx_shipments"."importer_address_zip", "public"."connexx_shipments"."importer_address_eori_number", "public"."connexx_shipments"."importer_address_vat_number", "public"."connexx_shipments"."should_use_importer_of_record", "public"."connexx_shipments"."requires_signature", "public"."connexx_shipments"."requires_adult_signature", "public"."connexx_shipments"."requires_direct_delivery", "public"."connexx_shipments"."deliver_to_residential", "public"."connexx_shipments"."notification_emails" FROM "public"."connexx_shipments" LEFT JOIN "public"."portal_user" AS "j3" ON ("j3"."id") = ("public"."connexx_shipments"."created_by") LEFT JOIN "public"."connexx_shipment_bulk_uploads" AS "j4" ON ("j4"."id") = ("public"."connexx_shipments"."shipment_bulk_upload_id") LEFT JOIN "public"."sales_account_shipping_whitelist" AS "j5" ON ("j5"."id") = ("public"."connexx_shipments"."shipping_whitelist_id") WHERE ("public"."connexx_shipments"."sales_account_id" = $1 AND "public"."connexx_shipments"."shipment_type" = $2 AND ("public"."connexx_shipments"."customer_reference" ILIKE $3 OR "public"."connexx_shipments"."customer_reference_2" ILIKE $4 OR "public"."connexx_shipments"."order_number" ILIKE $5 OR "public"."connexx_shipments"."from_address_company" ILIKE $6 OR "public"."connexx_shipments"."from_address_first_name" ILIKE $7 OR "public"."connexx_shipments"."from_address_last_name" ILIKE $8 OR "public"."connexx_shipments"."from_address_street_1" ILIKE $9 OR "public"."connexx_shipments"."from_address_zip" ILIKE $10 OR "public"."connexx_shipments"."to_address_company" ILIKE $11 OR "public"."connexx_shipments"."to_address_first_name" ILIKE $12 OR "public"."connexx_shipments"."to_address_last_name" ILIKE $13 OR "public"."connexx_shipments"."to_address_street_1" ILIKE $14 OR "public"."connexx_shipments"."to_address_zip" ILIKE $15 OR "public"."connexx_shipments"."estimated_delivery_date" ILIKE $16 OR ("public"."connexx_shipments"."id") IN (SELECT "t1"."shipment_id" FROM "public"."connexx_shipment_packages" AS "t1" WHERE ("t1"."tracking_code" ILIKE $17 AND "t1"."shipment_id" IS NOT NULL)) OR ("public"."connexx_shipments"."id") IN (SELECT "t2"."shipment_id" FROM "public"."connexx_shipment_item_descriptions" AS "t2" WHERE (("t2"."sku" ILIKE $18 OR "t2"."description" ILIKE $19) AND "t2"."shipment_id" IS NOT NULL)) OR ("j3"."email" ILIKE $20 AND ("j3"."id" IS NOT NULL)) OR ("j4"."filename" ILIKE $21 AND ("j4"."id" IS NOT NULL)) OR (("j5"."carrier_name" ILIKE $22 OR "j5"."service_name" ILIKE $23) AND ("j5"."id" IS NOT NULL))) AND "public"."connexx_shipments"."created_date" >= $24 AND "public"."connexx_shipments"."created_date" <= $25 AND "public"."connexx_shipments"."deleted_date" IS NULL AND "public"."connexx_shipments"."status" IN ($26)) ORDER BY "public"."connexx_shipments"."created_date" DESC LIMIT $27 OFFSET $28
770 min 12% 426 ms 108,512 upadmin 
SELECT COUNT(*), "public"."connexx_shipments"."status" FROM "public"."connexx_shipments" LEFT JOIN "public"."portal_user" AS "j3" ON ("j3"."id") = ("public"."connexx_shipments"."created_by") LEFT JOIN "public"."connexx_shipment_bulk_uploads" AS "j4" ON ("j4"."id") = ("public"."connexx_shipments"."shipment_bulk_upload_id") LEFT JOIN "public"."sales_account_shipping_whitelist" AS "j5" ON ("j5"."id") = ("public"."connexx_shipments"."shipping_whitelist_id") WHERE ("public"."connexx_shipments"."sales_account_id" = $1 AND "public"."connexx_shipments"."shipment_type" = $2 AND ("public"."connexx_shipments"."customer_reference" ILIKE $3 OR "public"."connexx_shipments"."customer_reference_2" ILIKE $4 OR "public"."connexx_shipments"."order_number" ILIKE $5 OR "public"."connexx_shipments"."from_address_company" ILIKE $6 OR "public"."connexx_shipments"."from_address_first_name" ILIKE $7 OR "public"."connexx_shipments"."from_address_last_name" ILIKE $8 OR "public"."connexx_shipments"."from_address_street_1" ILIKE $9 OR "public"."connexx_shipments"."from_address_zip" ILIKE $10 OR "public"."connexx_shipments"."to_address_company" ILIKE $11 OR "public"."connexx_shipments"."to_address_first_name" ILIKE $12 OR "public"."connexx_shipments"."to_address_last_name" ILIKE $13 OR "public"."connexx_shipments"."to_address_street_1" ILIKE $14 OR "public"."connexx_shipments"."to_address_zip" ILIKE $15 OR "public"."connexx_shipments"."estimated_delivery_date" ILIKE $16 OR ("public"."connexx_shipments"."id") IN (SELECT "t1"."shipment_id" FROM "public"."connexx_shipment_packages" AS "t1" WHERE ("t1"."tracking_code" ILIKE $17 AND "t1"."shipment_id" IS NOT NULL)) OR ("public"."connexx_shipments"."id") IN (SELECT "t2"."shipment_id" FROM "public"."connexx_shipment_item_descriptions" AS "t2" WHERE (("t2"."sku" ILIKE $18 OR "t2"."description" ILIKE $19) AND "t2"."shipment_id" IS NOT NULL)) OR ("j3"."email" ILIKE $20 AND ("j3"."id" IS NOT NULL)) OR ("j4"."filename" ILIKE $21 AND ("j4"."id" IS NOT NULL)) OR (("j5"."carrier_name" ILIKE $22 OR "j5"."service_name" ILIKE $23) AND ("j5"."id" IS NOT NULL))) AND "public"."connexx_shipments"."created_date" >= $24 AND "public"."connexx_shipments"."created_date" <= $25) GROUP BY "public"."connexx_shipments"."status" OFFSET $26
565 min 9% 344 ms 98,718 upadmin 
SELECT "public"."connexx_shipments"."id" FROM "public"."connexx_shipments" LEFT JOIN "public"."portal_user" AS "j3" ON ("j3"."id") = ("public"."connexx_shipments"."created_by") LEFT JOIN "public"."connexx_shipment_bulk_uploads" AS "j4" ON ("j4"."id") = ("public"."connexx_shipments"."shipment_bulk_upload_id") LEFT JOIN "public"."sales_account_shipping_whitelist" AS "j5" ON ("j5"."id") = ("public"."connexx_shipments"."shipping_whitelist_id") WHERE ("public"."connexx_shipments"."sales_account_id" = $1 AND "public"."connexx_shipments"."shipment_type" = $2 AND ("public"."connexx_shipments"."customer_reference" ILIKE $3 OR "public"."connexx_shipments"."customer_reference_2" ILIKE $4 OR "public"."connexx_shipments"."order_number" ILIKE $5 OR "public"."connexx_shipments"."from_address_company" ILIKE $6 OR "public"."connexx_shipments"."from_address_first_name" ILIKE $7 OR "public"."connexx_shipments"."from_address_last_name" ILIKE $8 OR "public"."connexx_shipments"."from_address_street_1" ILIKE $9 OR "public"."connexx_shipments"."from_address_zip" ILIKE $10 OR "public"."connexx_shipments"."to_address_company" ILIKE $11 OR "public"."connexx_shipments"."to_address_first_name" ILIKE $12 OR "public"."connexx_shipments"."to_address_last_name" ILIKE $13 OR "public"."connexx_shipments"."to_address_street_1" ILIKE $14 OR "public"."connexx_shipments"."to_address_zip" ILIKE $15 OR "public"."connexx_shipments"."estimated_delivery_date" ILIKE $16 OR ("public"."connexx_shipments"."id") IN (SELECT "t1"."shipment_id" FROM "public"."connexx_shipment_packages" AS "t1" WHERE ("t1"."tracking_code" ILIKE $17 AND "t1"."shipment_id" IS NOT NULL)) OR ("public"."connexx_shipments"."id") IN (SELECT "t2"."shipment_id" FROM "public"."connexx_shipment_item_descriptions" AS "t2" WHERE (("t2"."sku" ILIKE $18 OR "t2"."description" ILIKE $19) AND "t2"."shipment_id" IS NOT NULL)) OR ("j3"."email" ILIKE $20 AND ("j3"."id" IS NOT NULL)) OR ("j4"."filename" ILIKE $21 AND ("j4"."id" IS NOT NULL)) OR (("j5"."carrier_name" ILIKE $22 OR "j5"."service_name" ILIKE $23) AND ("j5"."id" IS NOT NULL))) AND "public"."connexx_shipments"."created_date" >= $24 AND "public"."connexx_shipments"."created_date" <= $25 AND "public"."connexx_shipments"."deleted_date" IS NULL AND "public"."connexx_shipments"."status" IN ($26)) ORDER BY "public"."connexx_shipments"."created_date" DESC OFFSET $27
499 min 8% 4,709 ms 6,364 upadmin 
INSERT INTO shipment_data.flattened_shipments (
    shipment_package_id, shipment_id, sales_account_id,
    sales_account_reference, company_id, customer_name, tracking_code,
    status, created_date, shipment_source_name, supplier_name,
    shipping_whitelist_id,
    iso_country_origin,
    iso_country_destination,
    zip_origin,
    zip_destination
)
SELECT
    csp.id AS shipment_package_id,
    cs.id  AS shipment_id,
    sa.id  AS sales_account_id,
    sa.sales_account_reference,
    co.id  AS company_id,
    UPPER(co.name) AS customer_name,
    csp.tracking_code,
    UPPER(cs.status),
    cs.created_date,
    UPPER(cs.shipment_source_name) AS shipment_source_name,
    UPPER(combined_suppliers.supplier_name) AS supplier_name,
    cs.shipping_whitelist_id,
    cs.from_address_country_iso,
    cs.to_address_country_iso,
    cs.from_address_zip,
    cs.to_address_zip
FROM
    public.connexx_shipment_packages csp
    LEFT JOIN public.connexx_shipments cs ON cs.id = csp.shipment_id
    LEFT JOIN public.sales_account sa ON sa.id = cs.sales_account_id
    LEFT JOIN public.company co ON co.id = sa.company_id
    LEFT JOIN public.sales_account_shipping_whitelist sasw ON sasw.id = cs.shipping_whitelist_id
    LEFT JOIN public.combined_suppliers ON combined_suppliers.id = sasw.supplier_id
WHERE
    csp.tracking_code IS NOT NULL
    AND upper(cs.status) <> $1
    AND csp.created_date::date = current_date
    AND csp.sales_account_id <> $2
    AND csp.sales_account_id <> $3
    AND NOT EXISTS (
        SELECT $4
        FROM shipment_data.flattened_shipments fs
        WHERE fs.shipment_package_id = csp.id
    )
238 min 4% 71 ms 200,228 upadmin 
SELECT COUNT(*), "public"."connexx_label_jobs"."downloaded" FROM "public"."connexx_label_jobs" WHERE "public"."connexx_label_jobs"."sales_account_id" = $1 GROUP BY "public"."connexx_label_jobs"."downloaded" OFFSET $2
Covered by index on (sales_account_id, created_date)
Rows: 11543704
Row progression: 11543704, 22814

Row estimates
- sales_account_id (=): 22814

Existing indexes
- id PRIMARY
- deprecated_shipment_ids
- sales_account_id, created_date
- uuid UNIQUE
183 min 3% 61 ms 179,280 upadmin 
SELECT *
        FROM get_quotes_v5(current_date,
          cast($1 as int),
          cast($2 as int),
          cast($3 as int),
          $4,
          $5,
          $6,
          $7,
          cast($8 as int),
          cast($9 as int) 
        )
        WHERE id IS NOT NULL
        ORDER BY carrier_name,
          supplier_name,
          service_name
144 min 2% 74,478 ms 116 postgresitd 
WITH ci_imports AS (SELECT sales_account_id, service, manifest_date::date, to_char(sales_account_id, $1) || $2 || carrier || $3 || service || $4 || to_char(manifest_date::date, $5) AS cis_key, count(*)                                 AS consignment_count FROM consignment_imports WHERE source = $6 group by 1, 2, carrier, 3) UPDATE consignment_imports_summary cis SET number_of_consignments = ci_imports.consignment_count FROM ci_imports WHERE ci_imports.cis_key = cis.cis_key
Covered by index on (cis_key)
Rows: 1123805
Row progression: 1123805, 1

Row estimates
- cis_key (=): 1

Existing indexes
- id PRIMARY
- carrier_name, sales_account_id, print_date
- cis_key UNIQUE
142 min 2% 1,343 ms 6,364 upadmin 
UPDATE shipment_data.flattened_shipments fs
SET status = UPPER(cs.status)
FROM public.connexx_shipments cs
WHERE fs.shipment_id = cs.id
  AND fs.created_date::date = current_date
  AND fs.status <> cs.status
140 min 2% 42 ms 200,228 upadmin 
SELECT COUNT(*) FROM (SELECT "public"."connexx_label_jobs"."id" FROM "public"."connexx_label_jobs" WHERE ("public"."connexx_label_jobs"."sales_account_id" = $1 AND "public"."connexx_label_jobs"."status" <> $2) OFFSET $3) AS "sub"
106 min 2% 61 ms 103,738 upadmin 
SELECT "public"."connexx_shipment_packages"."id", "public"."connexx_shipment_packages"."created_by", "public"."connexx_shipment_packages"."created_date", "public"."connexx_shipment_packages"."last_modified_by", "public"."connexx_shipment_packages"."last_modified_date", "public"."connexx_shipment_packages"."shipment_id", "public"."connexx_shipment_packages"."weight", "public"."connexx_shipment_packages"."length", "public"."connexx_shipment_packages"."height", "public"."connexx_shipment_packages"."type", "public"."connexx_shipment_packages"."quantity", "public"."connexx_shipment_packages"."width", "public"."connexx_shipment_packages"."tracking_code", "public"."connexx_shipment_packages"."label_created_date", "public"."connexx_shipment_packages"."label_url", "public"."connexx_shipment_packages"."label_format", "public"."connexx_shipment_packages"."errors", "public"."connexx_shipment_packages"."index", "public"."connexx_shipment_packages"."sales_account_id", "public"."connexx_shipment_packages"."base64_pdf", "public"."connexx_shipment_packages"."shipping_price", "public"."connexx_shipment_packages"."base64_zpl", "public"."connexx_shipment_packages"."original_ui_index", "public"."connexx_shipment_packages"."created_date_date", "public"."connexx_shipment_packages"."year", "public"."connexx_shipment_packages"."month", "public"."connexx_shipment_packages"."class", "public"."connexx_shipment_packages"."dg_net_weight", "public"."connexx_shipment_packages"."dgun_number", "public"."connexx_shipment_packages"."dry_ice_weight", "public"."connexx_shipment_packages"."lithium_category", "public"."connexx_shipment_packages"."packing_group", "public"."connexx_shipment_packages"."dg_package", "public"."connexx_shipment_packages"."carrier_shipment_package_id", "public"."connexx_shipment_packages"."dg_volume_ml_100" FROM "public"."connexx_shipment_packages" LEFT JOIN "public"."connexx_shipments" AS "j1" ON ("j1"."id") = ("public"."connexx_shipment_packages"."shipment_id") WHERE ("public"."connexx_shipment_packages"."tracking_code" IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$82,$83,$84,$85,$86,$87,$88,$89,$90,$91,$92,$93,$94,$95,$96,$97,$98,$99,$100) AND "public"."connexx_shipment_packages"."sales_account_id" IN ($101,$102,$103,$104,$105,$106,$107,$108,$109,$110,$111,$112,$113,$114,$115,$116,$117,$118,$119,$120,$121,$122,$123,$124,$125,$126,$127,$128,$129,$130,$131,$132,$133,$134,$135,$136,$137,$138,$139,$140,$141,$142,$143,$144,$145,$146,$147,$148,$149,$150,$151,$152,$153,$154,$155,$156,$157,$158,$159,$160,$161,$162,$163,$164,$165,$166,$167,$168,$169,$170,$171,$172,$173,$174,$175,$176,$177,$178,$179,$180,$181,$182,$183,$184,$185,$186,$187,$188,$189,$190,$191,$192,$193,$194,$195) AND ("j1"."shipping_whitelist_id" IS NOT NULL AND ("j1"."id" IS NOT NULL))) OFFSET $196
77 min 1% 26 ms 174,399 upadmin 
SELECT COUNT(*) FROM (SELECT "public"."connexx_shipments"."id" FROM "public"."connexx_shipments" WHERE ("public"."connexx_shipments"."sales_account_id" = $1 AND "public"."connexx_shipments"."shipment_type" = $2 AND "public"."connexx_shipments"."deleted_date" IS NULL) OFFSET $3) AS "sub"
33 min 0.5% 4,493 ms 443 postgresitd 
SELECT $4, ukesi.id, ukesi.status, ukesi.created_date, ukea.name, ukes.name, ukesi.supplier_invoice_number, ukesi.description, $5                                                      as invoice_type, COALESCE((SUM(COALESCE(ukec.supplier_cost_price, $6)) FILTER (WHERE ukec.exception_code != $7)), $8) + COALESCE((SELECT SUM(COALESCE(ukecs.supplier_cost_price, $9)) FROM uk_ecommerce_consignment_surcharges ukecs WHERE ukecs.supplier_linked_invoice_id = ukesi.id AND ukecs.exception_code != $10), $11) AS supplier_freight_exceptions, $12, COALESCE((SUM(COALESCE(ukec.sales_price, $13)) FILTER (WHERE ukec.exception_code <> $14)), $15) + COALESCE((SUM(COALESCE(ukec.surcharge_sales_price, $16)) FILTER (WHERE ukec.exception_code <> $17)), $18) + COALESCE((SELECT SUM(COALESCE(ukecs.sales_price, $19)) FROM uk_ecommerce_consignment_surcharges ukecs WHERE ukecs.supplier_linked_invoice_id = ukesi.id AND ukecs.exception_code != $20), $21) + COALESCE((SELECT SUM(t1.sales_fuel) ::bigint FROM (SELECT (SUM(COALESCE(sales_price, $22))::bigint + SUM(COALESCE(surcharge_sales_price, $23))::bigint) * COALESCE(sales_fuel_surcharge_percent, $24) / $25::bigint AS sales_fuel FROM uk_ecommerce_consignments WHERE supplier_linked_invoice_id = ukec.supplier_linked_invoice_id AND uk_ecommerce_consignments.exception_code != $26 GROUP BY sales_fuel_surcharge_percent ORDER BY sales_fuel_surcharge_percent) t1), $27) AS sales_freight_exceptions, COALESCE((SUM(COALESCE(ukec.supplier_cost_price, $28)) FILTER (WHERE ukec.exception_code = $29)), $30) + COALESCE((SELECT SUM(COALESCE(ukecs.supplier_cost_price, $31)) FROM uk_ecommerce_consignment_surcharges ukecs WHERE ukecs.supplier_linked_invoice_id = ukesi.id AND ukecs.exception_code = $32), $33)  AS supplier_freight, $34, COALESCE((SUM(COALESCE(ukec.sales_price, $35)) FILTER (WHERE ukec.exception_code = $36)), $37) + COALESCE((SUM(COALESCE(ukec.surcharge_sales_price, $38)) FILTER (WHERE ukec.exception_code = $39)), $40) + COALESCE((SELECT SUM(COALESCE(ukecs.sales_price, $41)) FROM uk_ecommerce_consignment_surcharges ukecs WHERE ukecs.supplier_linked_invoice_id = ukesi.id AND ukecs.exception_code = $42), $43) + COALESCE((SELECT SUM(t1.sales_fuel) ::bigint FROM (SELECT (SUM(COALESCE(sales_price, $44))::bigint + SUM(COALESCE(surcharge_sales_price, $45))::bigint) * COALESCE(sales_fuel_surcharge_percent, $46) / $47::bigint AS sales_fuel FROM uk_ecommerce_consignments WHERE supplier_linked_invoice_id = ukec.supplier_linked_invoice_id AND uk_ecommerce_consignments.exception_code = $48 GROUP BY sales_fuel_surcharge_percent ORDER BY sales_fuel_surcharge_percent) t1), $49) AS sales_freight, COALESCE(ukesi.purchase_transaction_id, $50)              AS purchase_transaction_id, COALESCE(ukes.purchase_account_id, $51)                   AS purchase_account_id, COALESCE(CONCAT(pa.purchase_account_reference, $52, RTRIM(company.name)), $53)        AS purchase_account_name, COALESCE(confirmed_for_invoicing, $54)                AS confirmed_for_invoicing FROM uk_ecommerce_supplier_invoice ukesi LEFT JOIN uk_ecommerce_supplier ukes ON ukes.id = ukesi.supplier_account_id LEFT JOIN uk_ecommerce_carrier ukea ON ukea.id = ukes.carrier_id LEFT JOIN uk_ecommerce_consignments ukec ON ukec.supplier_linked_invoice_id = ukesi.id LEFT JOIN purchase_account pa ON pa.id = ukes.purchase_account_id LEFT JOIN company ON company.id = pa.company_id WHERE ukesi.created_date::date BETWEEN $1 AND $2 AND ukesi.status <> $55 AND ukesi.financial_company = $3 GROUP BY 4, 5, 6, 7, 8, 2, 16, ukec.supplier_linked_invoice_id, ukesi.status, ukes.purchase_account_id, pa.purchase_account_reference, company.name, confirmed_for_invoicing ORDER BY 4 DESC, 2 DESC
29 min 0.5% 10,425 ms 165 postgresitd 
WITH returned_data AS (SELECT rc.awb                                  AS outgoing_barcode, rc.source_table                         AS parent_table, rc.source_table_id                      AS parent_table_id, $2                                      AS return_match, $3                                      AS customer_name, $4                                      AS trading_name, $5                                       AS sales_account_id, $6                                      AS sales_account_reference, $7                                      AS ship_to_postcode, $8                                      AS carrier, $9                                      AS dom_intl, $10                                       AS carrier_id, $11                                      AS depot, $12                                      AS return_method, $13                                      AS shipper_reference, $14                                      AS returns_address, COALESCE(rc.created_date, $15) AS return_received_date, COALESCE(rc.created_by, $16)             AS return_received_by FROM returns_consignment rc WHERE rc.awb = $1), uk_billed AS (SELECT DISTINCT ukec.barcode                                 AS outgoing_barcode, $17                  AS parent_table, ukec.id::bigint                              AS parent_table_id, $18                                           AS return_match, COALESCE(RTRIM(company.name), $19)            AS customer_name, COALESCE(sa.trade_name, $20)                  AS trading_name, COALESCE(ukec.sales_account_id, $21)           AS sales_account_id, COALESCE(ukec.sales_account_reference, $22)   AS sales_account_reference, COALESCE(ship_to_postcode, $23)               AS ship_to_postcode, COALESCE(ukcar.name, $24)                     AS carrier, $25                                        AS dom_intl, COALESCE(ukcar.id, $26)                        AS carrier_id, CASE WHEN carrier_cd.name IS NOT NULL AND carrier_cd.name <> $27 THEN carrier_cd.name ELSE COALESCE(general_cd.name, $28) END                                      AS depot, COALESCE(sa.return_method, $29)               AS return_method, COALESCE(shipper_reference_1, $30)            AS shipper_reference, regexp_replace(COALESCE(aan.address_line_1, $31) || chr($32) || COALESCE(aan.address_line_2, $33) || chr($34) || COALESCE(aan.address_line_3, $35) || chr($36) || COALESCE(aan.city, $37) || chr($38) || COALESCE(aan.county_state, $39) || chr($40) || COALESCE(aan.postcode, $41) || chr($42) || COALESCE(aan.country, $43), $44, $45, $46)                          AS returns_address, COALESCE(return_received_date, $47) AS return_received_date, COALESCE(return_received_by, $48)             AS return_received_by FROM uk_ecommerce_consignments ukec LEFT JOIN uk_ecommerce_service ukes ON ukec.service_id = ukes.id LEFT JOIN uk_ecommerce_carrier ukcar ON ukes.carrier_id = ukcar.id LEFT JOIN nominal_transaction_lines ntl ON ntl.referring_id = ukec.id LEFT JOIN sales_account_transactions sat ON sat.id = ntl.sales_transaction_id LEFT JOIN sales_account sa ON sa.id = ukec.sales_account_id LEFT JOIN company ON company.id = sa.company_id LEFT JOIN account_address_new aan ON aan.company_id = company.id AND aan.returns_address = $49 AND aan.status = $50 LEFT JOIN sales_account_collection_types carrier_sact ON sa.id = carrier_sact.sales_account_id AND carrier_sact.domestic_international = $51 AND carrier_sact.carrier_id = ukcar.id AND carrier_sact.collected_by = $52 AND carrier_sact.status = $53 LEFT JOIN sales_account_collection_types general_sact ON sa.id = general_sact.sales_account_id AND general_sact.collected_by = $54 AND general_sact.status = $55 LEFT JOIN carrier_depots carrier_cd ON carrier_sact.depot_id = carrier_cd.id LEFT JOIN carrier_depots general_cd ON general_sact.depot_id = general_cd.id WHERE ukec.barcode = $1), intl_billed AS (SELECT DISTINCT ON (ic.consignment_number) COALESCE(ic.barcode, ic.consignment_number)     AS outgoing_barcode, $56                             AS parent_table, ukec.id::bigint                                 AS parent_table_id, $57                                              AS return_match, COALESCE(RTRIM(company.name), $58)               AS customer_name, COALESCE(sa.trade_name, $59)                     AS trading_name, COALESCE(sa.id, $60)                              AS sales_account_id, COALESCE(sa.sales_account_reference, $61)        AS sales_account_reference, COALESCE(ic.ship_to_postcode, $62)               AS ship_to_postcode, COALESCE(i_carrier.name, $63)                    AS carrier, $64                                          AS dom_intl, COALESCE(i_carrier.id, $65)                       AS carrier_id, CASE WHEN carrier_cd.name IS NOT NULL AND carrier_cd.name <> $66 THEN carrier_cd.name ELSE COALESCE(general_cd.name, $67) END                                         AS depot, COALESCE(sa.return_method, $68)                  AS return_method, COALESCE(CASE WHEN ic.shipper_reference_1 IS NOT NULL AND ic.shipper_reference_1 <> $69 THEN ic.shipper_reference_1 ELSE ic.order_number END, $70)                                    AS shipper_reference, regexp_replace(COALESCE(aan.address_line_1, $71) || chr($72) || COALESCE(aan.address_line_2, $73) || chr($74) || COALESCE(aan.address_line_3, $75) || chr($76) || COALESCE(aan.city, $77) || chr($78) || COALESCE(aan.county_state, $79) || chr($80) || COALESCE(aan.postcode, $81) || chr($82) || COALESCE(aan.country, $83), $84, $85, $86)                             AS returns_address, COALESCE(ic.return_received_date, $87) AS return_received_date, COALESCE(ic.return_received_by, $88)             AS return_received_by FROM intl_consignments_surcharges_transactions ukec LEFT JOIN intl_consignments ic ON ic.id = ukec.consignment_id LEFT JOIN intl_service i_service ON i_service.id = ic.service_id LEFT JOIN intl_carrier i_carrier ON i_carrier.id = i_service.carrier_id LEFT JOIN sales_account sa ON sa.id = ukec.sales_account_id LEFT JOIN company ON company.id = sa.company_id LEFT JOIN account_address_new aan ON aan.company_id = sa.company_id AND aan.returns_address = $89 AND aan.status = $90 LEFT JOIN sales_account_collection_types carrier_sact ON sa.id = carrier_sact.sales_account_id AND carrier_sact.domestic_international = $91 AND carrier_sact.carrier_id = i_carrier.id AND carrier_sact.collected_by = $92 AND carrier_sact.status = $93 LEFT JOIN sales_account_collection_types general_sact ON sa.id = general_sact.sales_account_id AND general_sact.collected_by = $94 AND general_sact.status = $95 LEFT JOIN carrier_depots carrier_cd ON carrier_sact.depot_id = carrier_cd.id LEFT JOIN carrier_depots general_cd ON general_sact.depot_id = general_cd.id WHERE (ic.barcode = $1 OR ic.consignment_number = $1)), imports AS (SELECT DISTINCT ukec.barcode                                 AS outgoing_barcode, $96                        AS parent_table, ukec.id::bigint                              AS parent_table_id, $97                                           AS return_match, COALESCE(RTRIM(company.name), $98)            AS customer_name, COALESCE(sa.trade_name, $99)                  AS trading_name, COALESCE(ukec.sales_account_id, $100)           AS sales_account_id, COALESCE(ukec.sales_account_reference, $101)   AS sales_account_reference, COALESCE(ship_to_postcode, $102)               AS ship_to_postcode, COALESCE(carrier, $103)                        AS carrier, CASE WHEN carrier = $104 AND service = $105 THEN $106 WHEN carrier = $107 AND service <> $108 THEN $109 WHEN carrier = $110 THEN $111 ELSE $112 END                                      AS dom_intl, CASE WHEN carrier ilike $113 THEN $114 WHEN carrier ilike $115 THEN $116 WHEN carrier ilike $117 THEN $118 WHEN (carrier ilike $119 OR carrier ilike $120) THEN $121 WHEN carrier ilike $122 THEN $123 WHEN carrier ilike $124 THEN $125 WHEN carrier ilike $126 THEN $127 WHEN (carrier ilike $128 OR carrier ilike $129) THEN $130 WHEN carrier ilike $131 THEN $132 WHEN carrier ilike $133 THEN $134 WHEN carrier ilike $135 THEN $136 WHEN carrier ilike $137 THEN $138 ELSE COALESCE((SELECT id FROM intl_carrier WHERE (name = carrier OR similarity(name, carrier) > $139) AND financial_company = $140), $141) END                                      AS carrier_id, (SELECT COALESCE(COALESCE(CASE WHEN carrier = $142 AND service = $143 THEN (SELECT cd.name FROM sales_account_collection_types sact LEFT JOIN carrier_depots cd ON sact.depot_id = cd.id WHERE sact.sales_account_id = ukec.sales_account_id AND sact.domestic_international = $144 AND sact.carrier_id = $145 AND sact.collected_by = $146) WHEN carrier = $147 AND service <> $148 THEN (SELECT cd.name FROM sales_account_collection_types sact LEFT JOIN carrier_depots cd ON sact.depot_id = cd.id WHERE sact.status = $149 AND sact.sales_account_id = ukec.sales_account_id AND sact.domestic_international = $150 AND sact.carrier_id = $151 AND sact.collected_by = $152) WHEN carrier = $153 THEN (SELECT cd.name FROM sales_account_collection_types sact LEFT JOIN carrier_depots cd ON sact.depot_id = cd.id WHERE sact.status = $154 AND sact.sales_account_id = ukec.sales_account_id AND sact.domestic_international = $155 AND sact.carrier_id = $156 AND sact.collected_by = $157) WHEN carrier ilike $158 THEN (SELECT cd.name FROM sales_account_collection_types sact LEFT JOIN carrier_depots cd ON sact.depot_id = cd.id WHERE sact.status = $159 AND sact.sales_account_id = ukec.sales_account_id AND sact.domestic_international = $160 AND sact.carrier_id = $161 AND sact.collected_by = $162) WHEN (carrier ilike $163 OR carrier ilike $164) THEN (SELECT cd.name FROM sales_account_collection_types sact LEFT JOIN carrier_depots cd ON sact.depot_id = cd.id WHERE sact.status = $165 AND sact.sales_account_id = ukec.sales_account_id AND sact.domestic_international = $166 AND sact.carrier_id = $167 AND sact.collected_by = $168) WHEN carrier ilike $169 THEN (SELECT cd.name FROM sales_account_collection_types sact LEFT JOIN carrier_depots cd ON
22 min 0.3% 1,939 ms 666 upadmin 
SELECT MAX("effective_date") FROM (SELECT "public"."exchange_rates"."effective_date" FROM "public"."exchange_rates" WHERE $2=$3 OFFSET $1) AS "sub"
15 min 0.2% 41 ms 21,801 upadmin 
SELECT "public"."connexx_shipment_packages"."id", "public"."connexx_shipment_packages"."created_by", "public"."connexx_shipment_packages"."created_date", "public"."connexx_shipment_packages"."last_modified_by", "public"."connexx_shipment_packages"."last_modified_date", "public"."connexx_shipment_packages"."shipment_id", "public"."connexx_shipment_packages"."weight", "public"."connexx_shipment_packages"."length", "public"."connexx_shipment_packages"."height", "public"."connexx_shipment_packages"."type", "public"."connexx_shipment_packages"."quantity", "public"."connexx_shipment_packages"."width", "public"."connexx_shipment_packages"."tracking_code", "public"."connexx_shipment_packages"."label_created_date", "public"."connexx_shipment_packages"."label_url", "public"."connexx_shipment_packages"."label_format", "public"."connexx_shipment_packages"."errors", "public"."connexx_shipment_packages"."index", "public"."connexx_shipment_packages"."sales_account_id", "public"."connexx_shipment_packages"."base64_pdf", "public"."connexx_shipment_packages"."shipping_price", "public"."connexx_shipment_packages"."base64_zpl", "public"."connexx_shipment_packages"."original_ui_index", "public"."connexx_shipment_packages"."created_date_date", "public"."connexx_shipment_packages"."year", "public"."connexx_shipment_packages"."month", "public"."connexx_shipment_packages"."class", "public"."connexx_shipment_packages"."dg_net_weight", "public"."connexx_shipment_packages"."dgun_number", "public"."connexx_shipment_packages"."dry_ice_weight", "public"."connexx_shipment_packages"."lithium_category", "public"."connexx_shipment_packages"."packing_group", "public"."connexx_shipment_packages"."dg_package", "public"."connexx_shipment_packages"."carrier_shipment_package_id", "public"."connexx_shipment_packages"."dg_volume_ml_100" FROM "public"."connexx_shipment_packages" LEFT JOIN "public"."connexx_shipments" AS "j1" ON ("j1"."id") = ("public"."connexx_shipment_packages"."shipment_id") WHERE ("public"."connexx_shipment_packages"."tracking_code" IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50) AND "public"."connexx_shipment_packages"."shipment_id" IN ($51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$82,$83,$84,$85,$86,$87,$88,$89,$90,$91,$92,$93,$94,$95,$96,$97,$98,$99,$100) AND "public"."connexx_shipment_packages"."sales_account_id" IN ($101) AND ("j1"."shipping_whitelist_id" IS NOT NULL AND ("j1"."id" IS NOT NULL))) OFFSET $102
14 min 0.2% 70 ms 12,340 upadmin 
SELECT "public"."connexx_shipment_packages"."id", "public"."connexx_shipment_packages"."created_by", "public"."connexx_shipment_packages"."created_date", "public"."connexx_shipment_packages"."last_modified_by", "public"."connexx_shipment_packages"."last_modified_date", "public"."connexx_shipment_packages"."shipment_id", "public"."connexx_shipment_packages"."weight", "public"."connexx_shipment_packages"."length", "public"."connexx_shipment_packages"."height", "public"."connexx_shipment_packages"."type", "public"."connexx_shipment_packages"."quantity", "public"."connexx_shipment_packages"."width", "public"."connexx_shipment_packages"."tracking_code", "public"."connexx_shipment_packages"."label_created_date", "public"."connexx_shipment_packages"."label_url", "public"."connexx_shipment_packages"."label_format", "public"."connexx_shipment_packages"."errors", "public"."connexx_shipment_packages"."index", "public"."connexx_shipment_packages"."sales_account_id", "public"."connexx_shipment_packages"."base64_pdf", "public"."connexx_shipment_packages"."shipping_price", "public"."connexx_shipment_packages"."base64_zpl", "public"."connexx_shipment_packages"."original_ui_index", "public"."connexx_shipment_packages"."created_date_date", "public"."connexx_shipment_packages"."year", "public"."connexx_shipment_packages"."month", "public"."connexx_shipment_packages"."class", "public"."connexx_shipment_packages"."dg_net_weight", "public"."connexx_shipment_packages"."dgun_number", "public"."connexx_shipment_packages"."dry_ice_weight", "public"."connexx_shipment_packages"."lithium_category", "public"."connexx_shipment_packages"."packing_group", "public"."connexx_shipment_packages"."dg_package", "public"."connexx_shipment_packages"."carrier_shipment_package_id", "public"."connexx_shipment_packages"."dg_volume_ml_100" FROM "public"."connexx_shipment_packages" LEFT JOIN "public"."connexx_shipments" AS "j1" ON ("j1"."id") = ("public"."connexx_shipment_packages"."shipment_id") WHERE ("public"."connexx_shipment_packages"."tracking_code" IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26,$27,$28,$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,$43,$44,$45,$46,$47,$48,$49,$50,$51,$52,$53,$54,$55,$56,$57,$58,$59,$60,$61,$62,$63,$64,$65,$66,$67,$68,$69,$70,$71,$72,$73,$74,$75,$76,$77,$78,$79,$80,$81,$82,$83,$84,$85,$86,$87,$88,$89,$90,$91,$92,$93,$94,$95,$96,$97,$98,$99,$100) AND "public"."connexx_shipment_packages"."sales_account_id" IN ($101,$102,$103,$104,$105,$106,$107,$108) AND ("j1"."shipping_whitelist_id" IS NOT NULL AND ("j1"."id" IS NOT NULL))) OFFSET $109
11 min 0.2% 1,771 ms 365 postgresitd 
SELECT $3                                AS geo_group, $4                                   AS charge_type, INITCAP(RTRIM(company.name)), ukes.carrier_id, ukea.name                           AS carrier, ukec.package_type_id, ukept.name                          AS package, ukes.display_order                  AS display_order, $5                                  AS zone_name, $6                                   AS charged_weight, ukec.service_id, CASE WHEN next_day = $7 AND signature = $8 THEN REPLACE(CONCAT(ukes.name, $9), $10, $11) WHEN next_day = $12 AND signature = $13 THEN REPLACE(CONCAT(ukes.name, $14), $15, $16) WHEN next_day = $17 AND signature = $18 THEN CONCAT(ukes.name, $19) WHEN yodel_out_of_area = $20 THEN CONCAT(ukes.name, $21) ELSE CONCAT(ukes.name) END                             AS description, COUNT(*), ROUND(SUM(sales_price) + SUM(surcharge_sales_price)), $22, sales_price + surcharge_sales_price AS sales_price, $23, COALESCE(vat_chargeable_code, $24) FROM uk_ecommerce_consignments ukec LEFT JOIN sales_account sa ON sa.id = ukec.sales_account_id LEFT JOIN company ON company.id = sa.company_id LEFT JOIN uk_ecommerce_service ukes ON ukes.id = ukec.service_id LEFT JOIN uk_ecommerce_carrier ukea ON ukea.id = ukes.carrier_id LEFT JOIN uk_ecommerce_tariff uket ON uket.id = ukec.tariff_id LEFT JOIN uk_ecommerce_package_type ukept ON ukept.id = ukec.package_type_id WHERE ukec.sales_account_id = $1 AND ukec.exception_code = $25 AND (ukec.sales_price + ukec.surcharge_sales_price) <> $26 AND ukec.supplier_nominal_transaction_id <> $27 AND ukec.sales_nominal_transaction_id = $28 GROUP BY charge_type, ukea.name, ukept.name, ukes.display_order, ukes.name, zone_name, ukec.next_day, ukec.signature, ukec.yodel_out_of_area, ukes.carrier_id, ukec.package_type_id, ukec.service_id, company.name, sales_price + surcharge_sales_price, vat_chargeable_code UNION ALL SELECT $29               AS geo_group, $30                  AS charge_type, INITCAP(RTRIM(company.name)), ukes.carrier_id, ukea.name          AS carrier, ukec.package_type_id, ukept.name         AS package, ukes.display_order AS display_order, $31                 AS zone_name, $32                  AS charged_weight, ukec.surcharge_id, ukes.name, COUNT(*), ROUND(SUM(sales_price)), $33, sales_price        AS sales_price, $34, COALESCE(vat_chargeable_code, $35) FROM uk_ecommerce_consignment_surcharges ukec LEFT JOIN sales_account sa ON sa.id = ukec.sales_account_id LEFT JOIN company ON company.id = sa.company_id LEFT JOIN uk_ecommerce_service ukes ON ukes.id = ukec.surcharge_id LEFT JOIN uk_ecommerce_carrier ukea ON ukea.id = ukes.carrier_id LEFT JOIN uk_ecommerce_package_type ukept ON ukept.id = ukec.package_type_id WHERE ukec.sales_account_id = $1 AND ukec.exception_code = $36 AND brand <> $37 AND ukec.sales_price > $38 AND ukec.supplier_nominal_transaction_id <> $39 AND ukec.sales_nominal_transaction_id = $40 GROUP BY charge_type, ukea.name, ukept.name, ukes.display_order, ukes.name, ukes.carrier_id, ukec.package_type_id, ukec.surcharge_id, company.name, sales_price, vat_chargeable_code UNION ALL SELECT $41                               AS geo_group, $42                                    AS charge_type, INITCAP(RTRIM(company.name)), i_service.carrier_id, i_carrier.name                       AS carrier, ic.package_type_id, CASE WHEN ic.package_type_id = $43 THEN $44 WHEN ic.package_type_id = $45 THEN $46 WHEN ic.package_type_id = $47 THEN $48 ELSE $49 END                              AS package, COALESCE(i_service.display_order, $50) AS display_order, ic.zone_name                         AS zone_name, ic.charged_weight                    AS charged_weight, ic.service_id, i_service.name                       AS description, COUNT(*), SUM(icst.sales_freight_amount), $51, icst.sales_freight_amount, number_of_pieces, COALESCE(icst.sales_tax_code_id, $52) FROM intl_consignments_surcharges_transactions icst LEFT JOIN intl_consignments ic ON ic.id = icst.consignment_id LEFT JOIN sales_account sa ON sa.id = icst.sales_account_id LEFT JOIN company ON company.id = sa.company_id LEFT JOIN intl_service i_service ON i_service.id = ic.service_id LEFT JOIN intl_carrier i_carrier ON i_carrier.id = i_service.carrier_id LEFT JOIN intl_tariff uket ON uket.id = ic.tariff_id WHERE icst.sales_account_id = $1 AND icst.financial_company = $2 AND icst.transaction_type = $53 AND icst.sales_exception_code = $54 AND icst.sales_nominal_transaction_id = $55 AND icst.sales_freight_amount <> $56 AND icst.supplier_nominal_transaction_id <> $57 AND icst.consignment_surcharge_id = $58 AND icst.consignment_vat_duty_id = $59 AND i_carrier.eu = $60 GROUP BY charge_type, i_carrier.name, i_service.display_order, ic.zone_name, ic.charged_weight, i_service.name, i_service.carrier_id, ic.package_type_id, ic.service_id, company.name, icst.sales_freight_amount, number_of_pieces, icst.sales_tax_code_id UNION ALL SELECT $61                      AS geo_group, $62                           AS charge_type, INITCAP(RTRIM(company.name)), i_service.carrier_id, i_carrier.name              AS carrier, ics.package_type_id, $63                          AS package, i_service.display_order     AS display_order, COALESCE(ics.zone_name, $64) AS zone_name, $65                           AS charged_weight, ics.surcharge_id, i_service.name, COUNT(*), ROUND(SUM(icst.sales_freight_amount)), $66, icst.sales_freight_amount   AS sales_price, $67, COALESCE(icst.sales_tax_code_id, $68) FROM intl_consignments_surcharges_transactions icst LEFT JOIN intl_consignment_surcharges ics ON ics.id = icst.consignment_surcharge_id LEFT JOIN sales_account sa ON sa.id = icst.sales_account_id LEFT JOIN company ON company.id = sa.company_id LEFT JOIN intl_service i_service ON i_service.id = ics.surcharge_id LEFT JOIN intl_carrier i_carrier ON i_carrier.id = i_service.carrier_id WHERE icst.sales_account_id = $1 AND icst.financial_company = $2 AND ics.brand <> $69 AND icst.sales_exception_code = $70 AND icst.sales_freight_amount <> $71 AND icst.supplier_nominal_transaction_id <> $72 AND icst.sales_nominal_transaction_id = $73 AND icst.consignment_surcharge_id <> $74 AND icst.consignment_vat_duty_id = $75 AND i_carrier.eu = $76 GROUP BY charge_type, i_carrier.name, i_service.display_order, ics.zone_name, i_service.name, i_service.carrier_id, ics.package_type_id, ics.surcharge_id, company.name, icst.sales_freight_amount, icst.sales_tax_code_id ORDER BY charge_type, carrier, package, display_order, zone_name, charged_weight
11 min 0.2% 5,511 ms 117 postgresitd 
WITH docs AS ( SELECT docs.linked_id as id, jsonb_agg(docs.*) AS d_json FROM (SELECT id, filename, CAST((links ->> $3) AS integer) as linked_id FROM documents WHERE (links ->> $4)::text = $5 AND status = $6 AND (links ->> $7) <> $8) docs GROUP BY docs.linked_id), claims_cte AS (SELECT c.id, COALESCE(c.is_resolved, $9), c.created_date, c.created_by, c.barcode, COALESCE(c.evri_claim_ref, $10), c.claim_status, COALESCE(sat.transaction_reference, $11), COALESCE(c.cost_net_credit, $12), COALESCE(c.cost_total_credit, $13), COALESCE(c.sales_net_credit, $14), COALESCE(c.sales_total_credit, $15), COALESCE(c.order_ref, $16), COALESCE(c.order_type, $17)      as order_type, COALESCE(c.claim_reference, $18), COALESCE(c.recipient_name, $19), COALESCE(c.recipient_postcode, $20), COALESCE(c.lost_or_damaged, $21), COALESCE(c.dor_date, $22), COALESCE(c.cust_cont_name, $23), COALESCE(c.cust_cont_number, $24), COALESCE(c.cust_cont_email, $25), COALESCE(c.internal_ticket_number, $26), COALESCE(c.quantity_being_claimed, $27), COALESCE(c.product_category, $28), COALESCE(c.product_description, $29), COALESCE(c.cost_value, $30), COALESCE(c.retail_value, $31), COALESCE(c.supplier_claim_code, $32), COALESCE(c.appeal_comment, $33), COALESCE(ce.evri_file_ref, $34), COALESCE(c.carrier_id, $35)       as carrier_id, COALESCE(c.service_id, $36)       as service_id, COALESCE(c.sales_account_id, $37) as sales_account_id, CASE WHEN COALESCE(appeal, $38) = $39 THEN $40 ELSE $41 END, COALESCE(CASE WHEN COALESCE(appeal, $42) = $43 AND c.sales_credit_note IS NOT NULL THEN array_agg(COALESCE(e_pat.transaction_reference, $44)) ELSE array_agg(COALESCE(a_pat.transaction_reference, $45)) END, $46), COALESCE(CASE WHEN COALESCE(appeal, $47) = $48 AND c.sales_credit_note IS NOT NULL THEN array_agg(COALESCE(e_pat.supplier_invoice_reference, $49)) ELSE array_agg(COALESCE(a_pat.supplier_invoice_reference, $50)) END, $51) FROM claims c LEFT JOIN sales_account_transactions sat ON sat.transaction_reference = c.sales_credit_note LEFT JOIN claim_exports ce on c.export = ce.id LEFT JOIN claim_exports ca on c.appeal = ca.id LEFT JOIN purchase_account_transactions e_pat ON e_pat.id = ANY (ARRAY[ce.purchase_credit_notes]) LEFT JOIN purchase_account_transactions a_pat ON a_pat.id = ANY (ARRAY[ca.purchase_credit_notes]) WHERE c.status = $52 AND c.created_date::date BETWEEN $1 AND $2 group by c.id, sat.transaction_reference, ce.evri_file_ref) SELECT c.*, COALESCE(co.name, $53), COALESCE(sa.sales_account_reference, $54), COALESCE(inc.name, $55), COALESCE(ins.name, $56), $57, $58, COALESCE(d.d_json, $59) FROM claims_cte c LEFT JOIN intl_carrier inc ON c.carrier_id = inc.id LEFT JOIN intl_service ins ON c.service_id = ins.id LEFT JOIN sales_account sa ON c.sales_account_id = sa.id LEFT JOIN company co ON sa.company_id = co.id LEFT JOIN docs d ON d.id = c.id WHERE c.order_type = $60 UNION ALL SELECT c.*, COALESCE(co.name, $61), COALESCE(sa.sales_account_reference, $62), COALESCE(ukec.name, $63), COALESCE(ukes.name, $64), $65, $66, COALESCE(d.d_json, $67) FROM claims_cte c LEFT JOIN uk_ecommerce_carrier ukec ON c.carrier_id = ukec.id LEFT JOIN uk_ecommerce_service ukes ON c.service_id = ukes.id LEFT JOIN sales_account sa ON c.sales_account_id = sa.id LEFT JOIN company co ON sa.company_id = co.id LEFT JOIN docs d ON d.id = c.id WHERE c.order_type = $68 AND ukec.financial_company = $69 UNION ALL SELECT c.*, COALESCE(co.name, $70), COALESCE(sa.sales_account_reference, $71), COALESCE(ukc.name, $72), COALESCE(uks.name, $73), COALESCE(ukes.name, $74), COALESCE(sat.transaction_reference, $75), COALESCE(d.d_json, $76) FROM nominal_transaction_lines ntl LEFT JOIN uk_ecommerce_consignments ukec ON ukec.sales_nominal_transaction_id = ntl.id LEFT JOIN claims_cte c ON ukec.barcode = c.barcode LEFT JOIN sales_account_transactions sat ON ntl.sales_transaction_id = sat.id LEFT JOIN uk_ecommerce_supplier ukes ON ukec.supplier_account_id = ukes.id LEFT JOIN uk_ecommerce_carrier ukc ON c.carrier_id = ukc.id LEFT JOIN uk_ecommerce_service uks ON c.service_id = uks.id LEFT JOIN sales_account sa ON c.sales_account_id = sa.id LEFT JOIN company co ON sa.company_id = co.id LEFT JOIN docs d ON d.id = c.id WHERE c.order_type = $77 AND ntl.referring_table = $78 AND COALESCE(ukec.sales_nominal_transaction_id, $79) <> $80 AND ukc.financial_company = $81 UNION ALL SELECT c.*, COALESCE(co.name, $82), COALESCE(sa.sales_account_reference, $83), COALESCE(ukc.name, $84), COALESCE(uks.name, $85), COALESCE(ukes.name, $86), $87, COALESCE(d.d_json, $88) FROM claims_cte c LEFT JOIN uk_ecommerce_consignments ukec ON ukec.barcode = c.barcode LEFT JOIN uk_ecommerce_supplier ukes ON ukec.supplier_account_id = ukes.id LEFT JOIN uk_ecommerce_carrier ukc ON c.carrier_id = ukc.id LEFT JOIN uk_ecommerce_service uks ON c.service_id = uks.id LEFT JOIN sales_account sa ON c.sales_account_id = sa.id LEFT JOIN company co ON sa.company_id = co.id LEFT JOIN docs d ON d.id = c.id WHERE c.order_type = $89 AND COALESCE(ukec.sales_nominal_transaction_id, $90) = $91 AND ukc.financial_company = $92 UNION ALL SELECT c.*, COALESCE(co.name, $93), COALESCE(sa.sales_account_reference, $94), COALESCE(inc.name, $95), COALESCE(ins.name, $96), COALESCE(ints.name, $97), COALESCE(ntl.reference, $98), COALESCE(d.d_json, $99) FROM claims_cte c LEFT JOIN intl_consignments ic ON ic.barcode = c.barcode LEFT JOIN intl_consignments_surcharges_transactions icst ON ic.id = icst.consignment_id LEFT JOIN nominal_transaction_lines ntl ON icst.sales_nominal_transaction_id = ntl.id LEFT JOIN sales_account sa ON c.sales_account_id = sa.id LEFT JOIN company co ON sa.company_id = co.id LEFT JOIN intl_supplier ints ON ic.supplier_id = ints.id LEFT JOIN intl_carrier inc ON c.carrier_id = inc.id LEFT JOIN intl_service ins ON c.service_id = ins.id LEFT JOIN docs d ON d.id = c.id WHERE order_type = $100 AND ntl.referring_table = $101 AND COALESCE(icst.consignment_id, $102) <> $103 UNION ALL SELECT c.*, COALESCE(co.name, $104), COALESCE(sa.sales_account_reference, $105), COALESCE(inc.name, $106), COALESCE(ins.name, $107), $108, $109, COALESCE(d.d_json, $110) FROM claims_cte c LEFT JOIN intl_consignments ic ON ic.barcode = c.barcode LEFT JOIN intl_consignments_surcharges_transactions icst ON ic.id = icst.consignment_id LEFT JOIN intl_carrier inc ON c.carrier_id = inc.id LEFT JOIN intl_service ins ON c.service_id = ins.id LEFT JOIN sales_account sa ON c.sales_account_id = sa.id LEFT JOIN company co ON sa.company_id = co.id LEFT JOIN docs d ON d.id = c.id WHERE NOT EXISTS(SELECT c2.id FROM claims c2 LEFT JOIN intl_consignments ic ON ic.barcode = c2.barcode LEFT JOIN intl_consignments_surcharges_transactions icst ON ic.id = icst.consignment_id WHERE c.order_type = $111 AND COALESCE(icst.sales_nominal_transaction_id, $112) <> $113 AND c2.id = c.id) AND c.order_type = $114 UNION ALL SELECT c.*, COALESCE(co.name, $115), COALESCE(sa.sales_account_reference, $116), COALESCE(inc.name, $117), COALESCE(ins.name, $118), $119, $120, COALESCE(d.d_json, $121) FROM claims_cte c LEFT JOIN intl_consignments ic ON ic.barcode = c.barcode LEFT JOIN intl_consignments_surcharges_transactions icst ON ic.id = icst.consignment_id LEFT JOIN intl_carrier inc ON c.carrier_id = inc.id LEFT JOIN intl_service ins ON c.service_id = ins.id LEFT JOIN sales_account sa ON c.sales_account_id = sa.id LEFT JOIN company co ON sa.company_id = co.id LEFT JOIN docs d ON d.id = c.id WHERE NOT EXISTS(SELECT id FROM intl_consignments_surcharges_transactions inst WHERE inst.consignment_id = ic.id) AND c.order_type = $122 UNION ALL SELECT c.*, COALESCE(co.name, $123), COALESCE(sa.sales_account_reference, $124), COALESCE(ukec.name, $125), COALESCE(ukes.name, $126), $127, $128, COALESCE(d.d_json, $129) FROM claims_cte c LEFT JOIN consignment_imports ci ON ci.barcode = c.barcode LEFT JOIN uk_ecommerce_carrier ukec ON c.carrier_id = ukec.id LEFT JOIN uk_ecommerce_service ukes ON c.service_id = ukes.id LEFT JOIN sales_account sa ON c.sales_account_id = sa.id LEFT JOIN company co ON sa.company_id = co.id LEFT JOIN docs d ON d.id = c.id WHERE c.order_type = $130 AND ukec.financial_company = $131 UNION ALL SELECT c.*, COALESCE(co.name, $132), COALESCE(sa.sales_account_reference, $133), COALESCE(inc.name, $134), COALESCE(ins.name, $135), $136, $137, COALESCE(d.d_json, $138) FROM claims_cte c LEFT JOIN consignment_imports ci ON ci.barcode = c.barcode LEFT JOIN intl_carrier inc ON c.carrier_id = inc.id LEFT JOIN intl_service ins ON c.service_id = ins.id LEFT JOIN sales_account sa ON c.sales_account_id = sa.id LEFT JOIN company co ON sa.company_id = co.id LEFT JOIN docs d ON d.id = c.id WHERE c.order_type = $139 ORDER BY 1 DESC
8 min 0.1% 172 ms 2,648 postgresitd 
UPDATE consignment_imports ec SET sales_account_id = sa.id FROM sales_account sa WHERE ec.sales_account_reference = sa.sales_account_reference AND sa.status = $1 AND ec.sales_account_id = $2
Covered by index on (sales_account_reference, sales_account_id)
Rows: 74947712
Row progression: 74947712, 73942, 74

Row estimates
- sales_account_reference (=): 73942
- sales_account_id (=): 74624
- status (=): 37462614

Existing indexes
- id PRIMARY
- ((created_date)::date)
- barcode
- barcode_postcode_key UNIQUE
- carrier
- consignment_number
- consignment_number_postcode_key
- month, source
- order_number, ship_to_postcode
- sales_account_id
- sales_account_id, ((created_date)::date)
- sales_account_id, created_date_date
- sales_account_reference, sales_account_id
- source
- year, source
7 min 0.1% 750 ms 537 postgresitd 
WITH invoice_details AS (SELECT id, row_to_json(u) AS details FROM (SELECT isi.id                     AS id, isi.created_by, isi.created_date::date     AS created_date, i.name                     AS carrier_name, isupplier.id               AS supplier_account_id, isupplier.name             AS supplier_name, transaction_date::date     AS transaction_date, supplier_invoice_number, description, COALESCE(currency_id, $5)   AS currency, COALESCE(exchange_rate, $6) AS exchange_rate, invoice_type FROM intl_supplier_invoice isi LEFT JOIN intl_supplier isupplier ON isupplier.id = isi.supplier_account_id LEFT JOIN intl_carrier i ON isupplier.carrier_id = i.id WHERE isi.id = $1) u), exception_codes AS (SELECT ic.id                                AS id, sa.sales_account_reference, company.name                         AS customer_name, CASE WHEN ic.consignment_number <> $7 THEN ic.consignment_number ELSE ic.barcode END                              AS consignment_number, icst.supplier_freight_amount         AS supplier_freight_amount, icst.supplier_fuel_surcharge_amount  AS supplier_fuel_surcharge_amount, icst.supplier_fuel_surcharge_percent AS supplier_fuel_surcharge_percent, icst.supplier_tax_amount             AS supplier_tax_amount, icst.system_freight_amount           AS system_freight_amount, icst.system_fuel_surcharge_amount    AS system_fuel_surcharge_amount, icst.system_fuel_surcharge_percent   AS system_fuel_surcharge_percent, icst.system_tax_amount               AS system_tax_amount, icst.sales_freight_amount            AS sales_price, icst.sales_fuel_surcharge_amount     AS sales_fuel_surcharge_amount, icst.sales_fuel_surcharge_percent    AS sales_fuel_surcharge_percent, icst.sales_tax_code_id               AS sales_tax_code, icst.sales_tax_amount                AS sales_tax_amount, icst.sales_currency_id               AS sales_currency, COALESCE(nap.description, $8)        AS purchase_nominal, COALESCE(nas.description, $9)        AS sales_nominal, icst.sales_exception_code            AS sales_exception_code, icst.government_vat                  AS government_vat, icst.government_duty                 AS government_duty, icst.nominal_bucket_id               AS nominal_bucket_id FROM intl_consignments ic LEFT JOIN intl_service iservice ON iservice.id = ic.service_id LEFT JOIN intl_consignments_surcharges_transactions icst ON icst.consignment_id = ic.id LEFT JOIN nominal_account nap ON nap.id = icst.supplier_nominal_code_id LEFT JOIN nominal_account nas ON nas.id = icst.sales_nominal_code_id LEFT JOIN sales_account sa ON sa.id = icst.sales_account_id LEFT JOIN company ON company.id = sa.company_id WHERE icst.supplier_linked_invoice_id = $1 AND icst.consignment_surcharge_id = $10 AND ic.shipment_type = $2 AND icst.sales_exception_code = $3 AND icst.financial_company = $4), surcharges AS (SELECT icst.consignment_id               AS id, SUM(icst.supplier_freight_amount) AS supplier_surcharge_amount, SUM(icst.system_freight_amount)   AS system_surcharge_amount, SUM(icst.sales_freight_amount)    AS sales_amount FROM intl_consignments_surcharges_transactions icst LEFT JOIN intl_consignments ic ON ic.id = icst.consignment_id LEFT JOIN sales_account sa ON sa.id = icst.sales_account_id WHERE icst.supplier_linked_invoice_id = $1 AND icst.consignment_surcharge_id <> $11 AND ic.shipment_type = $2 AND icst.sales_exception_code = $3 AND icst.financial_company = $4 GROUP BY icst.consignment_id, ic.shipment_type) SELECT invoice_details.details                                            AS details, ec.id                                                              AS id, RTRIM(CONCAT(ec.sales_account_reference, $12, ec.customer_name)) AS customer, ec.consignment_number                                              AS consignment_number, ec.supplier_freight_amount + COALESCE(surcharges.supplier_surcharge_amount, $13) + ec.supplier_fuel_surcharge_amount                            AS supplier_net, supplier_tax_amount                                                AS supplier_tax_amount, ec.supplier_freight_amount + COALESCE(surcharges.supplier_surcharge_amount, $14) + ec.supplier_fuel_surcharge_amount + supplier_tax_amount                                          AS supplier_total, ec.system_freight_amount + COALESCE(surcharges.system_surcharge_amount, $15) + ec.system_fuel_surcharge_amount                              AS purchase_net, ec.system_tax_amount                                               AS purchase_tax_amount, ec.system_freight_amount + COALESCE(surcharges.system_surcharge_amount, $16) + ec.system_fuel_surcharge_amount + system_tax_amount + ec.government_vat + ec.government_duty                                           AS purchase_total, ec.sales_currency                                                  AS sales_currency, ec.sales_tax_code                                                  AS sales_tax_code, ec.sales_price + COALESCE(surcharges.sales_amount, $17) + sales_fuel_surcharge_amount + ec.government_vat + ec.government_duty                                           AS sales_total, sales_tax_amount                                                   AS sales_tax_amount, ec.sales_price + COALESCE(surcharges.sales_amount, $18) + sales_fuel_surcharge_amount + sales_tax_amount                                             AS sales_total, ec.purchase_nominal                                                AS purchase_nominal, ec.sales_nominal                                                   AS sales_nominal, ec.sales_price + COALESCE(surcharges.sales_amount, $19) + sales_fuel_surcharge_amount - ec.supplier_freight_amount - COALESCE(surcharges.supplier_surcharge_amount, $20) - ec.supplier_fuel_surcharge_amount                            AS profit, CASE WHEN ec.supplier_freight_amount = $21 AND (ec.government_duty <> $22 OR ec.government_vat <> $23) THEN $24 ELSE COALESCE(ec.supplier_freight_amount - ec.system_freight_amount, $25) END AS freight_variance, COALESCE(surcharges.supplier_surcharge_amount - surcharges.system_surcharge_amount, $26)              AS surcharge_variance, COALESCE(ec.supplier_fuel_surcharge_amount - ec.system_fuel_surcharge_amount, $27)                 AS fuel_variance, ec.supplier_fuel_surcharge_amount, ec.system_fuel_surcharge_amount, ec.sales_fuel_surcharge_amount, ec.supplier_fuel_surcharge_percent, ec.system_fuel_surcharge_percent, ec.sales_fuel_surcharge_percent, ec.sales_exception_code, ec.government_vat, ec.government_duty, ec.nominal_bucket_id FROM exception_codes AS ec LEFT JOIN surcharges ON surcharges.id = ec.id, invoice_details
5 min < 0.1% 287 ms 1,096 postgresitd 
WITH consignments as (SELECT isi.id                                                             AS isi_id, isi.status                                                         AS status, isi.created_date                                                   AS created_date, icarrier.name                                                      AS carrier_name, isupplier.name                                                     AS supplier_name, isi.supplier_invoice_number                                        AS supplier_invoice_number, COALESCE(description, $5)                                          AS description, isi.invoice_type                                                   as invoice_type, COALESCE(SUM(COALESCE(icst.supplier_freight_amount, $6) + COALESCE(icst.government_duty, $7) + COALESCE(icst.government_vat, $8)) FILTER (WHERE icst.sales_exception_code != $9), $10) + COALESCE(SUM(COALESCE(icst.supplier_fuel_surcharge_amount, $11)) FILTER (WHERE icst.sales_exception_code != $12), $13)                                                  AS supplier_cost_price_exceptions, $14                                                                  AS supplier_vat_exceptions, COALESCE(SUM(COALESCE(icst.sales_freight_amount, $15) + COALESCE(icst.government_duty, $16) + COALESCE(icst.government_vat, $17)) FILTER (WHERE icst.sales_exception_code != $18), $19) + COALESCE(SUM(COALESCE(icst.sales_fuel_surcharge_amount, $20)) FILTER (WHERE icst.sales_exception_code != $21), $22)                                                  AS sales_cost_price_exceptions, COALESCE(SUM(COALESCE(icst.supplier_freight_amount, $23) + COALESCE(icst.government_duty, $24) + COALESCE(icst.government_vat, $25)) FILTER (WHERE icst.sales_exception_code = $26), $27) + COALESCE(SUM(COALESCE(icst.supplier_fuel_surcharge_amount, $28)) FILTER (WHERE icst.sales_exception_code = $29), $30) AS supplier_cost_price, COALESCE(SUM(COALESCE(icst.sales_freight_amount, $31) + COALESCE(icst.government_duty, $32) + COALESCE(icst.government_vat, $33)) FILTER (WHERE icst.sales_exception_code = $34), $35) + COALESCE(SUM(COALESCE(icst.sales_fuel_surcharge_amount, $36)) FILTER (WHERE icst.sales_exception_code = $37), $38) AS sales_cost_price, $39                                                                  AS supplier_vat, COALESCE(isi.purchase_transaction_id, $40)                           AS nominal_transaction_id, COALESCE(pa.id, $41)                                                 AS pa_id, COALESCE(CONCAT(pa.purchase_account_reference, $42, RTRIM(company.name)), $43)                   AS purchase_account_name, COALESCE(confirmed_for_invoicing, $44)                           AS confirmed_for_invoicing FROM intl_supplier_invoice isi LEFT JOIN intl_supplier isupplier ON isupplier.id = isi.supplier_account_id LEFT JOIN intl_carrier icarrier ON icarrier.id = isupplier.carrier_id LEFT JOIN intl_consignments_surcharges_transactions icst ON icst.supplier_linked_invoice_id = isi.id LEFT JOIN purchase_account pa ON pa.id = isupplier.purchase_account_id LEFT JOIN company ON company.id = pa.company_id WHERE isi.status <> $45 AND isi.created_date :: date BETWEEN $2 AND $3 AND isi.invoice_type = $1 AND isi.financial_company = $4 AND icst.consignment_id <> $46 GROUP BY icarrier.name, isi.status, isupplier.name, isi.supplier_invoice_number, isi.id, isi.description, isi.invoice_type, isi.created_date, isi.purchase_transaction_id, pa.id, pa.purchase_account_reference, company.name, confirmed_for_invoicing, icst.government_duty, icst.government_vat UNION ALL SELECT isi.id                                                             AS isi_id, isi.status                                                         AS status, isi.created_date                                                   AS created_date, icarrier.name                                                      AS carrier_name, isupplier.name                                                     AS supplier_name, isi.supplier_invoice_number                                        AS supplier_invoice_number, COALESCE(description, $47)                                          AS description, isi.invoice_type                                                   as invoice_type, COALESCE(SUM(COALESCE(icst.supplier_freight_amount, $48) + COALESCE(icst.government_duty, $49) + COALESCE(icst.government_vat, $50)) FILTER (WHERE icst.sales_exception_code != $51), $52) + COALESCE(SUM(COALESCE(icst.supplier_fuel_surcharge_amount, $53)) FILTER (WHERE icst.sales_exception_code != $54), $55)                                                  AS supplier_cost_price_exceptions, $56                                                                  AS supplier_vat_exceptions, COALESCE(SUM(COALESCE(icst.sales_freight_amount, $57) + COALESCE(icst.government_duty, $58) + COALESCE(icst.government_vat, $59)) FILTER (WHERE icst.sales_exception_code != $60), $61) + COALESCE(SUM(COALESCE(icst.sales_fuel_surcharge_amount, $62)) FILTER (WHERE icst.sales_exception_code != $63), $64)                                                  AS sales_cost_price_exceptions, COALESCE(SUM(COALESCE(icst.supplier_freight_amount, $65) + COALESCE(icst.government_duty, $66) + COALESCE(icst.government_vat, $67)) FILTER (WHERE icst.sales_exception_code = $68), $69) + COALESCE(SUM(COALESCE(icst.supplier_fuel_surcharge_amount, $70)) FILTER (WHERE icst.sales_exception_code = $71), $72) AS supplier_cost_price, COALESCE(SUM(COALESCE(icst.sales_freight_amount, $73) + COALESCE(icst.government_duty, $74) + COALESCE(icst.government_vat, $75)) FILTER (WHERE icst.sales_exception_code = $76), $77) + COALESCE(SUM(COALESCE(icst.sales_fuel_surcharge_amount, $78)) FILTER (WHERE icst.sales_exception_code = $79), $80) AS sales_cost_price, $81                                                                  AS supplier_vat, COALESCE(isi.purchase_transaction_id, $82)                           AS nominal_transaction_id, COALESCE(pa.id, $83)                                                 AS pa_id, COALESCE(CONCAT(pa.purchase_account_reference, $84, RTRIM(company.name)), $85)                   AS purchase_account_name, COALESCE(confirmed_for_invoicing, $86)                           AS confirmed_for_invoicing FROM intl_supplier_invoice isi LEFT JOIN intl_supplier isupplier ON isupplier.id = isi.supplier_account_id LEFT JOIN intl_carrier icarrier ON icarrier.id = isupplier.carrier_id LEFT JOIN intl_consignments_surcharges_transactions icst ON icst.supplier_linked_invoice_id = isi.id LEFT JOIN purchase_account pa ON pa.id = isupplier.purchase_account_id LEFT JOIN company ON company.id = pa.company_id WHERE isi.status <> $87 AND isi.created_date ::date BETWEEN $2 AND $3 AND isi.invoice_type = $1 AND icst.financial_company = $4 AND isi.financial_company = $4 AND icst.consignment_id = $88 GROUP BY icarrier.name, isi.status, isupplier.name, isi.supplier_invoice_number, isi.id, isi.description, isi.invoice_type, isi.created_date, isi.purchase_transaction_id, pa.id, pa.purchase_account_reference, company.name, confirmed_for_invoicing) SELECT $89, isi_id, status, created_date, carrier_name, supplier_name, supplier_invoice_number, description, invoice_type, SUM(supplier_cost_price_exceptions), SUM(supplier_vat_exceptions) AS vat_exceptions, SUM(sales_cost_price_exceptions), SUM(supplier_cost_price), SUM(supplier_vat)            AS vat, SUM(sales_cost_price), nominal_transaction_id, pa_id, purchase_account_name, confirmed_for_invoicing FROM consignments GROUP BY isi_id, status, supplier_invoice_number, pa_id, purchase_account_name, description, invoice_type, nominal_transaction_id, created_date, carrier_name, supplier_name, confirmed_for_invoicing ORDER BY created_date DESC, carrier_name DESC, supplier_name, supplier_invoice_number, description
4 min < 0.1% 29 ms 8,434 postgresitd 
SELECT COALESCE(json_agg(query.*), $1) FROM (SELECT DISTINCT ON (csaic.id) COALESCE(jsonb_build_object( $2, COALESCE(c.name, $3), $4, COALESCE(aan.address_line_1, $5), $6, COALESCE(aan.address_line_2, $7), $8, COALESCE(aan.address_line_3, $9), $10, COALESCE(aan.city, $11), $12, COALESCE(aan.county_state, $13), $14, COALESCE(aan.postcode, $15), $16, COALESCE(aan.country_iso, $17), $18, COALESCE(RTRIM(ac.name), $19), $20, COALESCE(aan.telephone_number, $21)), $22)                                                AS default_address, COALESCE(cvw.phone_number, $23)                                AS telephone_number, COALESCE(jsonb_build_object($24, COALESCE(csaic.api_key, $25), $26, COALESCE(csaic.token, $27), $28, csaic.details->>$29), $30) AS tokens, csaic.id, csaic.sales_account_id                                        AS SalesAccountID, COALESCE(cap.token, $31)                                       AS connexx_token, COALESCE(cvw.name, $32)                                        AS warehouse_name, COALESCE(csaic.identifier, $33)                                AS StoreName, COALESCE(csaic.email, $34)                                     AS UserEmail FROM connexx_sales_account_integration_credentials csaic LEFT JOIN sales_account sa ON sa.id = csaic.sales_account_id LEFT JOIN company c ON sa.company_id = c.id LEFT JOIN connexx_virtual_warehouses cvw ON sa.id = cvw.sales_account_id AND cvw.is_default IS TRUE AND cvw.deleted_date IS NULL LEFT JOIN account_address_new aan ON cvw.address_id = aan.id LEFT JOIN connexx_api_keys cap ON cap.sales_account_id = sa.id LEFT JOIN account_contact ac ON ac.sales_account_id = sa.id WHERE COALESCE(csaic.api_key , $35) <> $36 AND csaic.integration_type = $37 AND csaic.status = $38 ORDER BY csaic.id DESC) query
3 min < 0.1% 834 ms 246 postgresitd 
WITH lat AS (SELECT lat.sales_account_id, COALESCE(sum(lat.outstanding), 0) AS outstanding, COALESCE(sum(lat.outstanding) FILTER (WHERE lat.date_due BETWEEN CURRENT_DATE - interval '7 days' AND CURRENT_DATE - interval '1 days'), 0)                       AS period_1, COALESCE(sum(lat.outstanding) FILTER (WHERE lat.date_due BETWEEN CURRENT_DATE - interval '14 days' AND CURRENT_DATE - interval '8 days'), 0)                       AS period_2, COALESCE(sum(lat.outstanding) FILTER (WHERE lat.date_due BETWEEN CURRENT_DATE - interval '30 days' AND CURRENT_DATE - interval '15 days'), 0)                       AS period_3, COALESCE(sum(lat.outstanding) FILTER (WHERE lat.date_due BETWEEN CURRENT_DATE - interval '60 days' AND CURRENT_DATE - interval '31 days'), 0)                       AS period_4, COALESCE(sum(lat.outstanding) FILTER (WHERE lat.date_due BETWEEN CURRENT_DATE - interval '90 days' AND CURRENT_DATE - interval '61 days'), 0)                       AS period_5, COALESCE(sum(lat.outstanding) FILTER (WHERE lat.date_due BETWEEN CURRENT_DATE - interval '100000 days' AND CURRENT_DATE - interval '91 days'), 0)                       AS period_6, lat.transaction_category, make_date(0001, 01, 01)           as transaction_date, ''                                as transaction_reference FROM sales_account_transactions lat WHERE lat.status = 'A' AND lat.reversal_user = '' AND lat.reversed_by = 0 AND lat.original_id = 0 AND lat.financial_company = $1 GROUP BY lat.sales_account_id, lat.status, lat.reversal_user, lat.reversed_by, lat.original_id, lat.financial_company, lat.transaction_category), addresses AS (SELECT sa.id                                      AS sales_account_id, array_agg(TRIM(COALESCE(INITCAP(aan.address_line_1), '') || ' ' || COALESCE(INITCAP(aan.address_line_2), '') || ' ' || COALESCE(INITCAP(aan.address_line_3), '') || ' ' || COALESCE(INITCAP(aan.city), '') || ' ' || COALESCE(INITCAP(aan.county_state), '') || ' ' || COALESCE(UPPER(aan.postcode), '') || ' ' || COALESCE(aan.country, ''))) AS addresses FROM sales_account sa LEFT JOIN account_address_new aan ON sa.company_id = aan.company_id WHERE sa.financial_company = $1 AND aan.status = 'A' GROUP BY 1), contacts AS (SELECT sa.id                                                  AS sales_account_id, array_agg(TRIM(COALESCE(INITCAP(ac.name), '') || ' ' || COALESCE(LOWER(ac.email_address), ''))) AS contacts FROM sales_account sa LEFT JOIN account_contact ac ON sa.company_id = ac.company_id WHERE sa.financial_company = $1 AND ac.status = 'A' GROUP BY 1), sales_accounts AS (SELECT sa.id, COALESCE(sa.old_id, 0)                           AS old_id, sa.company_id, sa.status, COALESCE(sa.salesperson, 0)                      AS salesperson, COALESCE(su.department, 0)                       AS department, coalesce(sa.sales_account_reference, '')         AS ledgerName_reference, coalesce(sa.old_sales_account_reference, '')     AS old_ledgerName_reference, COALESCE(sa.company_number, '')                  AS company_number, COALESCE(RTRIM(INITCAP(CONCAT(su.first_name, ' ', su.surname))), '')                                     AS sales_person_name, last_statement_date, CASE WHEN (c_terms ->> 'PaymentType')::int = 1 THEN 'DIRECT DEBIT' WHEN (c_terms ->> 'PaymentType')::int = 2 THEN 'BACS' WHEN (c_terms ->> 'PaymentType')::int = 3 THEN 'CHEQUE' WHEN (c_terms ->> 'PaymentType')::int = 4 THEN 'CREDIT CARD' WHEN (c_terms ->> 'PaymentType')::int = 5 THEN 'PREPAYMENT' WHEN (c_terms ->> 'PaymentType')::int = 6 THEN 'INTERNAL' ELSE '' END                                          AS payment_type, COALESCE((c_terms ->> 'CreditDays')::int, 0)     AS credit_terms_period, COALESCE((c_terms ->> 'FollowingMonth')::int, 0) AS following_month, COALESCE(sa.status_plus, 'A')                    as status_plus, sa.internal_credit_limit, sa.agent_limit, sa.alert_type, CASE WHEN sa.account_type = 1 THEN 'Parcel' WHEN sa.account_type = 2 THEN 'Freight' WHEN sa.account_type = 3 THEN 'Returns' ELSE '' END                                          AS account_type, CASE WHEN sa.base_currency IS NOT null AND sa.base_currency != 0 THEN currency.currency_code ELSE 'NOT SET' END                                          AS base_currency, CASE WHEN spsu.id <> 0 THEN CONCAT(spsu.first_name, ' ', spsu.surname) ELSE '' END                                          AS cs_salesperson, CASE WHEN amsu.id <> 0 THEN CONCAT(amsu.first_name, ' ', amsu.surname) ELSE '' END                                          AS cs_account_manager, CASE WHEN succ.id <> 0 THEN CONCAT(succ.first_name, ' ', succ.surname) ELSE '' END                                          AS credit_controller, COALESCE(sa.collection_method, '')               AS collection_method, COALESCE(evri_premium, false)                    AS evri_premium FROM sales_account sa LEFT JOIN jsonb_array_elements(sa.credit_terms_json -> 'credit_terms') AS c_terms ON (true AND (c_terms -> 'InvoiceTypeID')::text::int = 0) LEFT JOIN public.system_user su ON su.id = sa.salesperson LEFT JOIN currency ON sa.base_currency = currency.id LEFT JOIN system_user_structure_margins spsm ON sa.sales_person_structure_id = spsm.id LEFT JOIN public.system_user spsu ON spsm.system_user_id = spsu.id LEFT JOIN system_user_structure_margins amsm ON sa.account_manager_structure_id = amsm.id LEFT JOIN public.system_user amsu ON amsm.system_user_id = amsu.id LEFT JOIN public.system_user succ ON sa.credit_controller_id = succ.id WHERE sa.status <> 'P' AND sa.financial_company = $1) SELECT sa.id, sa.old_id, COALESCE(sa.internal_credit_limit, 0), COALESCE(sa.agent_limit, 0), sa.ledgerName_reference, sa.old_ledgerName_reference, COALESCE(lat.transaction_date, '0001-01-01'), '0001-01-01'::date, COALESCE(lat.transaction_reference, ''), RTRIM(COALESCE(company.name, '')), sa.sales_person_name                     AS sales_person_name, sa.credit_controller                     AS credit_controller, RTRIM(COALESCE(COALESCE(NULLIF(company.trading_name, ''), company.name), '')), COALESCE(sa.company_number, ''), SUM(COALESCE(lat.outstanding, 0)), SUM(COALESCE(lat.period_1, 0)), SUM(COALESCE(lat.period_2, 0)), SUM(COALESCE(lat.period_3, 0)), SUM(COALESCE(lat.period_4, 0)), SUM(COALESCE(lat.period_5, 0)), SUM(COALESCE(lat.period_6, 0)), sa.status, CASE WHEN sa.status_plus = 'A' AND sa.alert_type = 'amberalert' THEN 'A-AA' WHEN sa.status_plus = 'A' AND sa.alert_type = 'baddebt' THEN 'A-BD' WHEN sa.status_plus = 'A' AND sa.alert_type = 'onstop' THEN 'A-OS' WHEN sa.status_plus = 'I' AND sa.alert_type = 'amberalert' THEN 'I-AA' WHEN sa.status_plus = 'I' AND sa.alert_type = 'baddebt' THEN 'I-BD' WHEN sa.status_plus = 'I' AND sa.alert_type = 'onstop' THEN 'I-OS' ELSE sa.status_plus END, COALESCE(sa.payment_type, ''), COALESCE(sa.credit_terms_period, 0), CASE WHEN COALESCE(sa.status_plus, '') = 'A-BD' THEN SUM(COALESCE(lat.outstanding, 0)) ELSE 0 END, COALESCE(sa.last_statement_date, '0001-01-01'), COALESCE(sa.following_month, 0)          AS following_month, sa.account_type, sa.base_currency, CASE WHEN sa.cs_account_manager <> '' AND sa.cs_salesperson <> '' THEN CONCAT(sa.cs_salesperson, ', ', sa.cs_account_manager) ELSE CONCAT(sa.cs_salesperson, sa.cs_account_manager) END                                  AS cs_sp_am, COALESCE(sa.collection_method, '')       AS collection_method, sa.evri_premium, addresses.addresses || contacts.contacts AS contacts_addresses FROM sales_accounts sa LEFT JOIN company ON company.id = sa.company_id LEFT JOIN lat ON lat.sales_account_id = sa.id LEFT JOIN public.system_user su ON su.id = sa.salesperson LEFT JOIN addresses ON sa.id = addresses.sales_account_id LEFT JOIN contacts ON sa.id = contacts.sales_account_id WHERE  sa.status_plus = 'A' 
						AND (sa.alert_type = '' OR sa.alert_type = 'none' OR sa.alert_type is null OR sa.alert_type = 'onstop') GROUP BY sa.id, sa.old_id, sa.internal_credit_limit, sa.agent_limit, sa.ledgerName_reference, sa.old_ledgerName_reference, lat.transaction_date, lat.transaction_reference, company.name, sa.sales_person_name, sa.credit_controller, company.trading_name, sa.company_number, sa.status, sa.status_plus, sa.alert_type, sa.payment_type, sa.credit_terms_period, sa.last_statement_date, sa.following_month, sa.account_type, sa.base_currency, sa.cs_salesperson, sa.cs_account_manager, sa.collection_method, sa.evri_premium, addresses.addresses, contacts.contacts
3 min < 0.1% 1,358 ms 147 postgresitd 
SELECT $3                               AS geo_group, $4                                    AS charge_type, INITCAP(RTRIM(company.name)), i_service.carrier_id, i_carrier.name                       AS carrier, ic.package_type_id, CASE WHEN ic.package_type_id = $5 THEN $6 WHEN ic.package_type_id = $7 THEN $8 WHEN ic.package_type_id = $9 THEN $10 ELSE $11 END                              AS package, COALESCE(i_service.display_order, $12) AS display_order, ic.zone_name                         AS zone_name, ic.charged_weight                    AS charged_weight, ic.service_id, i_service.name                       AS description, COUNT(*), SUM(icst.sales_freight_amount), $13, icst.sales_freight_amount, number_of_pieces, COALESCE(icst.sales_tax_code_id, $14) FROM intl_consignments_surcharges_transactions icst LEFT JOIN intl_consignments ic ON ic.id = icst.consignment_id LEFT JOIN sales_account sa ON sa.id = icst.sales_account_id LEFT JOIN company ON company.id = sa.company_id LEFT JOIN intl_service i_service ON i_service.id = ic.service_id LEFT JOIN intl_carrier i_carrier ON i_carrier.id = i_service.carrier_id LEFT JOIN intl_tariff i_tariff ON i_tariff.id = ic.tariff_id WHERE icst.sales_account_id = $1 AND icst.financial_company = $2 AND icst.transaction_type = $15 AND icst.sales_exception_code = $16 AND icst.sales_nominal_transaction_id = $17 AND icst.supplier_nominal_transaction_id <> $18 AND icst.consignment_surcharge_id = $19 AND icst.consignment_vat_duty_id = $20 AND i_carrier.eu = $21 GROUP BY charge_type, i_carrier.name, i_service.display_order, ic.zone_name, ic.charged_weight, i_service.name, i_service.carrier_id, ic.package_type_id, ic.service_id, company.name, icst.sales_freight_amount, number_of_pieces, icst.sales_tax_code_id UNION ALL SELECT $22                      AS geo_group, $23                           AS charge_type, INITCAP(RTRIM(company.name)), i_service.carrier_id, i_carrier.name              AS carrier, ics.package_type_id, $24                          AS package, i_service.display_order     AS display_order, COALESCE(ics.zone_name, $25) AS zone_name, $26                           AS charged_weight, ics.surcharge_id, i_service.name, COUNT(*), ROUND(SUM(icst.sales_freight_amount)), $27, icst.sales_freight_amount   AS sales_price, $28, COALESCE(icst.sales_tax_code_id, $29) FROM intl_consignments_surcharges_transactions icst LEFT JOIN intl_consignment_surcharges ics ON ics.id = icst.consignment_surcharge_id LEFT JOIN sales_account sa ON sa.id = icst.sales_account_id LEFT JOIN company ON company.id = sa.company_id LEFT JOIN intl_service i_service ON i_service.id = ics.surcharge_id LEFT JOIN intl_carrier i_carrier ON i_carrier.id = i_service.carrier_id WHERE icst.sales_account_id = $1 AND icst.financial_company = $2 AND ics.brand <> $30 AND icst.sales_exception_code = $31 AND icst.sales_nominal_transaction_id = $32 AND icst.supplier_nominal_transaction_id <> $33 AND icst.consignment_surcharge_id <> $34 AND icst.consignment_vat_duty_id = $35 AND i_carrier.eu = $36 GROUP BY charge_type, i_carrier.name, i_service.display_order, ics.zone_name, i_service.name, i_service.carrier_id, ics.package_type_id, ics.surcharge_id, company.name, icst.sales_freight_amount, icst.sales_tax_code_id ORDER BY charge_type, carrier, package, display_order, zone_name, charged_weight
2 min < 0.1% 958 ms 115 postgresitd 
WITH lat AS (SELECT lat.sales_account_id, COALESCE(sum(lat.outstanding), $2) AS outstanding, COALESCE(sum(lat.outstanding) FILTER (WHERE lat.date_due BETWEEN CURRENT_DATE - interval $3 AND CURRENT_DATE - interval $4), $5)                       AS period_1, COALESCE(sum(lat.outstanding) FILTER (WHERE lat.date_due BETWEEN CURRENT_DATE - interval $6 AND CURRENT_DATE - interval $7), $8)                       AS period_2, COALESCE(sum(lat.outstanding) FILTER (WHERE lat.date_due BETWEEN CURRENT_DATE - interval $9 AND CURRENT_DATE - interval $10), $11)                       AS period_3, COALESCE(sum(lat.outstanding) FILTER (WHERE lat.date_due BETWEEN CURRENT_DATE - interval $12 AND CURRENT_DATE - interval $13), $14)                       AS period_4, COALESCE(sum(lat.outstanding) FILTER (WHERE lat.date_due BETWEEN CURRENT_DATE - interval $15 AND CURRENT_DATE - interval $16), $17)                       AS period_5, COALESCE(sum(lat.outstanding) FILTER (WHERE lat.date_due BETWEEN CURRENT_DATE - interval $18 AND CURRENT_DATE - interval $19), $20)                       AS period_6, lat.transaction_category, make_date($21, $22, $23)           as transaction_date, $24                                as transaction_reference FROM sales_account_transactions lat WHERE lat.status = $25 AND lat.reversal_user = $26 AND lat.reversed_by = $27 AND lat.original_id = $28 AND lat.financial_company = $1 GROUP BY lat.sales_account_id, lat.status, lat.reversal_user, lat.reversed_by, lat.original_id, lat.financial_company, lat.transaction_category), addresses AS (SELECT sa.id                                      AS sales_account_id, array_agg(TRIM(COALESCE(INITCAP(aan.address_line_1), $29) || $30 || COALESCE(INITCAP(aan.address_line_2), $31) || $32 || COALESCE(INITCAP(aan.address_line_3), $33) || $34 || COALESCE(INITCAP(aan.city), $35) || $36 || COALESCE(INITCAP(aan.county_state), $37) || $38 || COALESCE(UPPER(aan.postcode), $39) || $40 || COALESCE(aan.country, $41))) AS addresses FROM sales_account sa LEFT JOIN account_address_new aan ON sa.company_id = aan.company_id WHERE sa.financial_company = $1 AND aan.status = $42 GROUP BY 1), contacts AS (SELECT sa.id                                                  AS sales_account_id, array_agg(TRIM(COALESCE(INITCAP(ac.name), $43) || $44 || COALESCE(LOWER(ac.email_address), $45))) AS contacts FROM sales_account sa LEFT JOIN account_contact ac ON sa.company_id = ac.company_id WHERE sa.financial_company = $1 AND ac.status = $46 GROUP BY 1), sales_accounts AS (SELECT sa.id, COALESCE(sa.old_id, $47)                           AS old_id, sa.company_id, sa.status, COALESCE(sa.salesperson, $48)                      AS salesperson, COALESCE(su.department, $49)                       AS department, coalesce(sa.sales_account_reference, $50)         AS ledgerName_reference, coalesce(sa.old_sales_account_reference, $51)     AS old_ledgerName_reference, COALESCE(sa.company_number, $52)                  AS company_number, COALESCE(RTRIM(INITCAP(CONCAT(su.first_name, $53, su.surname))), $54)                                     AS sales_person_name, last_statement_date, CASE WHEN (c_terms ->> $55)::int = $56 THEN $57 WHEN (c_terms ->> $58)::int = $59 THEN $60 WHEN (c_terms ->> $61)::int = $62 THEN $63 WHEN (c_terms ->> $64)::int = $65 THEN $66 WHEN (c_terms ->> $67)::int = $68 THEN $69 WHEN (c_terms ->> $70)::int = $71 THEN $72 ELSE $73 END                                          AS payment_type, COALESCE((c_terms ->> $74)::int, $75)     AS credit_terms_period, COALESCE((c_terms ->> $76)::int, $77) AS following_month, COALESCE(sa.status_plus, $78)                    as status_plus, sa.internal_credit_limit, sa.agent_limit, sa.alert_type, CASE WHEN sa.account_type = $79 THEN $80 WHEN sa.account_type = $81 THEN $82 WHEN sa.account_type = $83 THEN $84 ELSE $85 END                                          AS account_type, CASE WHEN sa.base_currency IS NOT null AND sa.base_currency != $86 THEN currency.currency_code ELSE $87 END                                          AS base_currency, CASE WHEN spsu.id <> $88 THEN CONCAT(spsu.first_name, $89, spsu.surname) ELSE $90 END                                          AS cs_salesperson, CASE WHEN amsu.id <> $91 THEN CONCAT(amsu.first_name, $92, amsu.surname) ELSE $93 END                                          AS cs_account_manager, CASE WHEN succ.id <> $94 THEN CONCAT(succ.first_name, $95, succ.surname) ELSE $96 END                                          AS credit_controller, COALESCE(sa.collection_method, $97)               AS collection_method, COALESCE(evri_premium, $98)                    AS evri_premium FROM sales_account sa LEFT JOIN jsonb_array_elements(sa.credit_terms_json -> $99) AS c_terms ON ($100 AND (c_terms -> $101)::text::int = $102) LEFT JOIN public.system_user su ON su.id = sa.salesperson LEFT JOIN currency ON sa.base_currency = currency.id LEFT JOIN system_user_structure_margins spsm ON sa.sales_person_structure_id = spsm.id LEFT JOIN public.system_user spsu ON spsm.system_user_id = spsu.id LEFT JOIN system_user_structure_margins amsm ON sa.account_manager_structure_id = amsm.id LEFT JOIN public.system_user amsu ON amsm.system_user_id = amsu.id LEFT JOIN public.system_user succ ON sa.credit_controller_id = succ.id WHERE sa.status <> $103 AND sa.financial_company = $1) SELECT sa.id, sa.old_id, COALESCE(sa.internal_credit_limit, $104), COALESCE(sa.agent_limit, $105), sa.ledgerName_reference, sa.old_ledgerName_reference, COALESCE(lat.transaction_date, $106), $107::date, COALESCE(lat.transaction_reference, $108), RTRIM(COALESCE(company.name, $109)), sa.sales_person_name                     AS sales_person_name, sa.credit_controller                     AS credit_controller, RTRIM(COALESCE(COALESCE(NULLIF(company.trading_name, $110), company.name), $111)), COALESCE(sa.company_number, $112), SUM(COALESCE(lat.outstanding, $113)), SUM(COALESCE(lat.period_1, $114)), SUM(COALESCE(lat.period_2, $115)), SUM(COALESCE(lat.period_3, $116)), SUM(COALESCE(lat.period_4, $117)), SUM(COALESCE(lat.period_5, $118)), SUM(COALESCE(lat.period_6, $119)), sa.status, CASE WHEN sa.status_plus = $120 AND sa.alert_type = $121 THEN $122 WHEN sa.status_plus = $123 AND sa.alert_type = $124 THEN $125 WHEN sa.status_plus = $126 AND sa.alert_type = $127 THEN $128 WHEN sa.status_plus = $129 AND sa.alert_type = $130 THEN $131 WHEN sa.status_plus = $132 AND sa.alert_type = $133 THEN $134 WHEN sa.status_plus = $135 AND sa.alert_type = $136 THEN $137 ELSE sa.status_plus END, COALESCE(sa.payment_type, $138), COALESCE(sa.credit_terms_period, $139), CASE WHEN COALESCE(sa.status_plus, $140) = $141 THEN SUM(COALESCE(lat.outstanding, $142)) ELSE $143 END, COALESCE(sa.last_statement_date, $144), COALESCE(sa.following_month, $145)          AS following_month, sa.account_type, sa.base_currency, CASE WHEN sa.cs_account_manager <> $146 AND sa.cs_salesperson <> $147 THEN CONCAT(sa.cs_salesperson, $148, sa.cs_account_manager) ELSE CONCAT(sa.cs_salesperson, sa.cs_account_manager) END                                  AS cs_sp_am, COALESCE(sa.collection_method, $149)       AS collection_method, sa.evri_premium, addresses.addresses || contacts.contacts AS contacts_addresses FROM sales_accounts sa LEFT JOIN company ON company.id = sa.company_id LEFT JOIN lat ON lat.sales_account_id = sa.id LEFT JOIN public.system_user su ON su.id = sa.salesperson LEFT JOIN addresses ON sa.id = addresses.sales_account_id LEFT JOIN contacts ON sa.id = contacts.sales_account_id WHERE  (sa.status_plus = $150 OR sa.status_plus = $151) OR sa.alert_type = $152 GROUP BY sa.id, sa.old_id, sa.internal_credit_limit, sa.agent_limit, sa.ledgerName_reference, sa.old_ledgerName_reference, lat.transaction_date, lat.transaction_reference, company.name, sa.sales_person_name, sa.credit_controller, company.trading_name, sa.company_number, sa.status, sa.status_plus, sa.alert_type, sa.payment_type, sa.credit_terms_period, sa.last_statement_date, sa.following_month, sa.account_type, sa.base_currency, sa.cs_salesperson, sa.cs_account_manager, sa.collection_method, sa.evri_premium, addresses.addresses, contacts.contacts