Duplicate Indexes

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

rails generate migration remove_unneeded_indexes

And paste

remove_index :barcode_registry, name: "idx_barcode_registry_barcode", column: :barcode
remove_index :code_symbol, name: "idx_code_symbol_repo", column: :repo
remove_index :connexx_shipment_packages, name: "idx_tracking_code_trgm", column: :"tracking_code gin_trgm_ops"
remove_index :connexx_shipments, name: "idx_shipments_not_cancelled_error", column: [:"id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text", :"'ERROR'::text])"]
remove_index :connexx_shipments, name: "connexx_shipments_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", column: :id
remove_index :currency, name: "idx_currency_id", column: :id
remove_index :evri_ooa, name: "evri_ooa_postcode_index", column: :postcode
remove_index :file_imports, name: "idx_file_imports_hash", column: :file_hash
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 :flattened_shipments, name: "flattened_shipments_shipment_package_id_idx", column: :shipment_package_id
remove_index :flattened_shipments_scurri, name: "flattened_shipments_scurri_shipment_package_id_idx", column: :shipment_package_id
remove_index :intake_account_supplier_mapping, name: "idx_acct_supplier_config", column: :config_id
remove_index :intake_config_supplier, name: "idx_intake_config_supplier_config", column: :config_id
remove_index :intl_consignment_surcharges, name: "intl_consignment_surcharges_tsfc_idx", column: :tracking_surcharge_financial_company
remove_index :intl_consignments, name: "intl_consignments_tpfc_idx", column: :tracking_postcode_financial_company
remove_index :intl_consignments_surcharges_transactions, name: "icst_supplier_linked_invoice_id_index", column: :supplier_linked_invoice_id
remove_index :margin_details, name: "idx_margin_details_sales_account", column: :sales_account_id
remove_index :mb_shipments_rollup_mv, name: "mb_shipments_rollup_mv_day_customer", column: [:day, :customer_name]
remove_index :pipeline_events, name: "idx_pipeline_events_pair_group", column: [:pair_group_id, :sequence]
remove_index :processing_lab_exceptions, name: "idx_plab_exceptions_run", column: :run_id
remove_index :processing_lab_output, name: "idx_plab_output_run", column: :run_id
remove_index :processing_run_steps, name: "idx_processing_run_steps_run_id", column: :run_id
remove_index :project, name: "idx_project_org", column: :org_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 :sales_account_users, name: "sa_users_sa_idx", column: :sales_account_id
remove_index :session_queries, name: "idx_session_queries_session_id", column: :session_id
remove_index :shipments, name: "idx_shipments_barcode", column: :barcode
remove_index :surcharge_types, name: "idx_surcharge_types_code", column: :code
remove_index :system_user, name: "idx_system_user_id", column: :id
remove_index :tariff_credential, name: "idx_rates_tariff_credential_tariff", column: :tariff_id
remove_index :tariff_user_default, name: "idx_rates_tud_user_carrier", column: [:user_id, :carrier_id]
remove_index :tariff_user_default, name: "idx_rates_tud_user_carrier", column: [:user_id, :carrier_id]
remove_index :tariffs, name: "idx_tariffs_code", column: :code
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_ecommerce_user_default_tariff, name: "idx_user_default_tariff_user_carrier", column: [:user_id, :carrier_id]
remove_index :uk_postcodes_april_2025, name: "idx_postcodes_latlng_group", column: [:lat_group, :lng_group]
remove_index :upload_column_mappings, name: "idx_column_mapping_config", column: :configuration_id
remove_index :upload_transform_run, name: "upload_transform_run_created_at_idx1", column: :"created_at DESC"
remove_index :upload_transform_run, name: "upload_transform_run_status_created_at_idx1", column: [:status, :"created_at DESC"]
remove_index :upload_transform_run, name: "upload_transform_run_supplier_account_id_created_at_idx1", column: [:supplier_account_id, :"created_at DESC"]
remove_index :upload_transform_run_column, name: "idx_transform_run_column_run_id", column: :run_id
remove_index :upload_transform_run_column, name: "idx_upload_transform_run_column_run_order", column: [:run_id, :column_order]
remove_index :upload_transform_run_row, name: "idx_transform_run_row_run_id", column: :run_id
remove_index :upload_transform_run_row, name: "idx_upload_transform_run_row_run_rownum", column: [:run_id, :row_number]

Details
On barcode_registry idx_barcode_registry_barcode (barcode) is covered by uq_barcode_registry_barcode_customer (barcode, customer_id, label_created_at)
On code_symbol idx_code_symbol_repo (repo) is covered by idx_code_symbol_repo_type (repo, symbol_type)
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 idx_shipments_not_cancelled_error (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text])) is covered by idx_shipments_id_status (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))
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 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) is covered by currency_pk (id)
On currency idx_currency_id (id) is covered by currency_pk (id)
On evri_ooa evri_ooa_postcode_index (postcode) is covered by evri_ooa_pk (postcode)
On file_imports idx_file_imports_hash (file_hash) is covered by uq_file_imports_hash_carrier (file_hash, carrier_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 flattened_shipments flattened_shipments_shipment_package_id_idx (shipment_package_id) is covered by flattened_shipments_shipment_package_id_uk (shipment_package_id)
On flattened_shipments_scurri flattened_shipments_scurri_shipment_package_id_idx (shipment_package_id) is covered by flattened_shipments_scurri_shipment_package_id_uk (shipment_package_id)
On intake_account_supplier_mapping idx_acct_supplier_config (config_id) is covered by intake_account_supplier_mapping_config_id_account_pattern_key (config_id, account_pattern)
On intake_config_supplier idx_intake_config_supplier_config (config_id) is covered by intake_config_supplier_config_id_supplier_id_key (config_id, supplier_id)
On intl_consignment_surcharges intl_consignment_surcharges_tsfc_idx (tracking_surcharge_financial_company) is covered by ics_tracking_postcode_financial_company_index (tracking_surcharge_financial_company)
On intl_consignments intl_consignments_tpfc_idx (tracking_postcode_financial_company) is covered by ic_tracking_postcode_financial_company_index (tracking_postcode_financial_company)
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 margin_details idx_margin_details_sales_account (sales_account_id) is covered by idx_margin_details_lookup (sales_account_id, tariff_id, service_id, margin_type, effective_start_date)
On mb_shipments_rollup_mv mb_shipments_rollup_mv_day_customer (day, customer_name) is covered by mb_shipments_rollup_mv_uk (day, customer_name, day_order, week_start_sat)
On pipeline_events idx_pipeline_events_pair_group (pair_group_id, sequence) is covered by pipeline_events_pair_group_id_sequence_key (pair_group_id, sequence)
On processing_lab_exceptions idx_plab_exceptions_run (run_id) is covered by processing_lab_exceptions_run_id_line_index_target_table_key (run_id, line_index, target_table)
On processing_lab_output idx_plab_output_run (run_id) is covered by processing_lab_output_run_id_target_table_key (run_id, target_table)
On processing_run_steps idx_processing_run_steps_run_id (run_id) is covered by processing_run_steps_unique (run_id, step_name)
On project idx_project_org (org_id) is covered by project_org_id_key_key (org_id, key)
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 sales_account_users sa_users_sa_idx (sales_account_id) is covered by sales_account_users_pkey (sales_account_id, user_id, assignment_type)
On session_queries idx_session_queries_session_id (session_id) is covered by idx_session_queries_session (session_id)
On shipments idx_shipments_barcode (barcode) is covered by uq_shipments_barcode_date_carrier (barcode, invoice_date, carrier_id)
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 tariff_credential idx_rates_tariff_credential_tariff (tariff_id) is covered by uq_tariff_credential_natural_key (tariff_id, credential_type)
On tariff_user_default idx_rates_tud_user_carrier (user_id, carrier_id) is covered by tariff_user_default_user_id_carrier_id_tariff_id_key (user_id, carrier_id, tariff_id)
On tariff_user_default idx_rates_tud_user_carrier (user_id, carrier_id) is covered by tariff_user_default_user_id_carrier_id_tariff_id_key (user_id, carrier_id, tariff_id)
On tariffs idx_tariffs_code (code) is covered by tariffs_code_key (code)
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_tariff (supplier_linked_invoice_id, tariff_id)
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_ecommerce_user_default_tariff idx_user_default_tariff_user_carrier (user_id, carrier_id) is covered by uk_ecommerce_user_default_tari_user_id_carrier_id_tariff_id_key (user_id, carrier_id, tariff_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)
On upload_column_mappings idx_column_mapping_config (configuration_id) is covered by upload_column_mappings_configuration_id_target_field_key (configuration_id, target_field)
On upload_transform_run upload_transform_run_created_at_idx1 (created_at DESC) is covered by upload_transform_run_created_at_idx (created_at DESC)
On upload_transform_run upload_transform_run_status_created_at_idx1 (status, created_at DESC) is covered by upload_transform_run_status_created_at_idx (status, created_at DESC)
On upload_transform_run upload_transform_run_supplier_account_id_created_at_idx1 (supplier_account_id, created_at DESC) is covered by upload_transform_run_supplier_account_id_created_at_idx (supplier_account_id, created_at DESC)
On upload_transform_run_column idx_transform_run_column_run_id (run_id) is covered by idx_upload_transform_run_column_run_order (run_id, column_order)
On upload_transform_run_column idx_upload_transform_run_column_run_order (run_id, column_order) is covered by upload_transform_run_column_run_id_column_order_key (run_id, column_order)
On upload_transform_run_row idx_transform_run_row_run_id (run_id) is covered by idx_upload_transform_run_row_run_rownum (run_id, row_number)
On upload_transform_run_row idx_upload_transform_run_row_run_rownum (run_id, row_number) is covered by upload_transform_run_row_run_id_row_number_key (run_id, row_number)

Details

On barcode_registry

idx_barcode_registry_barcode (barcode)

is covered by

uq_barcode_registry_barcode_customer (barcode, customer_id, label_created_at)

On code_symbol

idx_code_symbol_repo (repo)

is covered by

idx_code_symbol_repo_type (repo, symbol_type)

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

idx_shipments_not_cancelled_error (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))

is covered by

idx_shipments_id_status (id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]))

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 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)

is covered by

currency_pk (id)

On currency

idx_currency_id (id)

is covered by

currency_pk (id)

On evri_ooa

evri_ooa_postcode_index (postcode)

is covered by

evri_ooa_pk (postcode)

On file_imports

idx_file_imports_hash (file_hash)

is covered by

uq_file_imports_hash_carrier (file_hash, carrier_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 flattened_shipments

flattened_shipments_shipment_package_id_idx (shipment_package_id)

is covered by

flattened_shipments_shipment_package_id_uk (shipment_package_id)

On flattened_shipments_scurri

flattened_shipments_scurri_shipment_package_id_idx (shipment_package_id)

is covered by

flattened_shipments_scurri_shipment_package_id_uk (shipment_package_id)

On intake_account_supplier_mapping

idx_acct_supplier_config (config_id)

is covered by

intake_account_supplier_mapping_config_id_account_pattern_key (config_id, account_pattern)

On intake_config_supplier

idx_intake_config_supplier_config (config_id)

is covered by

intake_config_supplier_config_id_supplier_id_key (config_id, supplier_id)

On intl_consignment_surcharges

intl_consignment_surcharges_tsfc_idx (tracking_surcharge_financial_company)

is covered by

ics_tracking_postcode_financial_company_index (tracking_surcharge_financial_company)

On intl_consignments

intl_consignments_tpfc_idx (tracking_postcode_financial_company)

is covered by

ic_tracking_postcode_financial_company_index (tracking_postcode_financial_company)

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 margin_details

idx_margin_details_sales_account (sales_account_id)

is covered by

idx_margin_details_lookup (sales_account_id, tariff_id, service_id, margin_type, effective_start_date)

On mb_shipments_rollup_mv

mb_shipments_rollup_mv_day_customer (day, customer_name)

is covered by

mb_shipments_rollup_mv_uk (day, customer_name, day_order, week_start_sat)

On pipeline_events

idx_pipeline_events_pair_group (pair_group_id, sequence)

is covered by

pipeline_events_pair_group_id_sequence_key (pair_group_id, sequence)

On processing_lab_exceptions

idx_plab_exceptions_run (run_id)

is covered by

processing_lab_exceptions_run_id_line_index_target_table_key (run_id, line_index, target_table)

On processing_lab_output

idx_plab_output_run (run_id)

is covered by

processing_lab_output_run_id_target_table_key (run_id, target_table)

On processing_run_steps

idx_processing_run_steps_run_id (run_id)

is covered by

processing_run_steps_unique (run_id, step_name)

On project

idx_project_org (org_id)

is covered by

project_org_id_key_key (org_id, key)

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 sales_account_users

sa_users_sa_idx (sales_account_id)

is covered by

sales_account_users_pkey (sales_account_id, user_id, assignment_type)

On session_queries

idx_session_queries_session_id (session_id)

is covered by

idx_session_queries_session (session_id)

On shipments

idx_shipments_barcode (barcode)

is covered by

uq_shipments_barcode_date_carrier (barcode, invoice_date, carrier_id)

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 tariff_credential

idx_rates_tariff_credential_tariff (tariff_id)

is covered by

uq_tariff_credential_natural_key (tariff_id, credential_type)

On tariff_user_default

idx_rates_tud_user_carrier (user_id, carrier_id)

is covered by

tariff_user_default_user_id_carrier_id_tariff_id_key (user_id, carrier_id, tariff_id)

On tariff_user_default

idx_rates_tud_user_carrier (user_id, carrier_id)

is covered by

tariff_user_default_user_id_carrier_id_tariff_id_key (user_id, carrier_id, tariff_id)

On tariffs

idx_tariffs_code (code)

is covered by

tariffs_code_key (code)

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_tariff (supplier_linked_invoice_id, tariff_id)

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_ecommerce_user_default_tariff

idx_user_default_tariff_user_carrier (user_id, carrier_id)

is covered by

uk_ecommerce_user_default_tari_user_id_carrier_id_tariff_id_key (user_id, carrier_id, tariff_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)

On upload_column_mappings

idx_column_mapping_config (configuration_id)

is covered by

upload_column_mappings_configuration_id_target_field_key (configuration_id, target_field)

On upload_transform_run

upload_transform_run_created_at_idx1 (created_at DESC)

is covered by

upload_transform_run_created_at_idx (created_at DESC)

On upload_transform_run

upload_transform_run_status_created_at_idx1 (status, created_at DESC)

is covered by

upload_transform_run_status_created_at_idx (status, created_at DESC)

On upload_transform_run

upload_transform_run_supplier_account_id_created_at_idx1 (supplier_account_id, created_at DESC)

is covered by

upload_transform_run_supplier_account_id_created_at_idx (supplier_account_id, created_at DESC)

On upload_transform_run_column

idx_transform_run_column_run_id (run_id)

is covered by

idx_upload_transform_run_column_run_order (run_id, column_order)

On upload_transform_run_column

idx_upload_transform_run_column_run_order (run_id, column_order)

is covered by

upload_transform_run_column_run_id_column_order_key (run_id, column_order)

On upload_transform_run_row

idx_transform_run_row_run_id (run_id)

is covered by

idx_upload_transform_run_row_run_rownum (run_id, row_number)

On upload_transform_run_row

idx_upload_transform_run_row_run_rownum (run_id, row_number)

is covered by

upload_transform_run_row_run_id_row_number_key (run_id, row_number)

Total Time	Average Time	Calls
511 min 3%	4,023 ms	7,623 upadmin
SELECT "public"."connexx_shipment_item_descriptions"."id", "public"."connexx_shipment_item_descriptions"."description", "public"."connexx_shipment_item_descriptions"."commodity_code", "public"."connexx_shipment_item_descriptions"."sku", "public"."connexx_shipment_item_descriptions"."item_value", "public"."connexx_shipment_item_descriptions"."weight", "public"."connexx_shipment_item_descriptions"."country_of_origin", "public"."connexx_shipment_item_descriptions"."manufacturer_details", "public"."connexx_shipment_item_descriptions"."currency" FROM "public"."connexx_shipment_item_descriptions" WHERE ("public"."connexx_shipment_item_descriptions"."sales_account_id" = $1 AND "public"."connexx_shipment_item_descriptions"."description" IS NOT NULL AND "public"."connexx_shipment_item_descriptions"."description" ILIKE $2) ORDER BY "public"."connexx_shipment_item_descriptions"."created_date" DESC LIMIT $3 OFFSET $4
52 min 0.3%	418 ms	7,443 upadmin
SELECT "public"."connexx_shipment_item_descriptions"."id", "public"."connexx_shipment_item_descriptions"."sku", "public"."connexx_shipment_item_descriptions"."description", "public"."connexx_shipment_item_descriptions"."commodity_code", "public"."connexx_shipment_item_descriptions"."item_value", "public"."connexx_shipment_item_descriptions"."weight", "public"."connexx_shipment_item_descriptions"."country_of_origin", "public"."connexx_shipment_item_descriptions"."manufacturer_details", "public"."connexx_shipment_item_descriptions"."currency" FROM "public"."connexx_shipment_item_descriptions" WHERE ("public"."connexx_shipment_item_descriptions"."sales_account_id" = $1 AND "public"."connexx_shipment_item_descriptions"."sku" IS NOT NULL AND "public"."connexx_shipment_item_descriptions"."sku" ILIKE $2) ORDER BY "public"."connexx_shipment_item_descriptions"."created_date" DESC LIMIT $3 OFFSET $4

Total Time	Average Time	Calls
903 min 6%	9,100 ms	5,954 upadmin
`SELECT "public"."connexx_shipments"."id", "public"."connexx_shipments"."integration_store_id" FROM "public"."connexx_shipments" WHERE ("public"."connexx_shipments"."sales_account_id" = $1 AND "public"."connexx_shipments"."store_name" ILIKE $2 AND "public"."connexx_shipments"."integration_store_id" IS NOT NULL) OFFSET $3`

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
3,591 min 24%	2,943 ms	73,211 upadmin
`SELECT "public"."connexx_shipments"."id", "public"."connexx_shipments"."store_name" FROM "public"."connexx_shipments" WHERE ("public"."connexx_shipments"."sales_account_id" = $1 AND "public"."connexx_shipments"."store_name" IS NOT NULL) OFFSET $2` Details `CREATE INDEX CONCURRENTLY ON connexx_shipments (store_name)` Rows: 21926344 Row progression: 21926344, 0 Row estimates - store_name (not_null): 0 - sales_account_id (=): 36302 Existing indexes - id PRIMARY - api_id, sales_account_id - carrier_shipment_id - created_by - created_date_date - customer_reference gin_trgm_ops GIN - customer_reference_2 WHERE (customer_reference_2 IS NOT NULL) AND ((customer_reference_2)::text <> ''::text) - customer_reference_2 gin_trgm_ops GIN - from_address_company gin_trgm_ops GIN - id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]) - id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]) - integration_store_id, sales_account_id - invoice_number WHERE (invoice_number IS NOT NULL) AND ((invoice_number)::text <> ''::text) - job_number - lower((customer_reference)::text) - lower((estimated_delivery_date)::text) - lower((from_address_first_name)::text) - lower((from_address_last_name)::text) - lower((from_address_street_1)::text) - lower((from_address_zip)::text) - lower((order_number)::text), shipment_source_name, sales_account_id, status - lower((to_address_company)::text) - lower((to_address_first_name)::text) - lower((to_address_last_name)::text) - lower((to_address_street_1)::text) - lower((to_address_zip)::text) - order_number - order_number WHERE (order_number IS NOT NULL) AND ((order_number)::text <> ''::text) - order_number gin_trgm_ops GIN - sales_account_id, created_date - sales_account_id, created_date, shipment_type, status - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'cancelled'::text) - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'error'::text) - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'label_created'::text) - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'ready_to_print'::text) - sales_account_id, customer_reference - shipment_bulk_upload_id - shipment_integration_id, lower((customer_reference)::text) - shipment_type - status, carrier_api_request_json - to_address_company gin_trgm_ops GIN - ups_pickup_prn_number - uuid UNIQUE
1,053 min 7%	395 ms	159,866 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: 17068888 Row progression: 17068888, 30699 Row estimates - sales_account_id (=): 30699 Existing indexes - id PRIMARY - deprecated_shipment_ids - sales_account_id, created_date - uuid UNIQUE`
999 min 7%	1,320 ms	45,388 upadmin
SELECT COUNT("public"."connexx_shipment_packages"."id"), "public"."connexx_shipment_packages"."type", "public"."connexx_shipment_packages"."length", "public"."connexx_shipment_packages"."width", "public"."connexx_shipment_packages"."height" FROM "public"."connexx_shipment_packages" WHERE "public"."connexx_shipment_packages"."sales_account_id" = $1 GROUP BY "public"."connexx_shipment_packages"."type", "public"."connexx_shipment_packages"."length", "public"."connexx_shipment_packages"."width", "public"."connexx_shipment_packages"."height" ORDER BY COUNT("public"."connexx_shipment_packages"."id") DESC LIMIT $2 OFFSET $3 Covered by index on (sales_account_id, created_date) Rows: 23469268 Row progression: 23469268, 36671 Row estimates - sales_account_id (=): 36671 Existing indexes - id PRIMARY - ((created_date)::date), tracking_code, carrier_shipment_package_id - created_date, id - created_date_date - sales_account_id, created_date - shipment_id - tracking_code, created_date - tracking_code, created_date WHERE tracking_code IS NOT NULL - tracking_code WHERE (tracking_code IS NOT NULL) AND ((tracking_code)::text <> ''::text) - tracking_code gin_trgm_ops GIN - tracking_code gin_trgm_ops GIN
903 min 6%	9,100 ms	5,954 upadmin
`SELECT "public"."connexx_shipments"."id", "public"."connexx_shipments"."integration_store_id" FROM "public"."connexx_shipments" WHERE ("public"."connexx_shipments"."sales_account_id" = $1 AND "public"."connexx_shipments"."store_name" ILIKE $2 AND "public"."connexx_shipments"."integration_store_id" IS NOT NULL) OFFSET $3` Details `CREATE INDEX CONCURRENTLY ON connexx_shipments USING gist (store_name gist_trgm_ops)` Rows: 21926344 Row progression: 21926344, 0 Row estimates - store_name (~~*): 0 - sales_account_id (=): 36302 - integration_store_id (not_null): 4315835 Existing indexes - id PRIMARY - api_id, sales_account_id - carrier_shipment_id - created_by - created_date_date - customer_reference gin_trgm_ops GIN - customer_reference_2 WHERE (customer_reference_2 IS NOT NULL) AND ((customer_reference_2)::text <> ''::text) - customer_reference_2 gin_trgm_ops GIN - from_address_company gin_trgm_ops GIN - id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]) - id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]) - integration_store_id, sales_account_id - invoice_number WHERE (invoice_number IS NOT NULL) AND ((invoice_number)::text <> ''::text) - job_number - lower((customer_reference)::text) - lower((estimated_delivery_date)::text) - lower((from_address_first_name)::text) - lower((from_address_last_name)::text) - lower((from_address_street_1)::text) - lower((from_address_zip)::text) - lower((order_number)::text), shipment_source_name, sales_account_id, status - lower((to_address_company)::text) - lower((to_address_first_name)::text) - lower((to_address_last_name)::text) - lower((to_address_street_1)::text) - lower((to_address_zip)::text) - order_number - order_number WHERE (order_number IS NOT NULL) AND ((order_number)::text <> ''::text) - order_number gin_trgm_ops GIN - sales_account_id, created_date - sales_account_id, created_date, shipment_type, status - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'cancelled'::text) - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'error'::text) - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'label_created'::text) - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'ready_to_print'::text) - sales_account_id, customer_reference - shipment_bulk_upload_id - shipment_integration_id, lower((customer_reference)::text) - shipment_type - status, carrier_api_request_json - to_address_company gin_trgm_ops GIN - ups_pickup_prn_number - uuid UNIQUE
887 min 6%	604 ms	88,102 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
593 min 4%	218 ms	163,142 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"`
511 min 3%	4,023 ms	7,623 upadmin
SELECT "public"."connexx_shipment_item_descriptions"."id", "public"."connexx_shipment_item_descriptions"."description", "public"."connexx_shipment_item_descriptions"."commodity_code", "public"."connexx_shipment_item_descriptions"."sku", "public"."connexx_shipment_item_descriptions"."item_value", "public"."connexx_shipment_item_descriptions"."weight", "public"."connexx_shipment_item_descriptions"."country_of_origin", "public"."connexx_shipment_item_descriptions"."manufacturer_details", "public"."connexx_shipment_item_descriptions"."currency" FROM "public"."connexx_shipment_item_descriptions" WHERE ("public"."connexx_shipment_item_descriptions"."sales_account_id" = $1 AND "public"."connexx_shipment_item_descriptions"."description" IS NOT NULL AND "public"."connexx_shipment_item_descriptions"."description" ILIKE $2) ORDER BY "public"."connexx_shipment_item_descriptions"."created_date" DESC LIMIT $3 OFFSET $4 Details `CREATE INDEX CONCURRENTLY ON connexx_shipment_item_descriptions (sales_account_id)` Rows: 33532000 Row progression: 33532000, 58114, 56960 Row estimates - sales_account_id (=): 58114 - description (~~*): 3286738 - description (not_null): 32865831 - created_date (sort): 106 Existing indexes - id PRIMARY - lower((description)::text) - lower((sku)::text) - shipment_id - shipment_id, lower((sku)::text), lower((description)::text) WHERE shipment_id IS NOT NULL - shipment_id WHERE shipment_id IS NOT NULL - sku - sku gin_trgm_ops, description gin_trgm_ops GIN - sku gin_trgm_ops WHERE shipment_id IS NOT NULL GIN
414 min 3%	337 ms	73,730 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
353 min 2%	4,978 ms	4,249 upadmin
SELECT * FROM ( SELECT DISTINCT ON (csp.tracking_code) csp.tracking_code, cs.id AS shipment_id, cs.created_date AS shipment_created_date, csp.randomized_reference, sasw.carrier_id, ct.id AS consignment_tracking_id, ct.created_date AS consignment_tracking_created_date, COALESCE(ct.tracking_information, $5::jsonb) AS existing_tracking_information FROM connexx_shipments cs JOIN connexx_shipment_packages csp ON csp.shipment_id = cs.id JOIN sales_account_shipping_whitelist sasw ON sasw.id = cs.shipping_whitelist_id LEFT JOIN consignment_tracking ct ON ct.barcode = csp.tracking_code WHERE sasw.supplier_id = $1 AND COALESCE(csp.tracking_code, $6) <> $7 AND COALESCE(csp.randomized_reference, $8) <> $9 AND cs.created_date >= NOW() - $2::interval ORDER BY csp.tracking_code, cs.created_date DESC ) candidates ORDER BY shipment_created_date ASC, tracking_code ASC OFFSET $3 LIMIT $4
300 min 2%	21 ms	868,371 upadmin
SELECT "public"."consignment_unified_statuses"."id", "public"."consignment_unified_statuses"."created_at", "public"."consignment_unified_statuses"."tracking_code", "public"."consignment_unified_statuses"."client_description", "public"."consignment_unified_statuses"."unified_status", "public"."consignment_unified_statuses"."carrier_id", "public"."consignment_unified_statuses"."carrier", "public"."consignment_unified_statuses"."ghost_scan", "public"."consignment_unified_statuses"."exception_type" FROM "public"."consignment_unified_statuses" WHERE $2=$3 OFFSET $1
159 min 1%	227 ms	41,917 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", "public"."connexx_shipments"."customer_order_source", "public"."connexx_shipments"."ignore_integration_update", "public"."connexx_shipments"."requires_saturday_delivery", "public"."connexx_shipments"."requires_sunday_delivery", "public"."connexx_shipments"."epl_label", "public"."connexx_shipments"."store_name", "public"."connexx_shipments"."shipment_date_from_ui", "public"."connexx_shipments"."label_generated_at", "public"."connexx_shipments"."api_imperial_units_used", "public"."connexx_shipments"."used_import_items_ai_feature" 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 N
131 min 0.9%	78 ms	100,083 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`
124 min 0.8%	47 ms	159,866 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"`
109 min 0.7%	328 ms	19,918 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", "public"."connexx_shipment_packages"."randomized_reference", "public"."connexx_shipment_packages"."epl_label", "public"."connexx_shipment_packages"."used_default_length", "public"."connexx_shipment_packages"."used_default_width", "public"."connexx_shipment_packages"."used_default_height", "public"."connexx_shipment_packages"."used_default_weight" 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 /, ... /) AND "public"."connexx_shipment_packages"."sales_account_id" IN ($2 /, ... /) AND ("j1"."shipping_whitelist_id" IS NOT NULL AND ("j1"."id" IS NOT NULL))) OFFSET $3
95 min 0.6%	1,510 ms	3,761 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, cs.id, sa.id, sa.sales_account_reference, co.id, UPPER(co.name), csp.tracking_code, UPPER(cs.status), cs.created_date, UPPER(cs.shipment_source_name), UPPER(combined_suppliers.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 LEFT JOIN shipment_data.flattened_shipments fs ON fs.shipment_package_id = csp.id WHERE csp.tracking_code IS NOT NULL AND upper(cs.status) <> $1 AND csp.created_date >= date_trunc($2, current_date) - INTERVAL $3 AND csp.sales_account_id NOT IN ($4 /, ... /) AND fs.shipment_package_id IS NULL
88 min 0.6%	110 ms	47,904 upadmin
SELECT "public"."consignment_tracking"."id", "public"."consignment_tracking"."status", "public"."consignment_tracking"."created_by", "public"."consignment_tracking"."created_date", "public"."consignment_tracking"."barcode", "public"."consignment_tracking"."carrier_id", "public"."consignment_tracking"."type", "public"."consignment_tracking"."tracking_information", "public"."consignment_tracking"."consignment_number" FROM "public"."consignment_tracking" WHERE "public"."consignment_tracking"."barcode" IN ($1 /, ... /) OFFSET $2 `Covered by index on (barcode) Rows: 36648700 Row progression: 36648700, 1 Row estimates - barcode (=): 1 Existing indexes - id PRIMARY - (((tracking_information -> '-1'::integer) -> 'trackingEvent'::text)) GIN - (((tracking_information -> 'statusCode'::text))::text) - ((created_date)::date) - barcode UNIQUE - barcode UNIQUE - carrier_id - consignment_number`
79 min 0.5%	1,253 ms	3,761 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 <> UPPER(cs.status)`
71 min 0.5%	1,126 ms	3,761 upadmin
INSERT INTO shipment_data.flattened_shipments_scurri ( shipment_package_id, sales_account_id, sales_account_reference, company_id, customer_name, tracking_code, created_date, supplier_name, iso_country_destination, zip_destination ) SELECT ci.id, sa.id, sa.sales_account_reference, co.id, UPPER(co.name), ci.barcode, ci.created_date, UPPER(ci.carrier), ci.ship_to_country, ci.ship_to_postcode FROM public.consignment_imports ci LEFT JOIN public.sales_account sa ON sa.id = ci.sales_account_id LEFT JOIN public.company co ON co.id = sa.company_id LEFT JOIN shipment_data.flattened_shipments_scurri fs ON fs.shipment_package_id = ci.id WHERE ci.barcode IS NOT NULL AND ci.source = $1 AND ci.created_date_date >= current_date - $2 AND ci.created_date_date <= current_date AND ci.sales_account_id NOT IN ($3 /, ... /) AND fs.shipment_package_id IS NULL
66 min 0.4%	278 ms	14,247 postgresitd
`SELECT EXISTS ( SELECT $3 FROM connexx_shipments WHERE customer_reference = $1 AND shipment_integration_id = $2) AS exists`
52 min 0.3%	418 ms	7,443 upadmin
SELECT "public"."connexx_shipment_item_descriptions"."id", "public"."connexx_shipment_item_descriptions"."sku", "public"."connexx_shipment_item_descriptions"."description", "public"."connexx_shipment_item_descriptions"."commodity_code", "public"."connexx_shipment_item_descriptions"."item_value", "public"."connexx_shipment_item_descriptions"."weight", "public"."connexx_shipment_item_descriptions"."country_of_origin", "public"."connexx_shipment_item_descriptions"."manufacturer_details", "public"."connexx_shipment_item_descriptions"."currency" FROM "public"."connexx_shipment_item_descriptions" WHERE ("public"."connexx_shipment_item_descriptions"."sales_account_id" = $1 AND "public"."connexx_shipment_item_descriptions"."sku" IS NOT NULL AND "public"."connexx_shipment_item_descriptions"."sku" ILIKE $2) ORDER BY "public"."connexx_shipment_item_descriptions"."created_date" DESC LIMIT $3 OFFSET $4 Details `CREATE INDEX CONCURRENTLY ON connexx_shipment_item_descriptions (sales_account_id)` Rows: 33532000 Row progression: 33532000, 58114, 49808 Row estimates - sales_account_id (=): 58114 - sku (~~*): 2874062 - sku (not_null): 28739159 - created_date (sort): 106 Existing indexes - id PRIMARY - lower((description)::text) - lower((sku)::text) - shipment_id - shipment_id, lower((sku)::text), lower((description)::text) WHERE shipment_id IS NOT NULL - shipment_id WHERE shipment_id IS NOT NULL - sku - sku gin_trgm_ops, description gin_trgm_ops GIN - sku gin_trgm_ops WHERE shipment_id IS NOT NULL GIN
50 min 0.3%	7,865 ms	384 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, $1 AS return_match, $2 AS customer_name, $3 AS trading_name, $4 AS sales_account_id, $5 AS sales_account_reference, $6 AS ship_to_postcode, $7 AS carrier, $8 AS dom_intl, $9 AS carrier_id, $10 AS depot, $11 AS return_method, $12 AS shipper_reference, $13 AS returns_address, COALESCE(rc.created_date, $14) AS return_received_date, COALESCE(rc.created_by, $15) AS return_received_by FROM returns_consignment rc WHERE rc.awb = $16), 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 = $56), intl_billed AS (SELECT DISTINCT ON (ic.consignment_number) COALESCE(ic.barcode, ic.consignment_number) AS outgoing_barcode, $57 AS parent_table, ukec.id::bigint AS parent_table_id, $58 AS return_match, COALESCE(RTRIM(company.name), $59) AS customer_name, COALESCE(sa.trade_name, $60) AS trading_name, COALESCE(sa.id, $61) AS sales_account_id, COALESCE(sa.sales_account_reference, $62) AS sales_account_reference, COALESCE(ic.ship_to_postcode, $63) AS ship_to_postcode, COALESCE(i_carrier.name, $64) AS carrier, $65 AS dom_intl, COALESCE(i_carrier.id, $66) AS carrier_id, CASE WHEN carrier_cd.name IS NOT NULL AND carrier_cd.name <> $67 THEN carrier_cd.name ELSE COALESCE(general_cd.name, $68) END AS depot, COALESCE(sa.return_method, $69) AS return_method, COALESCE(CASE WHEN ic.shipper_reference_1 IS NOT NULL AND ic.shipper_reference_1 <> $70 THEN ic.shipper_reference_1 ELSE ic.order_number END, $71) AS shipper_reference, regexp_replace(COALESCE(aan.address_line_1, $72) \|\| chr($73) \|\| COALESCE(aan.address_line_2, $74) \|\| chr($75) \|\| COALESCE(aan.address_line_3, $76) \|\| chr($77) \|\| COALESCE(aan.city, $78) \|\| chr($79) \|\| COALESCE(aan.county_state, $80) \|\| chr($81) \|\| COALESCE(aan.postcode, $82) \|\| chr($83) \|\| COALESCE(aan.country, $84), $85, $86, $87) AS returns_address, COALESCE(ic.return_received_date, $88) AS return_received_date, COALESCE(ic.return_received_by, $89) 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 = $90 AND aan.status = $91 LEFT JOIN sales_account_collection_types carrier_sact ON sa.id = carrier_sact.sales_account_id AND carrier_sact.domestic_international = $92 AND carrier_sact.carrier_id = i_carrier.id AND carrier_sact.collected_by = $93 AND carrier_sact.status = $94 LEFT JOIN sales_account_collection_types general_sact ON sa.id = general_sact.sales_account_id AND general_sact.collected_by = $95 AND general_sact.status = $96 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 = $97 OR ic.consignment_number = $98)), imports AS (SELECT DISTINCT ukec.barcode AS outgoing_barcode, $99 AS parent_table, ukec.id::bigint AS parent_table_id, $100 AS return_match, COALESCE(RTRIM(company.name), $101) AS customer_name, COALESCE(sa.trade_name, $102) AS trading_name, COALESCE(ukec.sales_account_id, $103) AS sales_account_id, COALESCE(ukec.sales_account_reference, $104) AS sales_account_reference, COALESCE(ship_to_postcode, $105) AS ship_to_postcode, COALESCE(carrier, $106) AS carrier, CASE WHEN carrier = $107 AND service = $108 THEN $109 WHEN carrier = $110 AND service <> $111 THEN $112 WHEN carrier = $113 THEN $114 ELSE $115 END AS dom_intl, CASE WHEN carrier ilike $116 THEN $117 WHEN carrier ilike $118 THEN $119 WHEN carrier ilike $120 THEN $121 WHEN (carrier ilike $122 OR carrier ilike $123) THEN $124 WHEN carrier ilike $125 THEN $126 WHEN carrier ilike $127 THEN $128 WHEN carrier ilike $129 THEN $130 WHEN (carrier ilike $131 OR carrier ilike $132) THEN $133 WHEN carrier ilike $134 THEN $135 WHEN carrier ilike $136 THEN $137 WHEN carrier ilike $138 THEN $139 WHEN carrier ilike $140 THEN $141 ELSE COALESCE((SELECT id FROM intl_carrier WHERE (name = carrier OR similarity(name, carrier) > $142) AND financial_company = $143), $144) END AS carrier_id, (SELECT COALESCE(COALESCE(CASE WHEN carrier = $145 AND service = $146 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 = $147 AND sact.carrier_id = $148 AND sact.collected_by = $149) WHEN carrier = $150 AND service <> $151 THEN (SELECT cd.name FROM sales_account_collection_types sact LEFT JOIN carrier_depots cd ON sact.depot_id = cd.id WHERE sact.status = $152 AND sact.sales_account_id = ukec.sales_account_id AND sact.domestic_international = $153 AND sact.carrier_id = $154 AND sact.collected_by = $155) WHEN carrier = $156 THEN (SELECT cd.name FROM sales_account_collection_types sact LEFT JOIN carrier_depots cd ON sact.depot_id = cd.id WHERE sact.status = $157 AND sact.sales_account_id = ukec.sales_account_id AND sact.domestic_international = $158 AND sact.carrier_id = $159 AND sact.collected_by = $160) WHEN carrier ilike $161 THEN (SELECT cd.name FROM sales_account_collection_types sact LEFT JOIN carrier_depots cd ON sact.depot_id = cd.id WHERE sact.status = $162 AND sact.sales_account_id = ukec.sales_account_id AND sact.domestic_international = $163 AND sact.carrier_id = $164 AND sact.collected_by = $165) WHEN (carrier ilike $166 OR carrier ilike $167) THEN (SELECT cd.name FROM sales_account_collection_types sact LEFT JOIN carrier_depots cd ON sact.depot_id = cd.id WHERE sact.status = $168 AND sact.sales_account_id = ukec.sales_account_id AND sact.domestic_international = $169 AND sact.carrier_id = $170 AND sact.collected_by = $171) WHEN carrier ilike $172 THEN (SELECT cd.name FROM sales_account_collection_types sact LEFT JOIN carrier_depots
42 min 0.3%	1,601 ms	1,578 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: 84220648 Row progression: 84220648, 76731, 71 Row estimates - sales_account_reference (=): 76731 - sales_account_id (=): 78031 - status (=): 42099095 Existing indexes - id PRIMARY - ((created_date)::date) - barcode - barcode_postcode_key UNIQUE - carrier - consignment_number - consignment_number_postcode_key - created_date_date, id WHERE ((source)::text = 'scurri'::text) AND (barcode IS NOT NULL) AND (sales_account_id <> ALL (ARRAY[225578, 1605, 0])) - 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
32 min 0.2%	1,464 ms	1,309 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
32 min 0.2%	21 ms	91,445 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", "public"."connexx_shipments"."customer_order_source", "public"."connexx_shipments"."ignore_integration_update", "public"."connexx_shipments"."requires_saturday_delivery", "public"."connexx_shipments"."requires_sunday_delivery", "public"."connexx_shipments"."epl_label", "public"."connexx_shipments"."store_name", "public"."connexx_shipments"."shipment_date_from_ui", "public"."connexx_shipments"."label_generated_at", "public"."connexx_shipments"."api_imperial_units_used", "public"."connexx_shipments"."used_import_items_ai_feature" 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) ORDER BY "public"."connexx_shipments"."created_date" DESC LIMIT $3 OFFSET $4 Covered by index on (sales_account_id, customer_reference) Rows: 21926344 Row progression: 21926344, 36302, 18151 Row estimates - sales_account_id (=): 36302 - shipment_type (=): 10963172 - deleted_date (null): 20525251 - created_date (sort): 9 Existing indexes - id PRIMARY - api_id, sales_account_id - carrier_shipment_id - created_by - created_date_date - customer_reference gin_trgm_ops GIN - customer_reference_2 WHERE (customer_reference_2 IS NOT NULL) AND ((customer_reference_2)::text <> ''::text) - customer_reference_2 gin_trgm_ops GIN - from_address_company gin_trgm_ops GIN - id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]) - id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]) - integration_store_id, sales_account_id - invoice_number WHERE (invoice_number IS NOT NULL) AND ((invoice_number)::text <> ''::text) - job_number - lower((customer_reference)::text) - lower((estimated_delivery_date)::text) - lower((from_address_first_name)::text) - lower((from_address_last_name)::text) - lower((from_address_street_1)::text) - lower((from_address_zip)::text) - lower((order_number)::text), shipment_source_name, sales_account_id, status - lower((to_address_company)::text) - lower((to_address_first_name)::text) - lower((to_address_last_name)::text) - lower((to_address_street_1)::text) - lower((to_address_zip)::text) - order_number - order_number WHERE (order_number IS NOT NULL) AND ((order_number)::text <> ''::text) - order_number gin_trgm_ops GIN - sales_account_id, created_date - sales_account_id, created_date, shipment_type, status - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'cancelled'::text) - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'error'::text) - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'label_created'::text) - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'ready_to_print'::text) - sales_account_id, customer_reference - shipment_bulk_upload_id - shipment_integration_id, lower((customer_reference)::text) - shipment_type - status, carrier_api_request_json - to_address_company gin_trgm_ops GIN - ups_pickup_prn_number - uuid UNIQUE
28 min 0.2%	968 ms	1,759 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
27 min 0.2%	53 ms	30,307 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", "public"."connexx_shipments"."customer_order_source", "public"."connexx_shipments"."ignore_integration_update", "public"."connexx_shipments"."requires_saturday_delivery", "public"."connexx_shipments"."requires_sunday_delivery", "public"."connexx_shipments"."epl_label", "public"."connexx_shipments"."store_name", "public"."connexx_shipments"."shipment_date_from_ui", "public"."connexx_shipments"."label_generated_at", "public"."connexx_shipments"."api_imperial_units_used", "public"."connexx_shipments"."used_import_items_ai_feature" FROM "public"."connexx_shipments" WHERE "public"."connexx_shipments"."id" IN ($1 /, ... /) OFFSET $2 Covered by index on (id) Rows: 21926344 Row progression: 21926344, 1 Row estimates - id (=): 1 Existing indexes - id PRIMARY - api_id, sales_account_id - carrier_shipment_id - created_by - created_date_date - customer_reference gin_trgm_ops GIN - customer_reference_2 WHERE (customer_reference_2 IS NOT NULL) AND ((customer_reference_2)::text <> ''::text) - customer_reference_2 gin_trgm_ops GIN - from_address_company gin_trgm_ops GIN - id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]) - id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]) - integration_store_id, sales_account_id - invoice_number WHERE (invoice_number IS NOT NULL) AND ((invoice_number)::text <> ''::text) - job_number - lower((customer_reference)::text) - lower((estimated_delivery_date)::text) - lower((from_address_first_name)::text) - lower((from_address_last_name)::text) - lower((from_address_street_1)::text) - lower((from_address_zip)::text) - lower((order_number)::text), shipment_source_name, sales_account_id, status - lower((to_address_company)::text) - lower((to_address_first_name)::text) - lower((to_address_last_name)::text) - lower((to_address_street_1)::text) - lower((to_address_zip)::text) - order_number - order_number WHERE (order_number IS NOT NULL) AND ((order_number)::text <> ''::text) - order_number gin_trgm_ops GIN - sales_account_id, created_date - sales_account_id, created_date, shipment_type, status - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'cancelled'::text) - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'error'::text) - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'label_created'::text) - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'ready_to_print'::text) - sales_account_id, customer_reference - shipment_bulk_upload_id - shipment_integration_id, lower((customer_reference)::text) - shipment_type - status, carrier_api_request_json - to_address_company gin_trgm_ops GIN - ups_pickup_prn_number - uuid UNIQUE
14 min < 0.1%	1,033 ms	832 upadmin
`SELECT MAX("effective_date") FROM (SELECT "public"."exchange_rates"."effective_date" FROM "public"."exchange_rates" WHERE $2=$3 OFFSET $1) AS "sub"`
13 min < 0.1%	3,154 ms	239 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
12 min < 0.1%	5,389 ms	133 postgresitd
WITH returned_to_itd AS (SELECT COALESCE((SELECT created_date FROM returns_consignment WHERE awb = $1), $2) AS date) SELECT ic.id, $3 as shipment_type, CASE WHEN i_carrier.eu = $4 THEN $5 ELSE $6 END AS transaction_type, CASE WHEN icst.sales_nominal_transaction_id > $7 THEN $8 WHEN icst.supplier_nominal_transaction_id <> $9 THEN $10 ELSE $11 END AS status, ic.consignment_number, COALESCE(ic.shipped_date, ic.invoice_date) AS transaction_date, COALESCE(CONCAT(RTRIM(company.name), $12, sa.sales_account_reference), $13) AS customer_name, i_carrier.name AS carrier_name, i_supplier.name AS supplier_name, i_service.name AS service_name, isi.supplier_invoice_number as supplier_invoice_number, CASE WHEN ic.tracking_postcode_financial_company ~* $14 THEN CONCAT($15, $16, icst.sales_exception_code) ELSE icst.sales_exception_code END as exception_code, ic.zone_name, ic.charged_weight, icst.sales_freight_amount, COALESCE((SELECT SUM(icst_s.sales_freight_amount) FROM intl_consignments_surcharges_transactions icst_s WHERE icst_s.consignment_id = icst.consignment_id AND icst_s.consignment_surcharge_id <> $17), $18) AS sales_surcharges, icst.sales_fuel_surcharge_amount, icst.sales_freight_amount + COALESCE((SELECT SUM(icst_s.sales_freight_amount) FROM intl_consignments_surcharges_transactions icst_s WHERE icst_s.consignment_id = icst.consignment_id AND icst_s.consignment_surcharge_id <> $19), $20) + icst.sales_fuel_surcharge_amount AS sales_total, icst.supplier_freight_amount, COALESCE((SELECT SUM(icst_s.supplier_freight_amount) FROM intl_consignments_surcharges_transactions icst_s WHERE icst_s.consignment_id = icst.consignment_id AND icst_s.consignment_surcharge_id <> $21), $22) AS supplier_surcharges, icst.supplier_fuel_surcharge_amount, icst.supplier_freight_amount + COALESCE((SELECT SUM(icst_s.supplier_freight_amount) FROM intl_consignments_surcharges_transactions icst_s WHERE icst_s.consignment_id = icst.consignment_id AND icst_s.consignment_surcharge_id <> $23), $24) + icst.supplier_fuel_surcharge_amount AS supplier_total, icst.sales_freight_amount + COALESCE((SELECT SUM(icst_s.sales_freight_amount) FROM intl_consignments_surcharges_transactions icst_s WHERE icst_s.consignment_id = icst.consignment_id AND icst_s.consignment_surcharge_id <> $25), $26) + icst.sales_fuel_surcharge_amount - icst.supplier_freight_amount - COALESCE((SELECT SUM(icst_s.supplier_freight_amount) FROM intl_consignments_surcharges_transactions icst_s WHERE icst_s.consignment_id = icst.consignment_id AND icst_s.consignment_surcharge_id <> $27), $28) - icst.supplier_fuel_surcharge_amount AS profit, COALESCE(ntl_i_supplier.purchase_transaction_id, $29) AS pat_id, COALESCE(ntl_i_supplier.reference, $30) AS pa_transaction_reference, COALESCE(ntl_sa.sales_transaction_id, $31) AS sat_id, COALESCE(ntl_sa.reference, $32) AS sa_transaction_reference, $33 AS consignment_vat_duty_id, returned_to_itd.date AS returned_to_itd_date FROM intl_consignments_surcharges_transactions icst LEFT JOIN intl_consignments ic ON ic.id = icst.consignment_id LEFT JOIN intl_service i_service ON i_service.id = ic.service_id LEFT JOIN intl_supplier i_supplier ON i_supplier.id = icst.supplier_account_id LEFT JOIN intl_carrier i_carrier ON i_carrier.id = i_service.carrier_id LEFT JOIN sales_account sa ON sa.id = icst.sales_account_id LEFT JOIN company ON company.id = sa.company_id LEFT JOIN nominal_transaction_lines ntl_i_supplier ON ntl_i_supplier.id = icst.supplier_nominal_transaction_id LEFT JOIN nominal_transaction_lines ntl_sa ON ntl_sa.id = icst.sales_nominal_transaction_id LEFT JOIN intl_supplier_invoice isi ON isi.id = icst.supplier_linked_invoice_id LEFT JOIN returned_to_itd ON $34 WHERE /ic.ts_text_search @@ to_tsquery(UPPER($1))/ (ic.barcode = $1 OR ic.consignment_number = $1 OR ic.order_number = $1 OR ic.shipper_reference_1 = $1 OR ic.shipper_reference_2 = $1) AND icst.consignment_surcharge_id = $35 AND ic.status = $36 AND icst.status = $37 UNION ALL SELECT ukec.id, $38 as shipment_type, $39 AS transaction_type, CASE WHEN ukec.sales_nominal_transaction_id > $40 THEN $41 WHEN ukec.supplier_nominal_transaction_id <> $42 THEN $43 ELSE $44 END AS status, ukec.barcode, COALESCE(ukec.print_date, ukec.invoice_date) AS transaction_date, COALESCE(CONCAT(RTRIM(company.name), $45, sa.sales_account_reference), $46) AS customer_name, ukec_carrier.name AS carrier_name, ukec_supplier.name AS supplier_name, ukec_service.name AS service_name, ukec.supplier_invoice_number as supplier_invoice_number, ukec.exception_code as exception_code, $47 as zone_name, ukec.package_weight, ukec.sales_price, ukec.surcharge_sales_price AS sales_surcharges, COALESCE(ukec.sales_fuel_surcharge_amount, $48), ukec.sales_price + ukec.surcharge_sales_price + COALESCE(ukec.sales_fuel_surcharge_amount, $49) AS sales_total, ukec.supplier_cost_price, ukec.surcharge_cost_price AS supplier_surcharges, $50, ukec.supplier_cost_price + ukec.surcharge_cost_price AS supplier_total, (ukec.sales_price + COALESCE(ukec.sales_fuel_surcharge_amount, $51)) - (ukec.supplier_cost_price + ukec.surcharge_cost_price) AS profit, COALESCE(ntl_i_supplier.purchase_transaction_id, $52) AS pat_id, COALESCE(ntl_i_supplier.reference, $53) AS pa_transaction_reference, COALESCE(ntl_sa.sales_transaction_id, $54) AS sat_id, COALESCE(ntl_sa.reference, $55) AS sa_transaction_reference, $56 AS consignment_vat_duty_id, returned_to_itd.date AS returned_to_itd_date FROM uk_ecommerce_consignments ukec LEFT JOIN uk_ecommerce_supplier ukec_supplier ON ukec_supplier.id = ukec.supplier_account_id LEFT JOIN uk_ecommerce_carrier ukec_carrier ON ukec_carrier.id = ukec_supplier.carrier_id LEFT JOIN uk_ecommerce_service ukec_service ON ukec_service.id = ukec.service_id LEFT JOIN sales_account sa ON sa.id = ukec.sales_account_id LEFT JOIN company ON company.id = sa.company_id LEFT JOIN nominal_transaction_lines ntl_i_supplier ON ntl_i_supplier.id = ukec.supplier_nominal_transaction_id LEFT JOIN nominal_transaction_lines ntl_sa ON ntl_sa.id = ukec.sales_nominal_transaction_id LEFT JOIN returned_to_itd ON $57 WHERE /ukec.ts_text_search @@ to_tsquery(UPPER($1))/ (ukec.barcode = $1 OR ukec.consignment_number = $1 OR ukec.order_number = $1 OR ukec.shipper_reference_1 = $1 OR ukec.shipper_reference_2 = $1) AND ukec.status = $58 UNION ALL SELECT ukecs.id, $59 as shipment_type, $60 AS transaction_type, CASE WHEN ukecs.sales_nominal_transaction_id > $61 THEN $62 WHEN ukecs.supplier_nominal_transaction_id <> $63 THEN $64 ELSE $65 END AS status, ukecs.barcode, ukecs.invoice_date AS transaction_date, COALESCE(CONCAT(RTRIM(company.name), $66, sa.sales_account_reference), $67) AS customer_name, ukec_carrier.name AS carrier_name, ukec_supplier.name AS supplier_name, COALESCE(ukec_service.name, $68) AS service_name, ukecs.supplier_invoice_number as supplier_invoice_number, ukecs.exception_code as exception_code, $69 as zone_name, $70, ukecs.sales_price, $71 AS sales_surcharges, $72, ukecs.sales_price AS sales_total, ukecs.supplier_cost_price, $73 AS supplier_surcharges, $74, ukecs.supplier_cost_price AS supplier_total, ukecs.sales_price - ukecs.supplier_cost_price AS profit, COALESCE(ntl_i_supplier.purchase_transaction_id, $75) AS pat_id, COALESCE(ntl_i_supplier.reference, $76) AS pa_transaction_reference, COALESCE(ntl_sa.sales_transaction_id, $77) AS sat_id, COALESCE(ntl_sa.reference, $78) AS sa_transaction_reference, $79 AS consignment_vat_duty_id, $80 AS returned_to_itd_date FROM uk_ecommerce_consignment_surcharges ukecs LEFT JOIN uk_ecommerce_supplier ukec_supplier ON ukec_supplier.id = ukecs.supplier_account_id LEFT JOIN uk_ecommerce_carrier ukec_carrier ON ukec_carrier.id = ukec_supplier.carrier_id LEFT JOIN uk_ecommerce_service ukec_service ON ukec_service.id = ukecs.surcharge_id LEFT JOIN sales_account sa ON sa.id = ukecs.sales_account_id LEFT JOIN company ON company.id = sa.company_id LEFT JOIN nominal_transaction_lines ntl_i_supplier ON ntl_i_supplier.id = ukecs.supplier_nominal_transaction_id LEFT JOIN nominal_transaction_lines ntl_sa ON ntl_sa.id = ukecs.sales_nominal_transaction_id WHERE /*ukecs.ts_text_search @@ to_tsquery(UP
11 min < 0.1%	504 ms	1,365 postgresitd
`SELECT note.id, note.created_date, COALESCE(note.created_by, $4), note, COALESCE(internal_type, $5) FROM note LEFT JOIN note_link ON note_link.note_id = note.id WHERE (note_link.old_table_id = $1 OR note_link.table_name = $3) AND note_link.record_id = $2 AND note_link.status = $6 ORDER BY note.created_date DESC LIMIT $7`
9 min < 0.1%	121 ms	4,390 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", "public"."connexx_shipment_packages"."randomized_reference", "public"."connexx_shipment_packages"."epl_label", "public"."connexx_shipment_packages"."used_default_length", "public"."connexx_shipment_packages"."used_default_width", "public"."connexx_shipment_packages"."used_default_height", "public"."connexx_shipment_packages"."used_default_weight" 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 /, ... /) AND "public"."connexx_shipment_packages"."shipment_id" IN ($2 /, ... /) AND "public"."connexx_shipment_packages"."sales_account_id" IN ($3) AND ("j1"."shipping_whitelist_id" IS NOT NULL AND ("j1"."id" IS NOT NULL))) OFFSET $4
7 min < 0.1%	59 ms	7,523 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
7 min < 0.1%	1,011 ms	420 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 AND (sa.alert_type = $151 OR sa.alert_type = $152 OR sa.alert_type is null OR sa.alert_type = $153) 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
6 min < 0.1%	20 ms	18,153 upadmin
UPDATE "public"."connexx_shipments" SET "status" = $1, "combined_label_url" = $2, "consignment_number" = $3, "base64_eltron" = $4, "base64_citizen" = $5, "epl_label" = $6, "carrier_label_errors" = $7, "carrier_shipment_id" = $8, "label_generated_at" = $9 WHERE ("public"."connexx_shipments"."id" = $10 AND $11=$12) RETURNING "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", "public"."connexx_shipments"."customer_order_source", "public"."connexx_shipments"."ignore_integration_update", "public"."connexx_shipments"."requires_saturday_delivery", "public"."connexx_shipments"."requires_sunday_delivery", "public"."connexx_shipments"."epl_label", "public"."connexx_shipments"."store_name", "public"."connexx_shipments"."shipment_date_from_ui", "public"."connexx_shipments"."label_generated_at", "public"."connexx_shipments"."api_imperial_units_used", "public"."connexx_shipments"."used_import_items_ai_feature"
5 min < 0.1%	1,231 ms	252 postgresitd
SELECT cs.id, cs.created_date, CASE WHEN w.domestic_international THEN (SELECT name FROM uk_ecommerce_carrier WHERE id = w.carrier_id) ELSE (SELECT name FROM intl_carrier WHERE id = w.carrier_id) END AS carrier, CASE WHEN w.domestic_international THEN (SELECT name from uk_ecommerce_service WHERE id = w.service_id) ELSE (SELECT name FROM intl_service WHERE id = w.service_id) END AS service, c.name AS customer, csp.tracking_code, COALESCE(cs.customer_reference, '') AS customer_reference, COALESCE(cs.customer_reference_2, '') AS customer_reference_2, COALESCE(COALESCE(cs.from_address_first_name, '') \|\| ' ' \|\| COALESCE(cs.from_address_last_name, ''), '') AS from_name, COALESCE(cs.to_address_first_name, '') AS to_address_first_name, COALESCE(cs.to_address_last_name, '') AS to_address_last_name, COALESCE(cs.to_address_company, '') AS to_address_company, COALESCE(cs.to_address_street_1, '') AS to_address_street_1, COALESCE(cs.to_address_street_2, '') AS to_address_street_2, COALESCE(cs.to_address_street_3, '') AS to_address_street_3, COALESCE(cs.to_address_city, '') AS to_address_city, COALESCE(cs.to_address_zip, '') AS to_address_zip, COALESCE(cs.to_address_country_iso, '') as to_address_country_iso, COALESCE(cs.to_address_email, '') AS email, COALESCE(cs.to_address_phone, '') AS phone, COALESCE(cs.from_address_street_1, '') AS from_address_street_1, COALESCE(cs.from_address_street_2, '') AS from_address_street_2, COALESCE(cs.from_address_street_3, '') AS from_address_street_3, COALESCE(cs.from_address_city, '') AS from_address_city, COALESCE(cs.from_address_zip, '') AS from_address_zip, COALESCE(cs.from_address_country_iso, '') as from_address_country_iso, COALESCE(ct.tracking_information, '[]') as tracking_information, COALESCE(latest_event.tracking_event, 'No tracking events received') AS tracking_event, COALESCE(latest_event.tracking_date, '01-01-0001') AS tracking_date, COALESCE(csp.weight / 100, 0) AS weight, COALESCE(csp.length / 100, 0) AS length, COALESCE(csp.width / 100, 0) AS width, COALESCE(csp.height / 100, 0) AS height, COALESCE(p.d_scan, '01-01-0001') AS d_scan, COALESCE(p.w_scan, '01-01-0001') AS w_scan, COALESCE(p.last_modified_by, '') AS last_modified_by, COALESCE(p.trailer_number, '') AS trailer_number, COALESCE(SUM(cdis.item_value * cdis.quantity), 0) AS total_item_value, COALESCE(SUM(cdis.quantity), 0) AS quantity, COALESCE(STRING_AGG(cdis.sku, ','), '') AS sku, COALESCE(cs.duty_or_tax_paid_by, '') AS duty_or_tax_paid_by, COALESCE(cs.label_generated_at, '0001-01-01 00:00:00'::timestamp) AS label_generation_date FROM connexx_shipment_packages csp LEFT JOIN connexx_shipments cs on cs.id = csp.shipment_id LEFT JOIN sales_account sa on csp.sales_account_id = sa.id LEFT JOIN company c on sa.company_id = c.id LEFT JOIN sales_account_shipping_whitelist w on w.id = cs.shipping_whitelist_id LEFT JOIN consignment_tracking ct ON ct.barcode = csp.tracking_code LEFT JOIN LATERAL ( SELECT e ->> 'trackingEvent' AS tracking_event, (e ->> 'statusTime')::timestamptz AS tracking_date FROM jsonb_array_elements(COALESCE(ct.tracking_information, '[]'::jsonb)) e ORDER BY (e ->> 'statusTime')::timestamptz DESC NULLS LAST LIMIT 1 ) latest_event ON true LEFT JOIN connexx_shipment_item_descriptions cdis ON cdis.shipment_id = cs.id LEFT JOIN scanning_app.parcels p ON p.barcode = csp.tracking_code LEFT JOIN connexx_shipment_bulk_uploads csbu ON csbu.id = cs.shipment_bulk_upload_id LEFT JOIN connexx_shipment_import_profiles cspi ON cspi.id = csbu.shipment_import_profile_id WHERE csp.sales_account_id = $1 AND csp.created_date BETWEEN $2 AND $3 AND csp.tracking_code IS NOT NULL AND cs.status = 'label_created' GROUP BY cs.id, cs.created_date, carrier, service, c.name, csp.tracking_code, cs.customer_reference, cs.customer_reference_2, from_name, cs.to_address_first_name, cs.to_address_last_name, cs.to_address_company, cs.to_address_street_1, cs.to_address_street_2, cs.to_address_street_3, cs.to_address_city, cs.to_address_zip, cs.to_address_country_iso, cs.to_address_email, cs.to_address_phone, cs.from_address_street_1, cs.from_address_street_2, cs.from_address_street_3, cs.from_address_city, cs.from_address_zip, cs.from_address_country_iso, ct.tracking_information, latest_event.tracking_event, latest_event.tracking_date, csp.weight, csp.length, csp.width, csp.height, p.d_scan, p.w_scan, p.last_modified_by, p.trailer_number, cs.label_generated_at ORDER BY 1, 2

Duplicate Indexes

Suggested Indexes

Slow Queries