Column	Sequence
audit_log.id rate_engine	audit_log_id_seq rate_engine
barcode_registry.id logistics_v2	barcode_registry_id_seq logistics_v2
billing_data_files.id	billing_data_files_id_seq
billing_pdfs.id	billing_pdfs_id_seq
carrier.id rate_engine	carrier_id_seq rate_engine
carriers.id logistics_v2	carriers_id_seq logistics_v2
customer_shipments.id	customer_shipments_id_seq
customer_tariffs.id logistics_v2	customer_tariffs_id_seq logistics_v2
customers.id logistics_v2	customers_id_seq logistics_v2
depot.id rate_engine	depot_id_seq rate_engine
ecommerce_supplier_link.id	ecommerce_supplier_link_id_seq
ecommerce_tariff_link.id	ecommerce_tariff_link_id_seq
fedex_missng.id	fedex_missng_id_seq
file_formats.id logistics_v2	file_formats_id_seq logistics_v2
file_imports.id logistics_v2	file_imports_id_seq logistics_v2
ftp_files.id	ftp_files_seq
general_lookup.id	general_lookup_id_seq
gri_sales_account.id	gri_sa_seq
icst_2026_01_14t15_35_24z.id temp	icst_2026_01_14t15_35_24z_id_seq temp
icst_2026_01_14t17_40_55z.id temp	icst_2026_01_14t17_40_55z_id_seq temp
intl_consignments_2025_08_08t13_30_43_01_00_288_19673.id temp	intl_consignments_2025_08_08t13_30_43_01_00_288_19673_id_seq temp
leads.id scanning_app	leads_id_seq scanning_app
margin_details.id	margin_details_id_seq
override_rate.id rate_engine	override_rate_id_seq rate_engine
pricing_profile.id rate_engine	pricing_profile_id_seq rate_engine
processing_events.id logistics_v2	processing_events_id_seq logistics_v2
query_log.id ai_codebrain	query_log_id_seq ai_codebrain
rate_card.id rate_engine	rate_card_id_seq rate_engine
rate_card_line.id rate_engine	rate_card_line_id_seq rate_engine
rate_cards.id logistics_v2	rate_cards_id_seq logistics_v2
sales_account_pricing.id logistics_v2	sales_account_pricing_id_seq logistics_v2
service.id rate_engine	service_id_seq rate_engine
service_zones.id logistics_v2	service_zones_id_seq logistics_v2
services.id logistics_v2	services_id_seq logistics_v2
shipments.id logistics_v2	shipments_id_seq logistics_v2
suppliers.id logistics_v2	suppliers_id_seq logistics_v2
surcharge_rules.id	surcharge_rules_id_seq
surcharge_types.id	surcharge_types_id_seq
surcharges.id logistics_v2	surcharges_id_seq logistics_v2
tariffs.id logistics_v2	tariffs_id_seq logistics_v2
validation_rule.id rate_engine	validation_rule_id_seq rate_engine
yodel_pan_files.id	yodel_pan_files_id_seq
zone_postcodes.id logistics_v2	zone_postcodes_id_seq logistics_v2
zones.id logistics_v2	zones_id_seq logistics_v2

Long Running Queries

We recommend setting a statement timeout on all non-superusers with:

ALTER ROLE <user> SET statement_timeout TO '60s';

Pid	Duration	State
1757900	11:09:46	active
postgresitd WITH ci_imports AS (SELECT sales_account_id, service, manifest_date::date, to_char(sales_account_id, 'fm00000000') \|\| '-' \|\| carrier \|\| '-' \|\| service \|\| '-' \|\| to_char(manifest_date::date, 'YYYYMMDD') AS cis_key, count(*) AS consignment_count FROM consignment_imports WHERE source = 'connexx' group by 1, 2, carrier, 3) UPDATE consignment_imports_summary cis SET number_of_consignments = ci_imports.consignment_count FROM ci_imports WHERE ci_imports.cis_key = cis.cis_key;
1780708	09:55:22	active
postgresitd WITH ci_imports AS (SELECT sales_account_id, service, manifest_date::date, to_char(sales_account_id, 'fm00000000') \|\| '-' \|\| carrier \|\| '-' \|\| service \|\| '-' \|\| to_char(manifest_date::date, 'YYYYMMDD') AS cis_key, count(*) AS consignment_count FROM consignment_imports WHERE source = 'connexx' group by 1, 2, carrier, 3) UPDATE consignment_imports_summary cis SET number_of_consignments = ci_imports.consignment_count FROM ci_imports WHERE ci_imports.cis_key = cis.cis_key;
1780294	09:54:00	active
postgresitd WITH ci_imports AS (SELECT sales_account_id, service, manifest_date::date, to_char(sales_account_id, 'fm00000000') \|\| '-' \|\| carrier \|\| '-' \|\| service \|\| '-' \|\| to_char(manifest_date::date, 'YYYYMMDD') AS cis_key, count(*) AS consignment_count FROM consignment_imports WHERE source = 'connexx' group by 1, 2, carrier, 3) UPDATE consignment_imports_summary cis SET number_of_consignments = ci_imports.consignment_count FROM ci_imports WHERE ci_imports.cis_key = cis.cis_key;
1802588	08:59:52	active
postgresitd WITH ci_imports AS (SELECT sales_account_id, service, manifest_date::date, to_char(sales_account_id, 'fm00000000') \|\| '-' \|\| carrier \|\| '-' \|\| service \|\| '-' \|\| to_char(manifest_date::date, 'YYYYMMDD') AS cis_key, count(*) AS consignment_count FROM consignment_imports WHERE source = 'connexx' group by 1, 2, carrier, 3) UPDATE consignment_imports_summary cis SET number_of_consignments = ci_imports.consignment_count FROM ci_imports WHERE ci_imports.cis_key = cis.cis_key;
1802587	08:58:48	active
postgresitd WITH ci_imports AS (SELECT sales_account_id, service, manifest_date::date, to_char(sales_account_id, 'fm00000000') \|\| '-' \|\| carrier \|\| '-' \|\| service \|\| '-' \|\| to_char(manifest_date::date, 'YYYYMMDD') AS cis_key, count(*) AS consignment_count FROM consignment_imports WHERE source = 'connexx' group by 1, 2, carrier, 3) UPDATE consignment_imports_summary cis SET number_of_consignments = ci_imports.consignment_count FROM ci_imports WHERE ci_imports.cis_key = cis.cis_key;
1822195	08:08:46	active
postgresitd WITH ci_imports AS (SELECT sales_account_id, service, manifest_date::date, to_char(sales_account_id, 'fm00000000') \|\| '-' \|\| carrier \|\| '-' \|\| service \|\| '-' \|\| to_char(manifest_date::date, 'YYYYMMDD') AS cis_key, count(*) AS consignment_count FROM consignment_imports WHERE source = 'connexx' group by 1, 2, carrier, 3) UPDATE consignment_imports_summary cis SET number_of_consignments = ci_imports.consignment_count FROM ci_imports WHERE ci_imports.cis_key = cis.cis_key;
1824802	08:07:13	active
postgresitd WITH ci_imports AS (SELECT sales_account_id, service, manifest_date::date, to_char(sales_account_id, 'fm00000000') \|\| '-' \|\| carrier \|\| '-' \|\| service \|\| '-' \|\| to_char(manifest_date::date, 'YYYYMMDD') AS cis_key, count(*) AS consignment_count FROM consignment_imports WHERE source = 'connexx' group by 1, 2, carrier, 3) UPDATE consignment_imports_summary cis SET number_of_consignments = ci_imports.consignment_count FROM ci_imports WHERE ci_imports.cis_key = cis.cis_key;
1846964	06:23:25	active
postgresitd WITH ci_imports AS (SELECT sales_account_id, service, manifest_date::date, to_char(sales_account_id, 'fm00000000') \|\| '-' \|\| carrier \|\| '-' \|\| service \|\| '-' \|\| to_char(manifest_date::date, 'YYYYMMDD') AS cis_key, count(*) AS consignment_count FROM consignment_imports WHERE source = 'connexx' group by 1, 2, carrier, 3) UPDATE consignment_imports_summary cis SET number_of_consignments = ci_imports.consignment_count FROM ci_imports WHERE ci_imports.cis_key = cis.cis_key;
1846974	06:22:34	active
postgresitd WITH ci_imports AS (SELECT sales_account_id, service, manifest_date::date, to_char(sales_account_id, 'fm00000000') \|\| '-' \|\| carrier \|\| '-' \|\| service \|\| '-' \|\| to_char(manifest_date::date, 'YYYYMMDD') AS cis_key, count(*) AS consignment_count FROM consignment_imports WHERE source = 'connexx' group by 1, 2, carrier, 3) UPDATE consignment_imports_summary cis SET number_of_consignments = ci_imports.consignment_count FROM ci_imports WHERE ci_imports.cis_key = cis.cis_key;
1868906	06:07:28	active
postgresitd WITH ci_imports AS (SELECT sales_account_id, service, manifest_date::date, to_char(sales_account_id, 'fm00000000') \|\| '-' \|\| carrier \|\| '-' \|\| service \|\| '-' \|\| to_char(manifest_date::date, 'YYYYMMDD') AS cis_key, count(*) AS consignment_count FROM consignment_imports WHERE source = 'connexx' group by 1, 2, carrier, 3) UPDATE consignment_imports_summary cis SET number_of_consignments = ci_imports.consignment_count FROM ci_imports WHERE ci_imports.cis_key = cis.cis_key;
1868916	06:06:52	active
postgresitd WITH ci_imports AS (SELECT sales_account_id, service, manifest_date::date, to_char(sales_account_id, 'fm00000000') \|\| '-' \|\| carrier \|\| '-' \|\| service \|\| '-' \|\| to_char(manifest_date::date, 'YYYYMMDD') AS cis_key, count(*) AS consignment_count FROM consignment_imports WHERE source = 'connexx' group by 1, 2, carrier, 3) UPDATE consignment_imports_summary cis SET number_of_consignments = ci_imports.consignment_count FROM ci_imports WHERE ci_imports.cis_key = cis.cis_key;
1890773	05:08:11	active
postgresitd WITH ci_imports AS (SELECT sales_account_id, service, manifest_date::date, to_char(sales_account_id, 'fm00000000') \|\| '-' \|\| carrier \|\| '-' \|\| service \|\| '-' \|\| to_char(manifest_date::date, 'YYYYMMDD') AS cis_key, count(*) AS consignment_count FROM consignment_imports WHERE source = 'connexx' group by 1, 2, carrier, 3) UPDATE consignment_imports_summary cis SET number_of_consignments = ci_imports.consignment_count FROM ci_imports WHERE ci_imports.cis_key = cis.cis_key;
1890772	05:07:27	active
postgresitd WITH ci_imports AS (SELECT sales_account_id, service, manifest_date::date, to_char(sales_account_id, 'fm00000000') \|\| '-' \|\| carrier \|\| '-' \|\| service \|\| '-' \|\| to_char(manifest_date::date, 'YYYYMMDD') AS cis_key, count(*) AS consignment_count FROM consignment_imports WHERE source = 'connexx' group by 1, 2, carrier, 3) UPDATE consignment_imports_summary cis SET number_of_consignments = ci_imports.consignment_count FROM ci_imports WHERE ci_imports.cis_key = cis.cis_key;
1913748	04:09:24	active
postgresitd WITH ci_imports AS (SELECT sales_account_id, service, manifest_date::date, to_char(sales_account_id, 'fm00000000') \|\| '-' \|\| carrier \|\| '-' \|\| service \|\| '-' \|\| to_char(manifest_date::date, 'YYYYMMDD') AS cis_key, count(*) AS consignment_count FROM consignment_imports WHERE source = 'connexx' group by 1, 2, carrier, 3) UPDATE consignment_imports_summary cis SET number_of_consignments = ci_imports.consignment_count FROM ci_imports WHERE ci_imports.cis_key = cis.cis_key;
1912689	04:08:36	active
postgresitd WITH ci_imports AS (SELECT sales_account_id, service, manifest_date::date, to_char(sales_account_id, 'fm00000000') \|\| '-' \|\| carrier \|\| '-' \|\| service \|\| '-' \|\| to_char(manifest_date::date, 'YYYYMMDD') AS cis_key, count(*) AS consignment_count FROM consignment_imports WHERE source = 'connexx' group by 1, 2, carrier, 3) UPDATE consignment_imports_summary cis SET number_of_consignments = ci_imports.consignment_count FROM ci_imports WHERE ci_imports.cis_key = cis.cis_key;
1924125	03:12:58	active
postgresitd INSERT INTO consignment_imports (status, source, created_by, created_date, order_number, order_date, order_value, consignment_number, consignment_identifier, barcode_postcode_key, barcode, package_weight, currency, shipment_method, shipper_warehouse, recipient_first_name, recipient_last_name, recipient_phone, ship_to_company_name, ship_to_address_line_1, ship_to_address_line_2, ship_to_address_line_3, ship_to_city, ship_to_state, ship_to_postcode, ship_to_country, email, carrier, service, applied_shipment_rule, external_tracking_url, package_type, import_date, print_date, manifest_date, company_name, sales_account_id, shipper_reference_1, shipper_reference_2, parent, client, brand, origin_of_parcel, invoice_date, evri_year_week, client_year_period, sales_account_reference, destination_area, p_shop_delivery, next_day, sunday, signature, household_signature, pin, volume, carrier_invoice_description, surcharge_description, carrier_invoice_number, client_id, account_number, product_number, product_description, p
1956718	02:19:56	active
postgresitd INSERT INTO consignment_imports (status, source, created_by, created_date, order_number, order_date, order_value, consignment_number, consignment_identifier, barcode_postcode_key, barcode, package_weight, currency, shipment_method, shipper_warehouse, recipient_first_name, recipient_last_name, recipient_phone, ship_to_company_name, ship_to_address_line_1, ship_to_address_line_2, ship_to_address_line_3, ship_to_city, ship_to_state, ship_to_postcode, ship_to_country, email, carrier, service, applied_shipment_rule, external_tracking_url, package_type, import_date, print_date, manifest_date, company_name, sales_account_id, shipper_reference_1, shipper_reference_2, parent, client, brand, origin_of_parcel, invoice_date, evri_year_week, client_year_period, sales_account_reference, destination_area, p_shop_delivery, next_day, sunday, signature, household_signature, pin, volume, carrier_invoice_description, surcharge_description, carrier_invoice_number, client_id, account_number, product_number, product_description, p
1956719	02:19:56	active
postgresitd INSERT INTO consignment_imports (status, source, created_by, created_date, order_number, order_date, order_value, consignment_number, consignment_identifier, barcode_postcode_key, barcode, package_weight, currency, shipment_method, shipper_warehouse, recipient_first_name, recipient_last_name, recipient_phone, ship_to_company_name, ship_to_address_line_1, ship_to_address_line_2, ship_to_address_line_3, ship_to_city, ship_to_state, ship_to_postcode, ship_to_country, email, carrier, service, applied_shipment_rule, external_tracking_url, package_type, import_date, print_date, manifest_date, company_name, sales_account_id, shipper_reference_1, shipper_reference_2, parent, client, brand, origin_of_parcel, invoice_date, evri_year_week, client_year_period, sales_account_reference, destination_area, p_shop_delivery, next_day, sunday, signature, household_signature, pin, volume, carrier_invoice_description, surcharge_description, carrier_invoice_number, client_id, account_number, product_number, product_description, p
1956965	01:19:56	active
postgresitd INSERT INTO consignment_imports (status, source, created_by, created_date, order_number, order_date, order_value, consignment_number, consignment_identifier, barcode_postcode_key, barcode, package_weight, currency, shipment_method, shipper_warehouse, recipient_first_name, recipient_last_name, recipient_phone, ship_to_company_name, ship_to_address_line_1, ship_to_address_line_2, ship_to_address_line_3, ship_to_city, ship_to_state, ship_to_postcode, ship_to_country, email, carrier, service, applied_shipment_rule, external_tracking_url, package_type, import_date, print_date, manifest_date, company_name, sales_account_id, shipper_reference_1, shipper_reference_2, parent, client, brand, origin_of_parcel, invoice_date, evri_year_week, client_year_period, sales_account_reference, destination_area, p_shop_delivery, next_day, sunday, signature, household_signature, pin, volume, carrier_invoice_description, surcharge_description, carrier_invoice_number, client_id, account_number, product_number, product_description, p
1970791	01:19:56	active
postgresitd INSERT INTO consignment_imports (status, source, created_by, created_date, order_number, order_date, order_value, consignment_number, consignment_identifier, barcode_postcode_key, barcode, package_weight, currency, shipment_method, shipper_warehouse, recipient_first_name, recipient_last_name, recipient_phone, ship_to_company_name, ship_to_address_line_1, ship_to_address_line_2, ship_to_address_line_3, ship_to_city, ship_to_state, ship_to_postcode, ship_to_country, email, carrier, service, applied_shipment_rule, external_tracking_url, package_type, import_date, print_date, manifest_date, company_name, sales_account_id, shipper_reference_1, shipper_reference_2, parent, client, brand, origin_of_parcel, invoice_date, evri_year_week, client_year_period, sales_account_reference, destination_area, p_shop_delivery, next_day, sunday, signature, household_signature, pin, volume, carrier_invoice_description, surcharge_description, carrier_invoice_number, client_id, account_number, product_number, product_description, p
1981408	00:19:56	active
postgresitd INSERT INTO consignment_imports (status, source, created_by, created_date, order_number, order_date, order_value, consignment_number, consignment_identifier, barcode_postcode_key, barcode, package_weight, currency, shipment_method, shipper_warehouse, recipient_first_name, recipient_last_name, recipient_phone, ship_to_company_name, ship_to_address_line_1, ship_to_address_line_2, ship_to_address_line_3, ship_to_city, ship_to_state, ship_to_postcode, ship_to_country, email, carrier, service, applied_shipment_rule, external_tracking_url, package_type, import_date, print_date, manifest_date, company_name, sales_account_id, shipper_reference_1, shipper_reference_2, parent, client, brand, origin_of_parcel, invoice_date, evri_year_week, client_year_period, sales_account_reference, destination_area, p_shop_delivery, next_day, sunday, signature, household_signature, pin, volume, carrier_invoice_description, surcharge_description, carrier_invoice_number, client_id, account_number, product_number, product_description, p
1981409	00:19:56	active
postgresitd INSERT INTO consignment_imports (status, source, created_by, created_date, order_number, order_date, order_value, consignment_number, consignment_identifier, barcode_postcode_key, barcode, package_weight, currency, shipment_method, shipper_warehouse, recipient_first_name, recipient_last_name, recipient_phone, ship_to_company_name, ship_to_address_line_1, ship_to_address_line_2, ship_to_address_line_3, ship_to_city, ship_to_state, ship_to_postcode, ship_to_country, email, carrier, service, applied_shipment_rule, external_tracking_url, package_type, import_date, print_date, manifest_date, company_name, sales_account_id, shipper_reference_1, shipper_reference_2, parent, client, brand, origin_of_parcel, invoice_date, evri_year_week, client_year_period, sales_account_reference, destination_area, p_shop_delivery, next_day, sunday, signature, household_signature, pin, volume, carrier_invoice_description, surcharge_description, carrier_invoice_number, client_id, account_number, product_number, product_description, p

Details
On barcode_registry idx_barcode_registry_barcode (barcode) is covered by uq_barcode_registry_barcode_customer (barcode, customer_id, label_created_at)
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 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 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 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 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_pt_created_desc (sales_account_id, service_id, package_type_id, created_date DESC) INCLUDE (tariff_id)
On uk_ecommerce_consignments dom_con_supplier_invoice_id_idx (supplier_linked_invoice_id) is covered by idx_ukec_siid_client (supplier_linked_invoice_id, client)
On uk_ecommerce_supplier_invoice idx_ukesi_supplier_account_id (supplier_account_id) is covered by idx_ukesi_supplier_account_purchase_tx (supplier_account_id, purchase_transaction_id)
On uk_postcodes_april_2025 idx_postcodes_latlng_group (lat_group, lng_group) is covered by idx_uk_postcodes_grouping (lat_group, lng_group, postcode_prefix)

Total Time	Average Time	Calls
4 min < 0.1%	403 ms	552 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" 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

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
878 min 21%	557 ms	94,595 upadmin
SELECT "public"."connexx_shipments"."id", "public"."connexx_shipments"."uuid", "public"."connexx_shipments"."created_by", "public"."connexx_shipments"."created_date", "public"."connexx_shipments"."last_modified_by", "public"."connexx_shipments"."last_modified_date", "public"."connexx_shipments"."duty_or_tax_paid_by", "public"."connexx_shipments"."customer_reference", "public"."connexx_shipments"."shipment_bulk_upload_id", "public"."connexx_shipments"."applied_rule_id", "public"."connexx_shipments"."from_address_first_name", "public"."connexx_shipments"."from_address_last_name", "public"."connexx_shipments"."from_address_company", "public"."connexx_shipments"."from_address_email", "public"."connexx_shipments"."from_address_phone", "public"."connexx_shipments"."from_address_street_1", "public"."connexx_shipments"."from_address_street_2", "public"."connexx_shipments"."from_address_street_3", "public"."connexx_shipments"."from_address_city", "public"."connexx_shipments"."from_address_county_state", "public"."connexx_shipments"."from_address_country_iso", "public"."connexx_shipments"."from_address_zip", "public"."connexx_shipments"."to_address_first_name", "public"."connexx_shipments"."to_address_last_name", "public"."connexx_shipments"."to_address_company", "public"."connexx_shipments"."to_address_email", "public"."connexx_shipments"."to_address_phone", "public"."connexx_shipments"."to_address_street_1", "public"."connexx_shipments"."to_address_street_2", "public"."connexx_shipments"."to_address_street_3", "public"."connexx_shipments"."to_address_city", "public"."connexx_shipments"."to_address_county_state", "public"."connexx_shipments"."to_address_country_iso", "public"."connexx_shipments"."to_address_zip", "public"."connexx_shipments"."sales_account_id", "public"."connexx_shipments"."job_number", "public"."connexx_shipments"."generated_by_admin", "public"."connexx_shipments"."date_converted_to_shipment", "public"."connexx_shipments"."shipment_type", "public"."connexx_shipments"."deleted_date", "public"."connexx_shipments"."deleted_by", "public"."connexx_shipments"."status", "public"."connexx_shipments"."rates_sql_query_output", "public"."connexx_shipments"."rates_sql_query_input", "public"."connexx_shipments"."errors", "public"."connexx_shipments"."reason_for_export", "public"."connexx_shipments"."combined_label_url", "public"."connexx_shipments"."bulk_upload_row_id", "public"."connexx_shipments"."original_shipment_id", "public"."connexx_shipments"."_remove_selected_quote_id", "public"."connexx_shipments"."shipping_whitelist_id", "public"."connexx_shipments"."from_address_vat_number", "public"."connexx_shipments"."from_address_eori_number", "public"."connexx_shipments"."to_address_vat_number", "public"."connexx_shipments"."to_address_eori_number", "public"."connexx_shipments"."virtual_warehouse_id", "public"."connexx_shipments"."service_options", "public"."connexx_shipments"."consignment_number", "public"."connexx_shipments"."commercial_invoice_pdf_url", "public"."connexx_shipments"."external_customer_id", "public"."connexx_shipments"."carrier_depot_id", "public"."connexx_shipments"."base64_eltron", "public"."connexx_shipments"."base64_citizen", "public"."connexx_shipments"."label_type"::text, "public"."connexx_shipments"."carrier_shipment_id", "public"."connexx_shipments"."invoice_date", "public"."connexx_shipments"."invoice_number", "public"."connexx_shipments"."order_number", "public"."connexx_shipments"."carrier_label_errors", "public"."connexx_shipments"."commercial_invoice_base64", "public"."connexx_shipments"."should_generate_commercial_invoice", "public"."connexx_shipments"."use_insurance", "public"."connexx_shipments"."insurance_amount", "public"."connexx_shipments"."insurance_amount_currency_code", "public"."connexx_shipments"."fedex_pickup_request_form_base64", "public"."connexx_shipments"."ddp", "public"."connexx_shipments"."should_pick_up", "public"."connexx_shipments"."pick_up_from_date_local_time", "public"."connexx_shipments"."pick_up_to_date_local_time", "public"."connexx_shipments"."pick_up_instructions", "public"."connexx_shipments"."carrier_api_request_json", "public"."connexx_shipments"."carrier_api_request", "public"."connexx_shipments"."original_shipping_whitelist_id", "public"."connexx_shipments"."uploaded_commercial_invoice_base64", "public"."connexx_shipments"."ioss_number", "public"."connexx_shipments"."future_label_date", "public"."connexx_shipments"."uploaded_proforma_invoice_base64", "public"."connexx_shipments"."uploaded_customs_declaration_base64", "public"."connexx_shipments"."uploaded_certificate_of_origin_base64", "public"."connexx_shipments"."uploaded_packing_listadr_base64", "public"."connexx_shipments"."uploaded_bank_documents_base64", "public"."connexx_shipments"."uploaded_dg_goods_declaration_base64", "public"."connexx_shipments"."uploaded_other_documents_base64", "public"."connexx_shipments"."uploaded_pickup_order_base64", "public"."connexx_shipments"."uploaded_export_declaration_base64", "public"."connexx_shipments"."uploaded_export_attachment_base64", "public"."connexx_shipments"."display_address_line_3", "public"."connexx_shipments"."discount_amount_multiplied", "public"."connexx_shipments"."shipment_source_name", "public"."connexx_shipments"."shipment_integration_id", "public"."connexx_shipments"."api_id", "public"."connexx_shipments"."ups_pickup_prn_number", "public"."connexx_shipments"."created_date_date", "public"."connexx_shipments"."dangerous_goods", "public"."connexx_shipments"."estimated_delivery_date", "public"."connexx_shipments"."packing_slip_base64", "public"."connexx_shipments"."should_split_duty_and_vat", "public"."connexx_shipments"."fedex_pickup_prn_number", "public"."connexx_shipments"."shipment_info", "public"."connexx_shipments"."integration_store_id", "public"."connexx_shipments"."store_integration_id", "public"."connexx_shipments"."dropoff_info", "public"."connexx_shipments"."ups_pickup_request_body", "public"."connexx_shipments"."fedex_pickup_location", "public"."connexx_shipments"."historic_prns", "public"."connexx_shipments"."cn22_base64", "public"."connexx_shipments"."customer_reference_2", "public"."connexx_shipments"."custom_shipment_data", "public"."connexx_shipments"."ukims_number", "public"."connexx_shipments"."delivery_instructions", "public"."connexx_shipments"."integration_created_date", "public"."connexx_shipments"."importer_address_first_name", "public"."connexx_shipments"."importer_address_last_name", "public"."connexx_shipments"."importer_address_company", "public"."connexx_shipments"."importer_address_email", "public"."connexx_shipments"."importer_address_phone", "public"."connexx_shipments"."importer_address_street_1", "public"."connexx_shipments"."importer_address_street_2", "public"."connexx_shipments"."importer_address_street_3", "public"."connexx_shipments"."importer_address_city", "public"."connexx_shipments"."importer_address_county_state", "public"."connexx_shipments"."importer_address_country_iso", "public"."connexx_shipments"."importer_address_zip", "public"."connexx_shipments"."importer_address_eori_number", "public"."connexx_shipments"."importer_address_vat_number", "public"."connexx_shipments"."should_use_importer_of_record", "public"."connexx_shipments"."requires_signature", "public"."connexx_shipments"."requires_adult_signature", "public"."connexx_shipments"."requires_direct_delivery", "public"."connexx_shipments"."deliver_to_residential", "public"."connexx_shipments"."notification_emails" FROM "public"."connexx_shipments" LEFT JOIN "public"."portal_user" AS "j3" ON ("j3"."id") = ("public"."connexx_shipments"."created_by") LEFT JOIN "public"."connexx_shipment_bulk_uploads" AS "j4" ON ("j4"."id") = ("public"."connexx_shipments"."shipment_bulk_upload_id") LEFT JOIN "public"."sales_account_shipping_whitelist" AS "j5" ON ("j5"."id") = ("public"."connexx_shipments"."shipping_whitelist_id") WHERE ("public"."connexx_shipments"."sales_account_id" = $1 AND "public"."connexx_shipments"."shipment_type" = $2 AND ("public"."connexx_shipments"."customer_reference" ILIKE $3 OR "public"."connexx_shipments"."customer_reference_2" ILIKE $4 OR "public"."connexx_shipments"."order_number" ILIKE $5 OR "public"."connexx_shipments"."from_address_company" ILIKE $6 OR "public"."connexx_shipments"."from_address_first_name" ILIKE $7 OR "public"."connexx_shipments"."from_address_last_name" ILIKE $8 OR "public"."connexx_shipments"."from_address_street_1" ILIKE $9 OR "public"."connexx_shipments"."from_address_zip" ILIKE $10 OR "public"."connexx_shipments"."to_address_company" ILIKE $11 OR "public"."connexx_shipments"."to_address_first_name" ILIKE $12 OR "public"."connexx_shipments"."to_address_last_name" ILIKE $13 OR "public"."connexx_shipments"."to_address_street_1" ILIKE $14 OR "public"."connexx_shipments"."to_address_zip" ILIKE $15 OR "public"."connexx_shipments"."estimated_delivery_date" ILIKE $16 OR ("public"."connexx_shipments"."id") IN (SELECT "t1"."shipment_id" FROM "public"."connexx_shipment_packages" AS "t1" WHERE ("t1"."tracking_code" ILIKE $17 AND "t1"."shipment_id" IS NOT NULL)) OR ("public"."connexx_shipments"."id") IN (SELECT "t2"."shipment_id" FROM "public"."connexx_shipment_item_descriptions" AS "t2" WHERE (("t2"."sku" ILIKE $18 OR "t2"."description" ILIKE $19) AND "t2"."shipment_id" IS NOT NULL)) OR ("j3"."email" ILIKE $20 AND ("j3"."id" IS NOT NULL)) OR ("j4"."filename" ILIKE $21 AND ("j4"."id" IS NOT NULL)) OR (("j5"."carrier_name" ILIKE $22 OR "j5"."service_name" ILIKE $23) AND ("j5"."id" IS NOT NULL))) AND "public"."connexx_shipments"."created_date" >= $24 AND "public"."connexx_shipments"."created_date" <= $25 AND "public"."connexx_shipments"."deleted_date" IS NULL AND "public"."connexx_shipments"."status" IN ($26)) ORDER BY "public"."connexx_shipments"."created_date" DESC LIMIT $27 OFFSET $28
730 min 18%	435 ms	100,762 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
459 min 11%	291 ms	94,596 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
239 min 6%	86 ms	166,314 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: 12958780 Row progression: 12958780, 25509 Row estimates - sales_account_id (=): 25509 Existing indexes - id PRIMARY - deprecated_shipment_ids - sales_account_id, created_date - uuid UNIQUE`
210 min 5%	76 ms	166,314 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"`
183 min 4%	70 ms	157,384 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`
87 min 2%	65 ms	80,578 upadmin
SELECT "public"."connexx_shipment_packages"."id", "public"."connexx_shipment_packages"."created_by", "public"."connexx_shipment_packages"."created_date", "public"."connexx_shipment_packages"."last_modified_by", "public"."connexx_shipment_packages"."last_modified_date", "public"."connexx_shipment_packages"."shipment_id", "public"."connexx_shipment_packages"."weight", "public"."connexx_shipment_packages"."length", "public"."connexx_shipment_packages"."height", "public"."connexx_shipment_packages"."type", "public"."connexx_shipment_packages"."quantity", "public"."connexx_shipment_packages"."width", "public"."connexx_shipment_packages"."tracking_code", "public"."connexx_shipment_packages"."label_created_date", "public"."connexx_shipment_packages"."label_url", "public"."connexx_shipment_packages"."label_format", "public"."connexx_shipment_packages"."errors", "public"."connexx_shipment_packages"."index", "public"."connexx_shipment_packages"."sales_account_id", "public"."connexx_shipment_packages"."base64_pdf", "public"."connexx_shipment_packages"."shipping_price", "public"."connexx_shipment_packages"."base64_zpl", "public"."connexx_shipment_packages"."original_ui_index", "public"."connexx_shipment_packages"."created_date_date", "public"."connexx_shipment_packages"."year", "public"."connexx_shipment_packages"."month", "public"."connexx_shipment_packages"."class", "public"."connexx_shipment_packages"."dg_net_weight", "public"."connexx_shipment_packages"."dgun_number", "public"."connexx_shipment_packages"."dry_ice_weight", "public"."connexx_shipment_packages"."lithium_category", "public"."connexx_shipment_packages"."packing_group", "public"."connexx_shipment_packages"."dg_package", "public"."connexx_shipment_packages"."carrier_shipment_package_id", "public"."connexx_shipment_packages"."dg_volume_ml_100" FROM "public"."connexx_shipment_packages" LEFT JOIN "public"."connexx_shipments" AS "j1" ON ("j1"."id") = ("public"."connexx_shipment_packages"."shipment_id") WHERE ("public"."connexx_shipment_packages"."tracking_code" IN ($1 /, ... /) 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
51 min 1%	574 ms	5,327 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
33 min 0.8%	2,498 ms	782 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
27 min 0.6%	2,599 ms	614 postgresitd
SELECT $4, ukesi.id, ukesi.status, ukesi.created_date, ukea.name, ukes.name, ukesi.supplier_invoice_number, ukesi.description, $5 as invoice_type, COALESCE((SUM(COALESCE(ukec.supplier_cost_price, $6)) FILTER (WHERE ukec.exception_code != $7)), $8) + COALESCE((SELECT SUM(COALESCE(ukecs.supplier_cost_price, $9)) FROM uk_ecommerce_consignment_surcharges ukecs WHERE ukecs.supplier_linked_invoice_id = ukesi.id AND ukecs.exception_code != $10), $11) AS supplier_freight_exceptions, $12, COALESCE((SUM(COALESCE(ukec.sales_price, $13)) FILTER (WHERE ukec.exception_code <> $14)), $15) + COALESCE((SUM(COALESCE(ukec.surcharge_sales_price, $16)) FILTER (WHERE ukec.exception_code <> $17)), $18) + COALESCE((SELECT SUM(COALESCE(ukecs.sales_price, $19)) FROM uk_ecommerce_consignment_surcharges ukecs WHERE ukecs.supplier_linked_invoice_id = ukesi.id AND ukecs.exception_code != $20), $21) + COALESCE((SELECT SUM(t1.sales_fuel) ::bigint FROM (SELECT (SUM(COALESCE(sales_price, $22))::bigint + SUM(COALESCE(surcharge_sales_price, $23))::bigint) * COALESCE(sales_fuel_surcharge_percent, $24) / $25::bigint AS sales_fuel FROM uk_ecommerce_consignments WHERE supplier_linked_invoice_id = ukec.supplier_linked_invoice_id AND uk_ecommerce_consignments.exception_code != $26 GROUP BY sales_fuel_surcharge_percent ORDER BY sales_fuel_surcharge_percent) t1), $27) AS sales_freight_exceptions, COALESCE((SUM(COALESCE(ukec.supplier_cost_price, $28)) FILTER (WHERE ukec.exception_code = $29)), $30) + COALESCE((SELECT SUM(COALESCE(ukecs.supplier_cost_price, $31)) FROM uk_ecommerce_consignment_surcharges ukecs WHERE ukecs.supplier_linked_invoice_id = ukesi.id AND ukecs.exception_code = $32), $33) AS supplier_freight, $34, COALESCE((SUM(COALESCE(ukec.sales_price, $35)) FILTER (WHERE ukec.exception_code = $36)), $37) + COALESCE((SUM(COALESCE(ukec.surcharge_sales_price, $38)) FILTER (WHERE ukec.exception_code = $39)), $40) + COALESCE((SELECT SUM(COALESCE(ukecs.sales_price, $41)) FROM uk_ecommerce_consignment_surcharges ukecs WHERE ukecs.supplier_linked_invoice_id = ukesi.id AND ukecs.exception_code = $42), $43) + COALESCE((SELECT SUM(t1.sales_fuel) ::bigint FROM (SELECT (SUM(COALESCE(sales_price, $44))::bigint + SUM(COALESCE(surcharge_sales_price, $45))::bigint) * COALESCE(sales_fuel_surcharge_percent, $46) / $47::bigint AS sales_fuel FROM uk_ecommerce_consignments WHERE supplier_linked_invoice_id = ukec.supplier_linked_invoice_id AND uk_ecommerce_consignments.exception_code = $48 GROUP BY sales_fuel_surcharge_percent ORDER BY sales_fuel_surcharge_percent) t1), $49) AS sales_freight, COALESCE(ukesi.purchase_transaction_id, 0) AS purchase_transaction_id, COALESCE(ukes.purchase_account_id, $50) AS purchase_account_id, COALESCE(CONCAT(pa.purchase_account_reference, $51, RTRIM(company.name)), $52) AS purchase_account_name, COALESCE(confirmed_for_invoicing, $53) AS confirmed_for_invoicing FROM uk_ecommerce_supplier_invoice ukesi LEFT JOIN uk_ecommerce_supplier ukes ON ukes.id = ukesi.supplier_account_id LEFT JOIN uk_ecommerce_carrier ukea ON ukea.id = ukes.carrier_id LEFT JOIN uk_ecommerce_consignments ukec ON ukec.supplier_linked_invoice_id = ukesi.id LEFT JOIN purchase_account pa ON pa.id = ukes.purchase_account_id LEFT JOIN company ON company.id = pa.company_id WHERE ukesi.created_date::date BETWEEN $1 AND $2 AND ukesi.status <> $54 AND ukesi.financial_company = $3 GROUP BY 4, 5, 6, 7, 8, 2, 16, ukec.supplier_linked_invoice_id, ukesi.status, ukes.purchase_account_id, pa.purchase_account_reference, company.name, confirmed_for_invoicing ORDER BY 4 DESC, 2 DESC
21 min 0.5%	67 ms	19,230 postgresitd
`SELECT EXISTS ( SELECT $3 FROM connexx_shipments WHERE customer_reference = $1 AND shipment_integration_id = $2) AS exists`
11 min 0.3%	124 ms	5,327 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
11 min 0.3%	122 ms	5,327 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)`
11 min 0.3%	21 ms	30,445 upadmin
`SELECT COUNT(*), "public"."connexx_shipments"."status" FROM "public"."connexx_shipments" WHERE ("public"."connexx_shipments"."sales_account_id" = $1 AND "public"."connexx_shipments"."shipment_type" = $2 AND "public"."connexx_shipments"."integration_store_id" = $3 AND "public"."connexx_shipments"."created_date" >= $4 AND "public"."connexx_shipments"."created_date" <= $5) GROUP BY "public"."connexx_shipments"."status" OFFSET $6` Covered by index on (integration_store_id, sales_account_id) Rows: 16915082 Row progression: 16915082, 7041, 13 Row estimates - integration_store_id (=): 7041 - sales_account_id (=): 30098 - created_date (<=): 1691509 - created_date (>=): 1691509 - shipment_type (=): 8457541 Existing indexes - id PRIMARY - api_id, sales_account_id - carrier_shipment_id - created_by - created_date_date - customer_reference gin_trgm_ops GIN - customer_reference_2 gin_trgm_ops GIN - from_address_company gin_trgm_ops GIN - id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]) - id WHERE upper((status)::text) <> ALL (ARRAY['CANCELLED'::text, 'ERROR'::text]) - integration_store_id, sales_account_id - job_number - lower((customer_reference)::text) - lower((estimated_delivery_date)::text) - lower((from_address_first_name)::text) - lower((from_address_last_name)::text) - lower((from_address_street_1)::text) - lower((from_address_zip)::text) - lower((order_number)::text), shipment_source_name, sales_account_id, status - lower((to_address_company)::text) - lower((to_address_first_name)::text) - lower((to_address_last_name)::text) - lower((to_address_street_1)::text) - lower((to_address_zip)::text) - order_number - order_number gin_trgm_ops GIN - sales_account_id, created_date - sales_account_id, created_date, shipment_type, status - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'cancelled'::text) - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'error'::text) - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'label_created'::text) - sales_account_id, created_date WHERE ((shipment_type)::text = 'SHIPMENT'::text) AND ((status)::text = 'ready_to_print'::text) - sales_account_id, customer_reference - shipment_bulk_upload_id - shipment_integration_id, lower((customer_reference)::text) - shipment_type - status, carrier_api_request_json - to_address_company gin_trgm_ops GIN - ups_pickup_prn_number - uuid UNIQUE
10 min 0.2%	40 ms	15,300 upadmin
SELECT "public"."connexx_shipment_packages"."id", "public"."connexx_shipment_packages"."created_by", "public"."connexx_shipment_packages"."created_date", "public"."connexx_shipment_packages"."last_modified_by", "public"."connexx_shipment_packages"."last_modified_date", "public"."connexx_shipment_packages"."shipment_id", "public"."connexx_shipment_packages"."weight", "public"."connexx_shipment_packages"."length", "public"."connexx_shipment_packages"."height", "public"."connexx_shipment_packages"."type", "public"."connexx_shipment_packages"."quantity", "public"."connexx_shipment_packages"."width", "public"."connexx_shipment_packages"."tracking_code", "public"."connexx_shipment_packages"."label_created_date", "public"."connexx_shipment_packages"."label_url", "public"."connexx_shipment_packages"."label_format", "public"."connexx_shipment_packages"."errors", "public"."connexx_shipment_packages"."index", "public"."connexx_shipment_packages"."sales_account_id", "public"."connexx_shipment_packages"."base64_pdf", "public"."connexx_shipment_packages"."shipping_price", "public"."connexx_shipment_packages"."base64_zpl", "public"."connexx_shipment_packages"."original_ui_index", "public"."connexx_shipment_packages"."created_date_date", "public"."connexx_shipment_packages"."year", "public"."connexx_shipment_packages"."month", "public"."connexx_shipment_packages"."class", "public"."connexx_shipment_packages"."dg_net_weight", "public"."connexx_shipment_packages"."dgun_number", "public"."connexx_shipment_packages"."dry_ice_weight", "public"."connexx_shipment_packages"."lithium_category", "public"."connexx_shipment_packages"."packing_group", "public"."connexx_shipment_packages"."dg_package", "public"."connexx_shipment_packages"."carrier_shipment_package_id", "public"."connexx_shipment_packages"."dg_volume_ml_100" FROM "public"."connexx_shipment_packages" LEFT JOIN "public"."connexx_shipments" AS "j1" ON ("j1"."id") = ("public"."connexx_shipment_packages"."shipment_id") WHERE ("public"."connexx_shipment_packages"."tracking_code" IN ($1 /, ... /) AND "public"."connexx_shipment_packages"."shipment_id" IN ($2 /, ... /) AND "public"."connexx_shipment_packages"."sales_account_id" IN ($3) AND ("j1"."shipping_whitelist_id" IS NOT NULL AND ("j1"."id" IS NOT NULL))) OFFSET $4
7 min 0.2%	587 ms	745 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
6 min 0.2%	181 ms	2,081 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: 72646008 Row progression: 72646008, 69798, 68 Row estimates - sales_account_reference (=): 69798 - sales_account_id (=): 70660 - status (=): 36319372 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.1%	861 ms	357 upadmin
`SELECT MAX("effective_date") FROM (SELECT "public"."exchange_rates"."effective_date" FROM "public"."exchange_rates" WHERE 1=1 OFFSET $1) AS "sub"`
5 min 0.1%	28 ms	10,657 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
4 min < 0.1%	160 ms	1,526 postgresitd
WITH consignments as (SELECT isi.id AS isi_id, isi.status AS status, isi.created_date AS created_date, icarrier.name AS carrier_name, isupplier.name AS supplier_name, isi.supplier_invoice_number AS supplier_invoice_number, COALESCE(description, $5) AS description, isi.invoice_type as invoice_type, COALESCE(SUM(COALESCE(icst.supplier_freight_amount, $6) + COALESCE(icst.government_duty, $7) + COALESCE(icst.government_vat, $8)) FILTER (WHERE icst.sales_exception_code != $9), $10) + COALESCE(SUM(COALESCE(icst.supplier_fuel_surcharge_amount, $11)) FILTER (WHERE icst.sales_exception_code != $12), $13) AS supplier_cost_price_exceptions, $14 AS supplier_vat_exceptions, COALESCE(SUM(COALESCE(icst.sales_freight_amount, $15) + COALESCE(icst.government_duty, $16) + COALESCE(icst.government_vat, $17)) FILTER (WHERE icst.sales_exception_code != $18), $19) + COALESCE(SUM(COALESCE(icst.sales_fuel_surcharge_amount, $20)) FILTER (WHERE icst.sales_exception_code != $21), $22) AS sales_cost_price_exceptions, COALESCE(SUM(COALESCE(icst.supplier_freight_amount, $23) + COALESCE(icst.government_duty, $24) + COALESCE(icst.government_vat, $25)) FILTER (WHERE icst.sales_exception_code = $26), $27) + COALESCE(SUM(COALESCE(icst.supplier_fuel_surcharge_amount, $28)) FILTER (WHERE icst.sales_exception_code = $29), $30) AS supplier_cost_price, COALESCE(SUM(COALESCE(icst.sales_freight_amount, $31) + COALESCE(icst.government_duty, $32) + COALESCE(icst.government_vat, $33)) FILTER (WHERE icst.sales_exception_code = $34), $35) + COALESCE(SUM(COALESCE(icst.sales_fuel_surcharge_amount, $36)) FILTER (WHERE icst.sales_exception_code = $37), $38) AS sales_cost_price, $39 AS supplier_vat, COALESCE(isi.purchase_transaction_id, $40) AS nominal_transaction_id, COALESCE(pa.id, $41) AS pa_id, COALESCE(CONCAT(pa.purchase_account_reference, $42, RTRIM(company.name)), $43) AS purchase_account_name, COALESCE(confirmed_for_invoicing, $44) AS confirmed_for_invoicing FROM intl_supplier_invoice isi LEFT JOIN intl_supplier isupplier ON isupplier.id = isi.supplier_account_id LEFT JOIN intl_carrier icarrier ON icarrier.id = isupplier.carrier_id LEFT JOIN intl_consignments_surcharges_transactions icst ON icst.supplier_linked_invoice_id = isi.id LEFT JOIN purchase_account pa ON pa.id = isupplier.purchase_account_id LEFT JOIN company ON company.id = pa.company_id WHERE isi.status <> $45 AND isi.created_date :: date BETWEEN $2 AND $3 AND isi.invoice_type = $1 AND isi.financial_company = $4 AND icst.consignment_id <> $46 GROUP BY icarrier.name, isi.status, isupplier.name, isi.supplier_invoice_number, isi.id, isi.description, isi.invoice_type, isi.created_date, isi.purchase_transaction_id, pa.id, pa.purchase_account_reference, company.name, confirmed_for_invoicing, icst.government_duty, icst.government_vat UNION ALL SELECT isi.id AS isi_id, isi.status AS status, isi.created_date AS created_date, icarrier.name AS carrier_name, isupplier.name AS supplier_name, isi.supplier_invoice_number AS supplier_invoice_number, COALESCE(description, $47) AS description, isi.invoice_type as invoice_type, COALESCE(SUM(COALESCE(icst.supplier_freight_amount, $48) + COALESCE(icst.government_duty, $49) + COALESCE(icst.government_vat, $50)) FILTER (WHERE icst.sales_exception_code != $51), $52) + COALESCE(SUM(COALESCE(icst.supplier_fuel_surcharge_amount, $53)) FILTER (WHERE icst.sales_exception_code != $54), $55) AS supplier_cost_price_exceptions, $56 AS supplier_vat_exceptions, COALESCE(SUM(COALESCE(icst.sales_freight_amount, $57) + COALESCE(icst.government_duty, $58) + COALESCE(icst.government_vat, $59)) FILTER (WHERE icst.sales_exception_code != $60), $61) + COALESCE(SUM(COALESCE(icst.sales_fuel_surcharge_amount, $62)) FILTER (WHERE icst.sales_exception_code != $63), $64) AS sales_cost_price_exceptions, COALESCE(SUM(COALESCE(icst.supplier_freight_amount, $65) + COALESCE(icst.government_duty, $66) + COALESCE(icst.government_vat, $67)) FILTER (WHERE icst.sales_exception_code = $68), $69) + COALESCE(SUM(COALESCE(icst.supplier_fuel_surcharge_amount, $70)) FILTER (WHERE icst.sales_exception_code = $71), $72) AS supplier_cost_price, COALESCE(SUM(COALESCE(icst.sales_freight_amount, $73) + COALESCE(icst.government_duty, $74) + COALESCE(icst.government_vat, $75)) FILTER (WHERE icst.sales_exception_code = $76), $77) + COALESCE(SUM(COALESCE(icst.sales_fuel_surcharge_amount, $78)) FILTER (WHERE icst.sales_exception_code = $79), $80) AS sales_cost_price, $81 AS supplier_vat, COALESCE(isi.purchase_transaction_id, $82) AS nominal_transaction_id, COALESCE(pa.id, $83) AS pa_id, COALESCE(CONCAT(pa.purchase_account_reference, $84, RTRIM(company.name)), $85) AS purchase_account_name, COALESCE(confirmed_for_invoicing, $86) AS confirmed_for_invoicing FROM intl_supplier_invoice isi LEFT JOIN intl_supplier isupplier ON isupplier.id = isi.supplier_account_id LEFT JOIN intl_carrier icarrier ON icarrier.id = isupplier.carrier_id LEFT JOIN intl_consignments_surcharges_transactions icst ON icst.supplier_linked_invoice_id = isi.id LEFT JOIN purchase_account pa ON pa.id = isupplier.purchase_account_id LEFT JOIN company ON company.id = pa.company_id WHERE isi.status <> $87 AND isi.created_date ::date BETWEEN $2 AND $3 AND isi.invoice_type = $1 AND icst.financial_company = $4 AND isi.financial_company = $4 AND icst.consignment_id = $88 GROUP BY icarrier.name, isi.status, isupplier.name, isi.supplier_invoice_number, isi.id, isi.description, isi.invoice_type, isi.created_date, isi.purchase_transaction_id, pa.id, pa.purchase_account_reference, company.name, confirmed_for_invoicing) SELECT $89, isi_id, status, created_date, carrier_name, supplier_name, supplier_invoice_number, description, invoice_type, SUM(supplier_cost_price_exceptions), SUM(supplier_vat_exceptions) AS vat_exceptions, SUM(sales_cost_price_exceptions), SUM(supplier_cost_price), SUM(supplier_vat) AS vat, SUM(sales_cost_price), nominal_transaction_id, pa_id, purchase_account_name, confirmed_for_invoicing FROM consignments GROUP BY isi_id, status, supplier_invoice_number, pa_id, purchase_account_name, description, invoice_type, nominal_transaction_id, created_date, carrier_name, supplier_name, confirmed_for_invoicing ORDER BY created_date DESC, carrier_name DESC, supplier_name, supplier_invoice_number, description
4 min < 0.1%	403 ms	552 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" 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 Details `CREATE INDEX CONCURRENTLY ON connexx_shipment_bulk_uploads (sales_account_id, created_date)` `Rows: 12843840 Row progression: 12843840, 30009, 0 Row estimates - sales_account_id (=): 30009 - filename (~~*): 1296734 - created_date (sort): 1 Existing indexes - id PRIMARY - sales_account_id - uuid UNIQUE`
3 min < 0.1%	472 ms	407 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
3 min < 0.1%	113 ms	1,460 postgresitd
`SELECT note.id, note.created_date, COALESCE(note.created_by, $4), note, COALESCE(internal_type, $5) FROM note LEFT JOIN note_link ON note_link.note_id = note.id WHERE (note_link.old_table_id = $1 OR note_link.table_name = $3) AND note_link.record_id = $2 AND note_link.status = $6 ORDER BY note.created_date DESC LIMIT $7`
2 min < 0.1%	497 ms	219 postgresitd
WITH lat AS (SELECT lat.sales_account_id, COALESCE(sum(lat.outstanding), $2) AS outstanding, COALESCE(sum(lat.outstanding) FILTER (WHERE lat.date_due BETWEEN CURRENT_DATE - interval $3 AND CURRENT_DATE - interval $4), $5) AS period_1, COALESCE(sum(lat.outstanding) FILTER (WHERE lat.date_due BETWEEN CURRENT_DATE - interval $6 AND CURRENT_DATE - interval $7), $8) AS period_2, COALESCE(sum(lat.outstanding) FILTER (WHERE lat.date_due BETWEEN CURRENT_DATE - interval $9 AND CURRENT_DATE - interval $10), $11) AS period_3, COALESCE(sum(lat.outstanding) FILTER (WHERE lat.date_due BETWEEN CURRENT_DATE - interval $12 AND CURRENT_DATE - interval $13), $14) AS period_4, COALESCE(sum(lat.outstanding) FILTER (WHERE lat.date_due BETWEEN CURRENT_DATE - interval $15 AND CURRENT_DATE - interval $16), $17) AS period_5, COALESCE(sum(lat.outstanding) FILTER (WHERE lat.date_due BETWEEN CURRENT_DATE - interval $18 AND CURRENT_DATE - interval $19), $20) AS period_6, lat.transaction_category, make_date($21, $22, $23) as transaction_date, $24 as transaction_reference FROM sales_account_transactions lat WHERE lat.status = $25 AND lat.reversal_user = $26 AND lat.reversed_by = $27 AND lat.original_id = $28 AND lat.financial_company = $1 GROUP BY lat.sales_account_id, lat.status, lat.reversal_user, lat.reversed_by, lat.original_id, lat.financial_company, lat.transaction_category), addresses AS (SELECT sa.id AS sales_account_id, array_agg(TRIM(COALESCE(INITCAP(aan.address_line_1), $29) \|\| $30 \|\| COALESCE(INITCAP(aan.address_line_2), $31) \|\| $32 \|\| COALESCE(INITCAP(aan.address_line_3), $33) \|\| $34 \|\| COALESCE(INITCAP(aan.city), $35) \|\| $36 \|\| COALESCE(INITCAP(aan.county_state), $37) \|\| $38 \|\| COALESCE(UPPER(aan.postcode), $39) \|\| $40 \|\| COALESCE(aan.country, $41))) AS addresses FROM sales_account sa LEFT JOIN account_address_new aan ON sa.company_id = aan.company_id WHERE sa.financial_company = $1 AND aan.status = $42 GROUP BY 1), contacts AS (SELECT sa.id AS sales_account_id, array_agg(TRIM(COALESCE(INITCAP(ac.name), $43) \|\| $44 \|\| COALESCE(LOWER(ac.email_address), $45))) AS contacts FROM sales_account sa LEFT JOIN account_contact ac ON sa.company_id = ac.company_id WHERE sa.financial_company = $1 AND ac.status = $46 GROUP BY 1), sales_accounts AS (SELECT sa.id, COALESCE(sa.old_id, $47) AS old_id, sa.company_id, sa.status, COALESCE(sa.salesperson, $48) AS salesperson, COALESCE(su.department, $49) AS department, coalesce(sa.sales_account_reference, $50) AS ledgerName_reference, coalesce(sa.old_sales_account_reference, $51) AS old_ledgerName_reference, COALESCE(sa.company_number, $52) AS company_number, COALESCE(RTRIM(INITCAP(CONCAT(su.first_name, $53, su.surname))), $54) AS sales_person_name, last_statement_date, CASE WHEN (c_terms ->> $55)::int = $56 THEN $57 WHEN (c_terms ->> $58)::int = $59 THEN $60 WHEN (c_terms ->> $61)::int = $62 THEN $63 WHEN (c_terms ->> $64)::int = $65 THEN $66 WHEN (c_terms ->> $67)::int = $68 THEN $69 WHEN (c_terms ->> $70)::int = $71 THEN $72 ELSE $73 END AS payment_type, COALESCE((c_terms ->> $74)::int, $75) AS credit_terms_period, COALESCE((c_terms ->> $76)::int, $77) AS following_month, COALESCE(sa.status_plus, $78) as status_plus, sa.internal_credit_limit, sa.agent_limit, sa.alert_type, CASE WHEN sa.account_type = $79 THEN $80 WHEN sa.account_type = $81 THEN $82 WHEN sa.account_type = $83 THEN $84 ELSE $85 END AS account_type, CASE WHEN sa.base_currency IS NOT null AND sa.base_currency != $86 THEN currency.currency_code ELSE $87 END AS base_currency, CASE WHEN spsu.id <> $88 THEN CONCAT(spsu.first_name, $89, spsu.surname) ELSE $90 END AS cs_salesperson, CASE WHEN amsu.id <> $91 THEN CONCAT(amsu.first_name, $92, amsu.surname) ELSE $93 END AS cs_account_manager, CASE WHEN succ.id <> $94 THEN CONCAT(succ.first_name, $95, succ.surname) ELSE $96 END AS credit_controller, COALESCE(sa.collection_method, $97) AS collection_method, COALESCE(evri_premium, $98) AS evri_premium FROM sales_account sa LEFT JOIN jsonb_array_elements(sa.credit_terms_json -> $99) AS c_terms ON ($100 AND (c_terms -> $101)::text::int = $102) LEFT JOIN public.system_user su ON su.id = sa.salesperson LEFT JOIN currency ON sa.base_currency = currency.id LEFT JOIN system_user_structure_margins spsm ON sa.sales_person_structure_id = spsm.id LEFT JOIN public.system_user spsu ON spsm.system_user_id = spsu.id LEFT JOIN system_user_structure_margins amsm ON sa.account_manager_structure_id = amsm.id LEFT JOIN public.system_user amsu ON amsm.system_user_id = amsu.id LEFT JOIN public.system_user succ ON sa.credit_controller_id = succ.id WHERE sa.status <> $103 AND sa.financial_company = $1) SELECT sa.id, sa.old_id, COALESCE(sa.internal_credit_limit, $104), COALESCE(sa.agent_limit, $105), sa.ledgerName_reference, sa.old_ledgerName_reference, COALESCE(lat.transaction_date, $106), $107::date, COALESCE(lat.transaction_reference, $108), RTRIM(COALESCE(company.name, $109)), sa.sales_person_name AS sales_person_name, sa.credit_controller AS credit_controller, RTRIM(COALESCE(COALESCE(NULLIF(company.trading_name, $110), company.name), $111)), COALESCE(sa.company_number, $112), SUM(COALESCE(lat.outstanding, $113)), SUM(COALESCE(lat.period_1, $114)), SUM(COALESCE(lat.period_2, $115)), SUM(COALESCE(lat.period_3, $116)), SUM(COALESCE(lat.period_4, $117)), SUM(COALESCE(lat.period_5, $118)), SUM(COALESCE(lat.period_6, $119)), sa.status, CASE WHEN sa.status_plus = $120 AND sa.alert_type = $121 THEN $122 WHEN sa.status_plus = $123 AND sa.alert_type = $124 THEN $125 WHEN sa.status_plus = $126 AND sa.alert_type = $127 THEN $128 WHEN sa.status_plus = $129 AND sa.alert_type = $130 THEN $131 WHEN sa.status_plus = $132 AND sa.alert_type = $133 THEN $134 WHEN sa.status_plus = $135 AND sa.alert_type = $136 THEN $137 ELSE sa.status_plus END, COALESCE(sa.payment_type, $138), COALESCE(sa.credit_terms_period, $139), CASE WHEN COALESCE(sa.status_plus, $140) = $141 THEN SUM(COALESCE(lat.outstanding, $142)) ELSE $143 END, COALESCE(sa.last_statement_date, $144), COALESCE(sa.following_month, $145) AS following_month, sa.account_type, sa.base_currency, CASE WHEN sa.cs_account_manager <> $146 AND sa.cs_salesperson <> $147 THEN CONCAT(sa.cs_salesperson, $148, sa.cs_account_manager) ELSE CONCAT(sa.cs_salesperson, sa.cs_account_manager) END AS cs_sp_am, COALESCE(sa.collection_method, $149) AS collection_method, sa.evri_premium, addresses.addresses \|\| contacts.contacts AS contacts_addresses FROM sales_accounts sa LEFT JOIN company ON company.id = sa.company_id LEFT JOIN lat ON lat.sales_account_id = sa.id LEFT JOIN public.system_user su ON su.id = sa.salesperson LEFT JOIN addresses ON sa.id = addresses.sales_account_id LEFT JOIN contacts ON sa.id = contacts.sales_account_id WHERE (sa.status_plus = $150 OR sa.status_plus = $151) OR sa.alert_type = $152 GROUP BY sa.id, sa.old_id, sa.internal_credit_limit, sa.agent_limit, sa.ledgerName_reference, sa.old_ledgerName_reference, lat.transaction_date, lat.transaction_reference, company.name, sa.sales_person_name, sa.credit_controller, company.trading_name, sa.company_number, sa.status, sa.status_plus, sa.alert_type, sa.payment_type, sa.credit_terms_period, sa.last_statement_date, sa.following_month, sa.account_type, sa.base_currency, sa.cs_salesperson, sa.cs_account_manager, sa.collection_method, sa.evri_premium, addresses.addresses, contacts.contacts

Unreadable Sequences

Long Running Queries

Duplicate Indexes

Suggested Indexes

Slow Queries