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_id", column: :id
remove_index :currency, name: "idx_currency", column: :id
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 :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 :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 (id) is covered by currency_pk (id)
On currency idx_currency (id) is covered by currency_pk (id)
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 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 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 upload_transform_run_column_run_id_field_path_key (run_id, field_path)
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 upload_transform_run_row_run_id_row_number_key (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 (id)

is covered by

currency_pk (id)

On currency

idx_currency (id)

is covered by

currency_pk (id)

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

upload_transform_run_column_run_id_field_path_key (run_id, field_path)

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

upload_transform_run_row_run_id_row_number_key (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
3 min < 0.1%	2,290 ms	70 upadmin
SELECT "public"."connexx_shipment_bulk_uploads"."id", "public"."connexx_shipment_bulk_uploads"."uuid", "public"."connexx_shipment_bulk_uploads"."created_by", "public"."connexx_shipment_bulk_uploads"."created_date", "public"."connexx_shipment_bulk_uploads"."last_modified_by", "public"."connexx_shipment_bulk_uploads"."last_modified_date", "public"."connexx_shipment_bulk_uploads"."sales_account_id", "public"."connexx_shipment_bulk_uploads"."shipment_import_profile_id", "public"."connexx_shipment_bulk_uploads"."courier_api_bulk_label_job_id", "public"."connexx_shipment_bulk_uploads"."filename", "public"."connexx_shipment_bulk_uploads"."method", "public"."connexx_shipment_bulk_uploads"."api_key_id", "public"."connexx_shipment_bulk_uploads"."sales_account_integration_id", "public"."connexx_shipment_bulk_uploads"."csv_url" FROM "public"."connexx_shipment_bulk_uploads" WHERE ("public"."connexx_shipment_bulk_uploads"."sales_account_id" = $1 AND "public"."connexx_shipment_bulk_uploads"."filename" ILIKE $2) ORDER BY "public"."connexx_shipment_bulk_uploads"."created_date" DESC OFFSET $3

Total Time	Average Time	Calls
140 min 5%	1,839 ms	4,585 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
16 min 0.5%	264 ms	3,552 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

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
486 min 16%	364 ms	79,950 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"`
440 min 15%	478 ms	55,182 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
284 min 9%	397 ms	42,844 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
206 min 7%	159 ms	78,061 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: 15442166 Row progression: 15442166, 28282 Row estimates - sales_account_id (=): 28282 Existing indexes - id PRIMARY - deprecated_shipment_ids - sales_account_id, created_date - uuid UNIQUE`
140 min 5%	1,839 ms	4,585 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: 24877030 Row progression: 24877030, 46412, 45333 Row estimates - sales_account_id (=): 46412 - description (~~*): 2429946 - description (not_null): 24298224 - created_date (sort): 65 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
87 min 3%	257 ms	20,408 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" FROM "public"."connexx_shipments" LEFT JOIN "public"."portal_user" AS "j3" ON ("j3"."id") = ("public"."connexx_shipments"."created_by") LEFT JOIN "public"."connexx_shipment_bulk_uploads" AS "j4" ON ("j4"."id") = ("public"."connexx_shipments"."shipment_bulk_upload_id") LEFT JOIN "public"."sales_account_shipping_whitelist" AS "j5" ON ("j5"."id") = ("public"."connexx_shipments"."shipping_whitelist_id") WHERE ("public"."connexx_shipments"."sales_account_id" = $1 AND "public"."connexx_shipments"."shipment_type" = $2 AND ("public"."connexx_shipments"."customer_reference" ILIKE $3 OR "public"."connexx_shipments"."customer_reference_2" ILIKE $4 OR "public"."connexx_shipments"."order_number" ILIKE $5 OR "public"."connexx_shipments"."from_address_company" ILIKE $6 OR "public"."connexx_shipments"."from_address_first_name" ILIKE $7 OR "public"."connexx_shipments"."from_address_last_name" ILIKE $8 OR "public"."connexx_shipments"."from_address_street_1" ILIKE $9 OR "public"."connexx_shipments"."from_address_zip" ILIKE $10 OR "public"."connexx_shipments"."to_address_company" ILIKE $11 OR "public"."connexx_shipments"."to_address_first_name" ILIKE $12 OR "public"."connexx_shipments"."to_address_last_name" ILIKE $13 OR "public"."connexx_shipments"."to_address_street_1" ILIKE $14 OR "public"."connexx_shipments"."to_address_zip" ILIKE $15 OR "public"."connexx_shipments"."estimated_delivery_date" ILIKE $16 OR ("public"."connexx_shipments"."id") IN (SELECT "t1"."shipment_id" FROM "public"."connexx_shipment_packages" AS "t1" WHERE ("t1"."tracking_code" ILIKE $17 AND "t1"."shipment_id" IS NOT NULL)) OR ("public"."connexx_shipments"."id") IN (SELECT "t2"."shipment_id" FROM "public"."connexx_shipment_item_descriptions" AS "t2" WHERE (("t2"."sku" ILIKE $18 OR "t2"."description" ILIKE $19) AND "t2"."shipment_id" IS NOT NULL)) OR ("j3"."email" ILIKE $20 AND ("j3"."id" IS NOT NULL)) OR ("j4"."filename" ILIKE $21 AND ("j4"."id" IS NOT NULL)) OR (("j5"."carrier_name" ILIKE $22 OR "j5"."service_name" ILIKE $23) AND ("j5"."id" IS NOT NULL))) AND "public"."connexx_shipments"."created_date" >= $24 AND "public"."connexx_shipments"."created_date" <= $25 AND "public"."connexx_shipments"."deleted_date" IS NULL AND "public"."connexx_shipments"."status" IN ($26)) ORDER BY "public"."connexx_shipments"."created_date" DESC LIMIT $27 OFFSET $28
83 min 3%	77 ms	64,758 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`
55 min 2%	42 ms	78,061 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"`
47 min 2%	70 ms	39,928 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" 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
26 min 0.9%	624 ms	2,482 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
21 min 0.7%	1,072 ms	1,148 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
20 min 0.7%	126 ms	9,679 postgresitd
`SELECT EXISTS ( SELECT $3 FROM connexx_shipments WHERE customer_reference = $1 AND shipment_integration_id = $2) AS exists`
16 min 0.5%	668 ms	1,419 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" 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"."st
16 min 0.5%	264 ms	3,552 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: 24877030 Row progression: 24877030, 46412, 38839 Row estimates - sales_account_id (=): 46412 - sku (~~*): 2081908 - sku (not_null): 20817928 - created_date (sort): 65 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
11 min 0.4%	22 ms	30,046 upadmin
`SELECT * FROM get_whitelist_id(cast($1 as int), cast($2 as bigint), cast($3 as int), $4, $5, $6, $7, cast($8 as int)) WHERE actual_zone IS NOT NULL`
8 min 0.3%	204 ms	2,482 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
7 min 0.2%	384 ms	1,036 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: 81701168 Row progression: 81701168, 77205, 74 Row estimates - sales_account_reference (=): 77205 - sales_account_id (=): 78481 - status (=): 40849222 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
5 min 0.2%	117 ms	2,482 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)`
3 min 0.1%	1,811 ms	114 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
3 min 0.1%	41 ms	4,567 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" 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
3 min < 0.1%	34 ms	4,968 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
2 min < 0.1%	462 ms	296 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
2 min < 0.1%	735 ms	139 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
1 min < 0.1%	609 ms	141 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
1 min < 0.1%	55 ms	1,074 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"."to_address_first_name" ASC OFFSET $27

Duplicate Indexes

Suggested Indexes

Slow Queries