Thread
-
select distinct runs slow on pg 10.6
yash mehta <yash215@gmail.com> — 2019-09-09T08:30:01Z
We have a query that takes 1min to execute in postgres 10.6 and the same executes in 4 sec in Oracle database. The query is doing 'select distinct'. If I add a 'group by' clause, performance in postgres improves significantly and fetches results in 2 sec (better than oracle). But unfortunately, we cannot modify the query. Could you please suggest a way to improve performance in Postgres without modifying the query. *Original condition: time taken 1min* Sort Method: external merge Disk: 90656kB *After removing distinct from query: time taken 2sec* Sort Method: top-N heapsort Memory: 201kB *After increasing work_mem to 180MB; it takes 20sec* Sort Method: quicksort Memory: 172409kB SELECT * FROM pg_stat_statements ORDER BY total_time DESC limit 1; -[ RECORD 1 ]-------+----------------------------------------------------------------------------------------------------------------------------------------- userid | 174862 dbid | 174861 queryid | 1469376470 query | <query is too long. It selects around 300 columns> calls | 1 total_time | 59469.972661 min_time | 59469.972661 max_time | 59469.972661 mean_time | 59469.972661 stddev_time | 0 rows | 25 shared_blks_hit | 27436 shared_blks_read | 2542 shared_blks_dirtied | 0 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 257 temp_blks_written | 11333 blk_read_time | 0 blk_write_time | 0
-
Re: select distinct runs slow on pg 10.6
yash mehta <yash215@gmail.com> — 2019-09-09T08:38:03Z
In addition to below mail, we have used btree indexes for primary key columns. Below is the query: select distinct shipmentre0_.FIN_ID as FIN1_53_0_, workflowst10_.FIN_ID as FIN1_57_1_, carriers3_.FIN_ID as FIN1_40_2_, shipmentro1_.FIN_ID as FIN1_33_3_, shipmentme11_.FIN_ID as FIN1_5_4_, workflowst9_.FIN_ID as FIN1_57_5_, workflowst8_.FIN_ID as FIN1_57_6_, workflowst7_.FIN_ID as FIN1_57_7_, consignees5_.FIN_ID as FIN1_81_8_, consignees6_.FIN_ID as FIN1_81_9_, shipmentty4_.FIN_ID as FIN1_8_10_, shipmentsc2_.FIN_ID as FIN1_78_11_, shipmentre0_.MOD_ID as MOD2_53_0_, shipmentre0_.SHIPMENT_METHOD_ID as SHIPMENT3_53_0_, shipmentre0_.SHIPPER_ID as SHIPPER4_53_0_, shipmentre0_.CONSIGNEES_ID as CONSIGNEES5_53_0_, shipmentre0_.SHIPMENT_BASIS_ID as SHIPMENT6_53_0_, shipmentre0_.SHIPMENT_TYPE_ID as SHIPMENT7_53_0_, shipmentre0_.SHIPMENT_ARRANGEMENT_ID as SHIPMENT8_53_0_, shipmentre0_.SHIPMENT_DATE as SHIPMENT9_53_0_, shipmentre0_.SHIPMENT_CURRENCY_ID as SHIPMENT10_53_0_, shipmentre0_.CARRIER_CREW_EXTN_ID as CARRIER11_53_0_, shipmentre0_.END_TIME as END12_53_0_, shipmentre0_.SHIPMENT_VALUE_USD as SHIPMENT13_53_0_, shipmentre0_.SHIPMENT_VALUE_BASE as SHIPMENT14_53_0_, shipmentre0_.INSURANCE_VALUE_USD as INSURANCE15_53_0_, shipmentre0_.INSURANCE_VALUE_BASE as INSURANCE16_53_0_, shipmentre0_.REMARKS as REMARKS53_0_, shipmentre0_.DELETION_REMARKS as DELETION18_53_0_, shipmentre0_.SHIPMENT_STATUS_ID as SHIPMENT19_53_0_, shipmentre0_.VAULT_STATUS_ID as VAULT20_53_0_, shipmentre0_.SHIPMENT_CHARGE_STATUS as SHIPMENT21_53_0_, shipmentre0_.SHIPMENT_DOCUMENT_STATUS as SHIPMENT22_53_0_, shipmentre0_.INSURANCE_PROVIDER as INSURANCE23_53_0_, shipmentre0_.SHIPMENT_PROVIDER as SHIPMENT24_53_0_, shipmentre0_.SECURITY_PROVIDER_ID as SECURITY25_53_0_, shipmentre0_.CONSIGNEE_CONTACT_NAME as CONSIGNEE26_53_0_, shipmentre0_.SIGNAL as SIGNAL53_0_, shipmentre0_.CHARGEABLE_WT as CHARGEABLE28_53_0_, shipmentre0_.NO_OF_PIECES as NO29_53_0_, shipmentre0_.REGIONS_ID as REGIONS30_53_0_, shipmentre0_.IS_DELETED as IS31_53_0_, shipmentre0_.CREATED as CREATED53_0_, shipmentre0_.CREATED_BY as CREATED33_53_0_, shipmentre0_.LAST_UPDATED as LAST34_53_0_, shipmentre0_.LAST_UPDATED_BY as LAST35_53_0_, shipmentre0_.LAST_CHECKED_BY as LAST36_53_0_, shipmentre0_.LAST_MAKED as LAST37_53_0_, shipmentre0_.MAKER_CHECKER_STATUS as MAKER38_53_0_, shipmentre0_.SHADOW_ID as SHADOW39_53_0_, --(select now()) as formula48_0_, workflowst10_.WORKFLOW_MODULE as WORKFLOW2_57_1_, workflowst10_.NAME as NAME57_1_, workflowst10_.DEAL_DISPLAY_MODULE as DEAL4_57_1_, workflowst10_.WORKFLOW_LEVEL as WORKFLOW5_57_1_, workflowst10_.IS_DEAL_EDITABLE as IS6_57_1_, workflowst10_.GEN_CONFO as GEN7_57_1_, workflowst10_.GEN_DEAL_TICKET as GEN8_57_1_, workflowst10_.GEN_SETTLEMENTS as GEN9_57_1_, workflowst10_.VAULT_START as VAULT10_57_1_, workflowst10_.UPDATE_MAIN_INV as UPDATE11_57_1_, workflowst10_.UPDATE_OTHER_INV as UPDATE12_57_1_, workflowst10_.RELEASE_SHIPMENT as RELEASE13_57_1_, workflowst10_.IS_DEAL_SPLITTABLE as IS14_57_1_, workflowst10_.SEND_EMAIL as SEND15_57_1_, workflowst10_.IS_DELETED as IS16_57_1_, workflowst10_.CREATED as CREATED57_1_, workflowst10_.CREATED_BY as CREATED18_57_1_, workflowst10_.LAST_UPDATED as LAST19_57_1_, workflowst10_.LAST_UPDATED_BY as LAST20_57_1_, workflowst10_.LAST_CHECKED_BY as LAST21_57_1_, workflowst10_.LAST_MAKED as LAST22_57_1_, workflowst10_.MOD_ID as MOD23_57_1_, workflowst10_.MAKER_CHECKER_STATUS as MAKER24_57_1_, workflowst10_.SHADOW_ID as SHADOW25_57_1_, --(select now()) as formula52_1_, carriers3_.MOD_ID as MOD2_40_2_, carriers3_.CITIES_ID as CITIES3_40_2_, carriers3_.CODE as CODE40_2_, carriers3_.NAME as NAME40_2_, carriers3_.CARRIER_TYPES as CARRIER6_40_2_, carriers3_.NAME_IN_FL as NAME7_40_2_, carriers3_.IATA_CODE as IATA8_40_2_, carriers3_.KC_CODE as KC9_40_2_, carriers3_.AIRLINE_ACCT as AIRLINE10_40_2_, carriers3_.ADDRESS1 as ADDRESS11_40_2_, carriers3_.ADDRESS2 as ADDRESS12_40_2_, carriers3_.ADDRESS3 as ADDRESS13_40_2_, carriers3_.ADDRESS4 as ADDRESS14_40_2_, carriers3_.TERMINAL as TERMINAL40_2_, carriers3_.AIRLINE_AGENT as AIRLINE16_40_2_, carriers3_.ACCOUNTINGINFO as ACCOUNT17_40_2_, carriers3_.IMPORT_DEPT as IMPORT18_40_2_, carriers3_.IMPORT_AFTER_OFFICE_HOUR as IMPORT19_40_2_, carriers3_.IMPORT_CONTACT as IMPORT20_40_2_, carriers3_.IMPORT_FAX as IMPORT21_40_2_, carriers3_.IMPORT_EMAIL as IMPORT22_40_2_, carriers3_.EXPORT_DEPTT as EXPORT23_40_2_, carriers3_.EXPORT_AFTER_OFFICE_HOUR as EXPORT24_40_2_, carriers3_.EXPORT_CONTACT as EXPORT25_40_2_, carriers3_.EXPORT_FAX as EXPORT26_40_2_, carriers3_.IMPORT_CONTACT_NO as IMPORT27_40_2_, carriers3_.EXPORT_CONTACT_NO as EXPORT28_40_2_, carriers3_.EXPORT_EMAIL as EXPORT29_40_2_, carriers3_.AWB_ISSUED_BY as AWB30_40_2_, carriers3_.IS_DELETED as IS31_40_2_, carriers3_.CREATED as CREATED40_2_, carriers3_.CREATED_BY as CREATED33_40_2_, carriers3_.LAST_UPDATED as LAST34_40_2_, carriers3_.LAST_UPDATED_BY as LAST35_40_2_, carriers3_.LAST_CHECKED_BY as LAST36_40_2_, carriers3_.LAST_MAKED as LAST37_40_2_, carriers3_.MAKER_CHECKER_STATUS as MAKER38_40_2_, carriers3_.SHADOW_ID as SHADOW39_40_2_, --(select now()) as formula36_2_, shipmentro1_.MOD_ID as MOD2_33_3_, shipmentro1_.REGION_ID as REGION3_33_3_, shipmentro1_.SHIPMENT_SCHEDULE_ID as SHIPMENT4_33_3_, shipmentro1_.SHIPMENT_RECORD_ID as SHIPMENT5_33_3_, shipmentro1_.AIRWAY_BILL_NO as AIRWAY6_33_3_, shipmentro1_.SHIPMENT_DATE as SHIPMENT7_33_3_, shipmentro1_.ARRIVAL_DATE as ARRIVAL8_33_3_, shipmentro1_.LEG_NO as LEG9_33_3_, shipmentro1_.NO_OF_PCS as NO10_33_3_, shipmentro1_.CHARGEABLE_WEIGHT as CHARGEABLE11_33_3_, shipmentro1_.CARRIER_CREW_EXTN_ID as CARRIER12_33_3_, shipmentro1_.IS_DELETED as IS13_33_3_, shipmentro1_.CREATED as CREATED33_3_, shipmentro1_.CREATED_BY as CREATED15_33_3_, shipmentro1_.LAST_UPDATED as LAST16_33_3_, shipmentro1_.LAST_UPDATED_BY as LAST17_33_3_, shipmentro1_.LAST_CHECKED_BY as LAST18_33_3_, shipmentro1_.LAST_MAKED as LAST19_33_3_, shipmentro1_.MAKER_CHECKER_STATUS as MAKER20_33_3_, shipmentro1_.SHADOW_ID as SHADOW21_33_3_, --(select now()) as formula29_3_, shipmentme11_.MOD_ID as MOD2_5_4_, shipmentme11_.CODE as CODE5_4_, shipmentme11_.NAME as NAME5_4_, shipmentme11_.SHIPMENT_METHOD_TYPE as SHIPMENT5_5_4_, shipmentme11_.IS_DELETED as IS6_5_4_, shipmentme11_.CREATED as CREATED5_4_, shipmentme11_.CREATED_BY as CREATED8_5_4_, shipmentme11_.LAST_UPDATED as LAST9_5_4_, shipmentme11_.LAST_UPDATED_BY as LAST10_5_4_, shipmentme11_.LAST_CHECKED_BY as LAST11_5_4_, shipmentme11_.LAST_MAKED as LAST12_5_4_, shipmentme11_.MAKER_CHECKER_STATUS as MAKER13_5_4_, shipmentme11_.SHADOW_ID as SHADOW14_5_4_, --(select now()) as formula4_4_, workflowst9_.WORKFLOW_MODULE as WORKFLOW2_57_5_, workflowst9_.NAME as NAME57_5_, workflowst9_.DEAL_DISPLAY_MODULE as DEAL4_57_5_, workflowst9_.WORKFLOW_LEVEL as WORKFLOW5_57_5_, workflowst9_.IS_DEAL_EDITABLE as IS6_57_5_, workflowst9_.GEN_CONFO as GEN7_57_5_, workflowst9_.GEN_DEAL_TICKET as GEN8_57_5_, workflowst9_.GEN_SETTLEMENTS as GEN9_57_5_, workflowst9_.VAULT_START as VAULT10_57_5_, workflowst9_.UPDATE_MAIN_INV as UPDATE11_57_5_, workflowst9_.UPDATE_OTHER_INV as UPDATE12_57_5_, workflowst9_.RELEASE_SHIPMENT as RELEASE13_57_5_, workflowst9_.IS_DEAL_SPLITTABLE as IS14_57_5_, workflowst9_.SEND_EMAIL as SEND15_57_5_, workflowst9_.IS_DELETED as IS16_57_5_, workflowst9_.CREATED as CREATED57_5_, workflowst9_.CREATED_BY as CREATED18_57_5_, workflowst9_.LAST_UPDATED as LAST19_57_5_, workflowst9_.LAST_UPDATED_BY as LAST20_57_5_, workflowst9_.LAST_CHECKED_BY as LAST21_57_5_, workflowst9_.LAST_MAKED as LAST22_57_5_, workflowst9_.MOD_ID as MOD23_57_5_, workflowst9_.MAKER_CHECKER_STATUS as MAKER24_57_5_, workflowst9_.SHADOW_ID as SHADOW25_57_5_, --(select now()) as formula52_5_, workflowst8_.WORKFLOW_MODULE as WORKFLOW2_57_6_, workflowst8_.NAME as NAME57_6_, workflowst8_.DEAL_DISPLAY_MODULE as DEAL4_57_6_, workflowst8_.WORKFLOW_LEVEL as WORKFLOW5_57_6_, workflowst8_.IS_DEAL_EDITABLE as IS6_57_6_, workflowst8_.GEN_CONFO as GEN7_57_6_, workflowst8_.GEN_DEAL_TICKET as GEN8_57_6_, workflowst8_.GEN_SETTLEMENTS as GEN9_57_6_, workflowst8_.VAULT_START as VAULT10_57_6_, workflowst8_.UPDATE_MAIN_INV as UPDATE11_57_6_, workflowst8_.UPDATE_OTHER_INV as UPDATE12_57_6_, workflowst8_.RELEASE_SHIPMENT as RELEASE13_57_6_, workflowst8_.IS_DEAL_SPLITTABLE as IS14_57_6_, workflowst8_.SEND_EMAIL as SEND15_57_6_, workflowst8_.IS_DELETED as IS16_57_6_, workflowst8_.CREATED as CREATED57_6_, workflowst8_.CREATED_BY as CREATED18_57_6_, workflowst8_.LAST_UPDATED as LAST19_57_6_, workflowst8_.LAST_UPDATED_BY as LAST20_57_6_, workflowst8_.LAST_CHECKED_BY as LAST21_57_6_, workflowst8_.LAST_MAKED as LAST22_57_6_, workflowst8_.MOD_ID as MOD23_57_6_, workflowst8_.MAKER_CHECKER_STATUS as MAKER24_57_6_, workflowst8_.SHADOW_ID as SHADOW25_57_6_, --(select now()) as formula52_6_, workflowst7_.WORKFLOW_MODULE as WORKFLOW2_57_7_, workflowst7_.NAME as NAME57_7_, workflowst7_.DEAL_DISPLAY_MODULE as DEAL4_57_7_, workflowst7_.WORKFLOW_LEVEL as WORKFLOW5_57_7_, workflowst7_.IS_DEAL_EDITABLE as IS6_57_7_, workflowst7_.GEN_CONFO as GEN7_57_7_, workflowst7_.GEN_DEAL_TICKET as GEN8_57_7_, workflowst7_.GEN_SETTLEMENTS as GEN9_57_7_, workflowst7_.VAULT_START as VAULT10_57_7_, workflowst7_.UPDATE_MAIN_INV as UPDATE11_57_7_, workflowst7_.UPDATE_OTHER_INV as UPDATE12_57_7_, workflowst7_.RELEASE_SHIPMENT as RELEASE13_57_7_, workflowst7_.IS_DEAL_SPLITTABLE as IS14_57_7_, workflowst7_.SEND_EMAIL as SEND15_57_7_, workflowst7_.IS_DELETED as IS16_57_7_, workflowst7_.CREATED as CREATED57_7_, workflowst7_.CREATED_BY as CREATED18_57_7_, workflowst7_.LAST_UPDATED as LAST19_57_7_, workflowst7_.LAST_UPDATED_BY as LAST20_57_7_, workflowst7_.LAST_CHECKED_BY as LAST21_57_7_, workflowst7_.LAST_MAKED as LAST22_57_7_, workflowst7_.MOD_ID as MOD23_57_7_, workflowst7_.MAKER_CHECKER_STATUS as MAKER24_57_7_, workflowst7_.SHADOW_ID as SHADOW25_57_7_, --(select now()) as formula52_7_, consignees5_.MOD_ID as MOD2_81_8_, consignees5_.COUNTRIES_ID as COUNTRIES3_81_8_, consignees5_.CITIES_ID as CITIES4_81_8_, consignees5_.REGIONS_ID as REGIONS5_81_8_, consignees5_.SHORT_NAME as SHORT6_81_8_, consignees5_.IS_COUNTERPARTY as IS7_81_8_, consignees5_.NAME as NAME81_8_, consignees5_.AIRPORTS_ID as AIRPORTS9_81_8_, consignees5_.ADDRESS1 as ADDRESS10_81_8_, consignees5_.ADDRESS2 as ADDRESS11_81_8_, consignees5_.ADDRESS3 as ADDRESS12_81_8_, consignees5_.ADDRESS4 as ADDRESS13_81_8_, consignees5_.AWB_SPECIAL_CLAUSE as AWB14_81_8_, consignees5_.ISSUING_CARRIER_AGENT_NAME as ISSUING15_81_8_, consignees5_.AGENT_ADDRESS1 as AGENT16_81_8_, consignees5_.AGENT_ADDRESS2 as AGENT17_81_8_, consignees5_.POSTAL_CODE as POSTAL18_81_8_, consignees5_.IS_DELETED as IS19_81_8_, consignees5_.CREATED as CREATED81_8_, consignees5_.CREATED_BY as CREATED21_81_8_, consignees5_.LAST_UPDATED as LAST22_81_8_, consignees5_.LAST_UPDATED_BY as LAST23_81_8_, consignees5_.LAST_CHECKED_BY as LAST24_81_8_, consignees5_.LAST_MAKED as LAST25_81_8_, consignees5_.MAKER_CHECKER_STATUS as MAKER26_81_8_, consignees5_.SHADOW_ID as SHADOW27_81_8_, --(select now()) as formula74_8_, consignees6_.MOD_ID as MOD2_81_9_, consignees6_.COUNTRIES_ID as COUNTRIES3_81_9_, consignees6_.CITIES_ID as CITIES4_81_9_, consignees6_.REGIONS_ID as REGIONS5_81_9_, consignees6_.SHORT_NAME as SHORT6_81_9_, consignees6_.IS_COUNTERPARTY as IS7_81_9_, consignees6_.NAME as NAME81_9_, consignees6_.AIRPORTS_ID as AIRPORTS9_81_9_, consignees6_.ADDRESS1 as ADDRESS10_81_9_, consignees6_.ADDRESS2 as ADDRESS11_81_9_, consignees6_.ADDRESS3 as ADDRESS12_81_9_, consignees6_.ADDRESS4 as ADDRESS13_81_9_, consignees6_.AWB_SPECIAL_CLAUSE as AWB14_81_9_, consignees6_.ISSUING_CARRIER_AGENT_NAME as ISSUING15_81_9_, consignees6_.AGENT_ADDRESS1 as AGENT16_81_9_, consignees6_.AGENT_ADDRESS2 as AGENT17_81_9_, consignees6_.POSTAL_CODE as POSTAL18_81_9_, consignees6_.IS_DELETED as IS19_81_9_, consignees6_.CREATED as CREATED81_9_, consignees6_.CREATED_BY as CREATED21_81_9_, consignees6_.LAST_UPDATED as LAST22_81_9_, consignees6_.LAST_UPDATED_BY as LAST23_81_9_, consignees6_.LAST_CHECKED_BY as LAST24_81_9_, consignees6_.LAST_MAKED as LAST25_81_9_, consignees6_.MAKER_CHECKER_STATUS as MAKER26_81_9_, consignees6_.SHADOW_ID as SHADOW27_81_9_, --(select now()) as formula74_9_, shipmentty4_.MOD_ID as MOD2_8_10_, shipmentty4_.CODE as CODE8_10_, shipmentty4_.NAME as NAME8_10_, shipmentty4_.REGIONS_ID as REGIONS5_8_10_, shipmentty4_.IS_DELETED as IS6_8_10_, shipmentty4_.CREATED as CREATED8_10_, shipmentty4_.CREATED_BY as CREATED8_8_10_, shipmentty4_.LAST_UPDATED as LAST9_8_10_, shipmentty4_.LAST_UPDATED_BY as LAST10_8_10_, shipmentty4_.LAST_CHECKED_BY as LAST11_8_10_, shipmentty4_.LAST_MAKED as LAST12_8_10_, shipmentty4_.MAKER_CHECKER_STATUS as MAKER13_8_10_, shipmentty4_.SHADOW_ID as SHADOW14_8_10_, --(select now()) as formula6_10_, shipmentsc2_.MOD_ID as MOD2_78_11_, shipmentsc2_.CARRIER_ID as CARRIER3_78_11_, shipmentsc2_.ORIGIN_AIRPORTS_ID as ORIGIN4_78_11_, shipmentsc2_.DEST_AIRPORTS_ID as DEST5_78_11_, shipmentsc2_.SCHEDULE as SCHEDULE78_11_, shipmentsc2_.ARRIVAL_DATE as ARRIVAL7_78_11_, shipmentsc2_.EST_TIME_DEPARTURE as EST8_78_11_, shipmentsc2_.EST_TIME_ARRIVAL as EST9_78_11_, shipmentsc2_.ROUTE_LEG_SEQ_NO as ROUTE10_78_11_, shipmentsc2_.CUTOFF_HOURS_BEFORE_DEPARTURE as CUTOFF11_78_11_, shipmentsc2_.AVAILABLE_IN_A_WEEK as AVAILABLE12_78_11_, shipmentsc2_.REMARKS as REMARKS78_11_, shipmentsc2_.STATUS as STATUS78_11_, shipmentsc2_.REGION_ID as REGION15_78_11_, shipmentsc2_.IS_DELETED as IS16_78_11_, shipmentsc2_.CREATED as CREATED78_11_, shipmentsc2_.CREATED_BY as CREATED18_78_11_, shipmentsc2_.LAST_UPDATED as LAST19_78_11_, shipmentsc2_.LAST_UPDATED_BY as LAST20_78_11_, shipmentsc2_.LAST_CHECKED_BY as LAST21_78_11_, shipmentsc2_.LAST_MAKED as LAST22_78_11_, shipmentsc2_.MAKER_CHECKER_STATUS as MAKER23_78_11_, shipmentsc2_.SHADOW_ID as SHADOW24_78_11_, --(select now()) as formula71_11_, shipmentro1_.SHIPMENT_RECORD_ID as SHIPMENT5___, shipmentro1_.FIN_ID as FIN1___ from TBLS_SHIPMENT_RECORDS shipmentre0_ inner join TBLS_SHIPMENT_RECORD_ROUTING shipmentro1_ on shipmentre0_.FIN_ID = shipmentro1_.SHIPMENT_RECORD_ID inner join TBLS_SHIPMENT_SCHEDULES shipmentsc2_ on shipmentro1_.SHIPMENT_SCHEDULE_ID = shipmentsc2_.FIN_ID inner join TBLS_CARRIERS carriers3_ on shipmentsc2_.CARRIER_ID = carriers3_.FIN_ID inner join TBLS_SHIPMENT_TYPES shipmentty4_ on shipmentre0_.SHIPMENT_TYPE_ID = shipmentty4_.FIN_ID inner join TBLS_CONSIGNEES consignees5_ on shipmentre0_.SHIPPER_ID = consignees5_.FIN_ID inner join TBLS_CONSIGNEES consignees6_ on shipmentre0_.CONSIGNEES_ID = consignees6_.FIN_ID inner join TBLS_WORKFLOW_STATES workflowst7_ on shipmentre0_.SHIPMENT_STATUS_ID = workflowst7_.FIN_ID inner join TBLS_WORKFLOW_STATES workflowst8_ on shipmentre0_.SHIPMENT_CHARGE_STATUS = workflowst8_.FIN_ID inner join TBLS_WORKFLOW_STATES workflowst9_ on shipmentre0_.SHIPMENT_DOCUMENT_STATUS = workflowst9_.FIN_ID inner join TBLS_WORKFLOW_STATES workflowst10_ on shipmentre0_.VAULT_STATUS_ID = workflowst10_.FIN_ID left outer join TBLS_SHIPMENT_METHODS shipmentme11_ on shipmentre0_.SHIPMENT_METHOD_ID = shipmentme11_.FIN_ID left outer join TBLS_BANK_NOTES_DEALS_LEGS deallegs12_ on shipmentre0_.FIN_ID = deallegs12_.SHIPMENT_RECORDS_ID where (shipmentro1_.LEG_NO = (select min(shipmentro13_.LEG_NO) from TBLS_SHIPMENT_RECORD_ROUTING shipmentro13_ where shipmentre0_.FIN_ID = shipmentro13_.SHIPMENT_RECORD_ID and ((shipmentro13_.IS_DELETED = 'N')))) and (shipmentre0_.IS_DELETED = 'N') and (TO_CHAR(shipmentro1_.ARRIVAL_DATE, 'YYYY-MM-DD') <= '2019-08-29') order by shipmentre0_.SHIPMENT_DATE limit 25 ; On Mon, Sep 9, 2019 at 2:00 PM yash mehta <yash215@gmail.com> wrote: > We have a query that takes 1min to execute in postgres 10.6 and the same > executes in 4 sec in Oracle database. The query is doing 'select distinct'. > If I add a 'group by' clause, performance in postgres improves > significantly and fetches results in 2 sec (better than oracle). But > unfortunately, we cannot modify the query. Could you please suggest a way > to improve performance in Postgres without modifying the query. > > *Original condition: time taken 1min* > > Sort Method: external merge Disk: 90656kB > > > > *After removing distinct from query: time taken 2sec* > > Sort Method: top-N heapsort Memory: 201kB > > > > *After increasing work_mem to 180MB; it takes 20sec* > > Sort Method: quicksort Memory: 172409kB > > > > SELECT * FROM pg_stat_statements ORDER BY total_time DESC limit 1; > > -[ RECORD 1 > ]-------+----------------------------------------------------------------------------------------------------------------------------------------- > > userid | 174862 > > dbid | 174861 > > queryid | 1469376470 > > query | <query is too long. It selects around 300 columns> > > calls | 1 > > total_time | 59469.972661 > > min_time | 59469.972661 > > max_time | 59469.972661 > > mean_time | 59469.972661 > > stddev_time | 0 > > rows | 25 > > shared_blks_hit | 27436 > > shared_blks_read | 2542 > > shared_blks_dirtied | 0 > > shared_blks_written | 0 > > local_blks_hit | 0 > > local_blks_read | 0 > > local_blks_dirtied | 0 > > local_blks_written | 0 > > temp_blks_read | 257 > > temp_blks_written | 11333 > > blk_read_time | 0 > > blk_write_time | 0 >
-
Re: select distinct runs slow on pg 10.6
Flo Rance <trourance@gmail.com> — 2019-09-09T10:00:14Z
On Mon, Sep 9, 2019 at 10:38 AM yash mehta <yash215@gmail.com> wrote: > In addition to below mail, we have used btree indexes for primary key > columns. Below is the query: > > select distinct shipmentre0_.FIN_ID as FIN1_53_0_, > workflowst10_.FIN_ID as FIN1_57_1_, > carriers3_.FIN_ID as FIN1_40_2_, > shipmentro1_.FIN_ID as FIN1_33_3_, > shipmentme11_.FIN_ID as FIN1_5_4_, > workflowst9_.FIN_ID as FIN1_57_5_, > workflowst8_.FIN_ID as FIN1_57_6_, > workflowst7_.FIN_ID as FIN1_57_7_, > consignees5_.FIN_ID as FIN1_81_8_, > consignees6_.FIN_ID as FIN1_81_9_, > shipmentty4_.FIN_ID as FIN1_8_10_, > shipmentsc2_.FIN_ID as FIN1_78_11_, > shipmentre0_.MOD_ID as MOD2_53_0_, > shipmentre0_.SHIPMENT_METHOD_ID as SHIPMENT3_53_0_, > shipmentre0_.SHIPPER_ID as SHIPPER4_53_0_, > shipmentre0_.CONSIGNEES_ID as CONSIGNEES5_53_0_, > shipmentre0_.SHIPMENT_BASIS_ID as SHIPMENT6_53_0_, > shipmentre0_.SHIPMENT_TYPE_ID as SHIPMENT7_53_0_, > shipmentre0_.SHIPMENT_ARRANGEMENT_ID as SHIPMENT8_53_0_, > shipmentre0_.SHIPMENT_DATE as SHIPMENT9_53_0_, > shipmentre0_.SHIPMENT_CURRENCY_ID as SHIPMENT10_53_0_, > shipmentre0_.CARRIER_CREW_EXTN_ID as CARRIER11_53_0_, > shipmentre0_.END_TIME as END12_53_0_, > shipmentre0_.SHIPMENT_VALUE_USD as SHIPMENT13_53_0_, > shipmentre0_.SHIPMENT_VALUE_BASE as SHIPMENT14_53_0_, > shipmentre0_.INSURANCE_VALUE_USD as INSURANCE15_53_0_, > shipmentre0_.INSURANCE_VALUE_BASE as INSURANCE16_53_0_, > shipmentre0_.REMARKS as REMARKS53_0_, > shipmentre0_.DELETION_REMARKS as DELETION18_53_0_, > shipmentre0_.SHIPMENT_STATUS_ID as SHIPMENT19_53_0_, > shipmentre0_.VAULT_STATUS_ID as VAULT20_53_0_, > shipmentre0_.SHIPMENT_CHARGE_STATUS as SHIPMENT21_53_0_, > shipmentre0_.SHIPMENT_DOCUMENT_STATUS as SHIPMENT22_53_0_, > shipmentre0_.INSURANCE_PROVIDER as INSURANCE23_53_0_, > shipmentre0_.SHIPMENT_PROVIDER as SHIPMENT24_53_0_, > shipmentre0_.SECURITY_PROVIDER_ID as SECURITY25_53_0_, > shipmentre0_.CONSIGNEE_CONTACT_NAME as CONSIGNEE26_53_0_, > shipmentre0_.SIGNAL as SIGNAL53_0_, > shipmentre0_.CHARGEABLE_WT as CHARGEABLE28_53_0_, > shipmentre0_.NO_OF_PIECES as NO29_53_0_, > shipmentre0_.REGIONS_ID as REGIONS30_53_0_, > shipmentre0_.IS_DELETED as IS31_53_0_, > shipmentre0_.CREATED as CREATED53_0_, > shipmentre0_.CREATED_BY as CREATED33_53_0_, > shipmentre0_.LAST_UPDATED as LAST34_53_0_, > shipmentre0_.LAST_UPDATED_BY as LAST35_53_0_, > shipmentre0_.LAST_CHECKED_BY as LAST36_53_0_, > shipmentre0_.LAST_MAKED as LAST37_53_0_, > shipmentre0_.MAKER_CHECKER_STATUS as MAKER38_53_0_, > shipmentre0_.SHADOW_ID as SHADOW39_53_0_, > --(select now()) as formula48_0_, > workflowst10_.WORKFLOW_MODULE as WORKFLOW2_57_1_, > workflowst10_.NAME as NAME57_1_, > workflowst10_.DEAL_DISPLAY_MODULE as DEAL4_57_1_, > workflowst10_.WORKFLOW_LEVEL as WORKFLOW5_57_1_, > workflowst10_.IS_DEAL_EDITABLE as IS6_57_1_, > workflowst10_.GEN_CONFO as GEN7_57_1_, > workflowst10_.GEN_DEAL_TICKET as GEN8_57_1_, > workflowst10_.GEN_SETTLEMENTS as GEN9_57_1_, > workflowst10_.VAULT_START as VAULT10_57_1_, > workflowst10_.UPDATE_MAIN_INV as UPDATE11_57_1_, > workflowst10_.UPDATE_OTHER_INV as UPDATE12_57_1_, > workflowst10_.RELEASE_SHIPMENT as RELEASE13_57_1_, > workflowst10_.IS_DEAL_SPLITTABLE as IS14_57_1_, > workflowst10_.SEND_EMAIL as SEND15_57_1_, > workflowst10_.IS_DELETED as IS16_57_1_, > workflowst10_.CREATED as CREATED57_1_, > workflowst10_.CREATED_BY as CREATED18_57_1_, > workflowst10_.LAST_UPDATED as LAST19_57_1_, > workflowst10_.LAST_UPDATED_BY as LAST20_57_1_, > workflowst10_.LAST_CHECKED_BY as LAST21_57_1_, > workflowst10_.LAST_MAKED as LAST22_57_1_, > workflowst10_.MOD_ID as MOD23_57_1_, > workflowst10_.MAKER_CHECKER_STATUS as MAKER24_57_1_, > workflowst10_.SHADOW_ID as SHADOW25_57_1_, > --(select now()) as formula52_1_, > carriers3_.MOD_ID as MOD2_40_2_, > carriers3_.CITIES_ID as CITIES3_40_2_, > carriers3_.CODE as CODE40_2_, > carriers3_.NAME as NAME40_2_, > carriers3_.CARRIER_TYPES as CARRIER6_40_2_, > carriers3_.NAME_IN_FL as NAME7_40_2_, > carriers3_.IATA_CODE as IATA8_40_2_, > carriers3_.KC_CODE as KC9_40_2_, > carriers3_.AIRLINE_ACCT as AIRLINE10_40_2_, > carriers3_.ADDRESS1 as ADDRESS11_40_2_, > carriers3_.ADDRESS2 as ADDRESS12_40_2_, > carriers3_.ADDRESS3 as ADDRESS13_40_2_, > carriers3_.ADDRESS4 as ADDRESS14_40_2_, > carriers3_.TERMINAL as TERMINAL40_2_, > carriers3_.AIRLINE_AGENT as AIRLINE16_40_2_, > carriers3_.ACCOUNTINGINFO as ACCOUNT17_40_2_, > carriers3_.IMPORT_DEPT as IMPORT18_40_2_, > carriers3_.IMPORT_AFTER_OFFICE_HOUR as IMPORT19_40_2_, > carriers3_.IMPORT_CONTACT as IMPORT20_40_2_, > carriers3_.IMPORT_FAX as IMPORT21_40_2_, > carriers3_.IMPORT_EMAIL as IMPORT22_40_2_, > carriers3_.EXPORT_DEPTT as EXPORT23_40_2_, > carriers3_.EXPORT_AFTER_OFFICE_HOUR as EXPORT24_40_2_, > carriers3_.EXPORT_CONTACT as EXPORT25_40_2_, > carriers3_.EXPORT_FAX as EXPORT26_40_2_, > carriers3_.IMPORT_CONTACT_NO as IMPORT27_40_2_, > carriers3_.EXPORT_CONTACT_NO as EXPORT28_40_2_, > carriers3_.EXPORT_EMAIL as EXPORT29_40_2_, > carriers3_.AWB_ISSUED_BY as AWB30_40_2_, > carriers3_.IS_DELETED as IS31_40_2_, > carriers3_.CREATED as CREATED40_2_, > carriers3_.CREATED_BY as CREATED33_40_2_, > carriers3_.LAST_UPDATED as LAST34_40_2_, > carriers3_.LAST_UPDATED_BY as LAST35_40_2_, > carriers3_.LAST_CHECKED_BY as LAST36_40_2_, > carriers3_.LAST_MAKED as LAST37_40_2_, > carriers3_.MAKER_CHECKER_STATUS as MAKER38_40_2_, > carriers3_.SHADOW_ID as SHADOW39_40_2_, > --(select now()) as formula36_2_, > shipmentro1_.MOD_ID as MOD2_33_3_, > shipmentro1_.REGION_ID as REGION3_33_3_, > shipmentro1_.SHIPMENT_SCHEDULE_ID as SHIPMENT4_33_3_, > shipmentro1_.SHIPMENT_RECORD_ID as SHIPMENT5_33_3_, > shipmentro1_.AIRWAY_BILL_NO as AIRWAY6_33_3_, > shipmentro1_.SHIPMENT_DATE as SHIPMENT7_33_3_, > shipmentro1_.ARRIVAL_DATE as ARRIVAL8_33_3_, > shipmentro1_.LEG_NO as LEG9_33_3_, > shipmentro1_.NO_OF_PCS as NO10_33_3_, > shipmentro1_.CHARGEABLE_WEIGHT as CHARGEABLE11_33_3_, > shipmentro1_.CARRIER_CREW_EXTN_ID as CARRIER12_33_3_, > shipmentro1_.IS_DELETED as IS13_33_3_, > shipmentro1_.CREATED as CREATED33_3_, > shipmentro1_.CREATED_BY as CREATED15_33_3_, > shipmentro1_.LAST_UPDATED as LAST16_33_3_, > shipmentro1_.LAST_UPDATED_BY as LAST17_33_3_, > shipmentro1_.LAST_CHECKED_BY as LAST18_33_3_, > shipmentro1_.LAST_MAKED as LAST19_33_3_, > shipmentro1_.MAKER_CHECKER_STATUS as MAKER20_33_3_, > shipmentro1_.SHADOW_ID as SHADOW21_33_3_, > --(select now()) as formula29_3_, > shipmentme11_.MOD_ID as MOD2_5_4_, > shipmentme11_.CODE as CODE5_4_, > shipmentme11_.NAME as NAME5_4_, > shipmentme11_.SHIPMENT_METHOD_TYPE as SHIPMENT5_5_4_, > shipmentme11_.IS_DELETED as IS6_5_4_, > shipmentme11_.CREATED as CREATED5_4_, > shipmentme11_.CREATED_BY as CREATED8_5_4_, > shipmentme11_.LAST_UPDATED as LAST9_5_4_, > shipmentme11_.LAST_UPDATED_BY as LAST10_5_4_, > shipmentme11_.LAST_CHECKED_BY as LAST11_5_4_, > shipmentme11_.LAST_MAKED as LAST12_5_4_, > shipmentme11_.MAKER_CHECKER_STATUS as MAKER13_5_4_, > shipmentme11_.SHADOW_ID as SHADOW14_5_4_, > --(select now()) as formula4_4_, > workflowst9_.WORKFLOW_MODULE as WORKFLOW2_57_5_, > workflowst9_.NAME as NAME57_5_, > workflowst9_.DEAL_DISPLAY_MODULE as DEAL4_57_5_, > workflowst9_.WORKFLOW_LEVEL as WORKFLOW5_57_5_, > workflowst9_.IS_DEAL_EDITABLE as IS6_57_5_, > workflowst9_.GEN_CONFO as GEN7_57_5_, > workflowst9_.GEN_DEAL_TICKET as GEN8_57_5_, > workflowst9_.GEN_SETTLEMENTS as GEN9_57_5_, > workflowst9_.VAULT_START as VAULT10_57_5_, > workflowst9_.UPDATE_MAIN_INV as UPDATE11_57_5_, > workflowst9_.UPDATE_OTHER_INV as UPDATE12_57_5_, > workflowst9_.RELEASE_SHIPMENT as RELEASE13_57_5_, > workflowst9_.IS_DEAL_SPLITTABLE as IS14_57_5_, > workflowst9_.SEND_EMAIL as SEND15_57_5_, > workflowst9_.IS_DELETED as IS16_57_5_, > workflowst9_.CREATED as CREATED57_5_, > workflowst9_.CREATED_BY as CREATED18_57_5_, > workflowst9_.LAST_UPDATED as LAST19_57_5_, > workflowst9_.LAST_UPDATED_BY as LAST20_57_5_, > workflowst9_.LAST_CHECKED_BY as LAST21_57_5_, > workflowst9_.LAST_MAKED as LAST22_57_5_, > workflowst9_.MOD_ID as MOD23_57_5_, > workflowst9_.MAKER_CHECKER_STATUS as MAKER24_57_5_, > workflowst9_.SHADOW_ID as SHADOW25_57_5_, > --(select now()) as formula52_5_, > workflowst8_.WORKFLOW_MODULE as WORKFLOW2_57_6_, > workflowst8_.NAME as NAME57_6_, > workflowst8_.DEAL_DISPLAY_MODULE as DEAL4_57_6_, > workflowst8_.WORKFLOW_LEVEL as WORKFLOW5_57_6_, > workflowst8_.IS_DEAL_EDITABLE as IS6_57_6_, > workflowst8_.GEN_CONFO as GEN7_57_6_, > workflowst8_.GEN_DEAL_TICKET as GEN8_57_6_, > workflowst8_.GEN_SETTLEMENTS as GEN9_57_6_, > workflowst8_.VAULT_START as VAULT10_57_6_, > workflowst8_.UPDATE_MAIN_INV as UPDATE11_57_6_, > workflowst8_.UPDATE_OTHER_INV as UPDATE12_57_6_, > workflowst8_.RELEASE_SHIPMENT as RELEASE13_57_6_, > workflowst8_.IS_DEAL_SPLITTABLE as IS14_57_6_, > workflowst8_.SEND_EMAIL as SEND15_57_6_, > workflowst8_.IS_DELETED as IS16_57_6_, > workflowst8_.CREATED as CREATED57_6_, > workflowst8_.CREATED_BY as CREATED18_57_6_, > workflowst8_.LAST_UPDATED as LAST19_57_6_, > workflowst8_.LAST_UPDATED_BY as LAST20_57_6_, > workflowst8_.LAST_CHECKED_BY as LAST21_57_6_, > workflowst8_.LAST_MAKED as LAST22_57_6_, > workflowst8_.MOD_ID as MOD23_57_6_, > workflowst8_.MAKER_CHECKER_STATUS as MAKER24_57_6_, > workflowst8_.SHADOW_ID as SHADOW25_57_6_, > --(select now()) as formula52_6_, > workflowst7_.WORKFLOW_MODULE as WORKFLOW2_57_7_, > workflowst7_.NAME as NAME57_7_, > workflowst7_.DEAL_DISPLAY_MODULE as DEAL4_57_7_, > workflowst7_.WORKFLOW_LEVEL as WORKFLOW5_57_7_, > workflowst7_.IS_DEAL_EDITABLE as IS6_57_7_, > workflowst7_.GEN_CONFO as GEN7_57_7_, > workflowst7_.GEN_DEAL_TICKET as GEN8_57_7_, > workflowst7_.GEN_SETTLEMENTS as GEN9_57_7_, > workflowst7_.VAULT_START as VAULT10_57_7_, > workflowst7_.UPDATE_MAIN_INV as UPDATE11_57_7_, > workflowst7_.UPDATE_OTHER_INV as UPDATE12_57_7_, > workflowst7_.RELEASE_SHIPMENT as RELEASE13_57_7_, > workflowst7_.IS_DEAL_SPLITTABLE as IS14_57_7_, > workflowst7_.SEND_EMAIL as SEND15_57_7_, > workflowst7_.IS_DELETED as IS16_57_7_, > workflowst7_.CREATED as CREATED57_7_, > workflowst7_.CREATED_BY as CREATED18_57_7_, > workflowst7_.LAST_UPDATED as LAST19_57_7_, > workflowst7_.LAST_UPDATED_BY as LAST20_57_7_, > workflowst7_.LAST_CHECKED_BY as LAST21_57_7_, > workflowst7_.LAST_MAKED as LAST22_57_7_, > workflowst7_.MOD_ID as MOD23_57_7_, > workflowst7_.MAKER_CHECKER_STATUS as MAKER24_57_7_, > workflowst7_.SHADOW_ID as SHADOW25_57_7_, > --(select now()) as formula52_7_, > consignees5_.MOD_ID as MOD2_81_8_, > consignees5_.COUNTRIES_ID as COUNTRIES3_81_8_, > consignees5_.CITIES_ID as CITIES4_81_8_, > consignees5_.REGIONS_ID as REGIONS5_81_8_, > consignees5_.SHORT_NAME as SHORT6_81_8_, > consignees5_.IS_COUNTERPARTY as IS7_81_8_, > consignees5_.NAME as NAME81_8_, > consignees5_.AIRPORTS_ID as AIRPORTS9_81_8_, > consignees5_.ADDRESS1 as ADDRESS10_81_8_, > consignees5_.ADDRESS2 as ADDRESS11_81_8_, > consignees5_.ADDRESS3 as ADDRESS12_81_8_, > consignees5_.ADDRESS4 as ADDRESS13_81_8_, > consignees5_.AWB_SPECIAL_CLAUSE as AWB14_81_8_, > consignees5_.ISSUING_CARRIER_AGENT_NAME as ISSUING15_81_8_, > consignees5_.AGENT_ADDRESS1 as AGENT16_81_8_, > consignees5_.AGENT_ADDRESS2 as AGENT17_81_8_, > consignees5_.POSTAL_CODE as POSTAL18_81_8_, > consignees5_.IS_DELETED as IS19_81_8_, > consignees5_.CREATED as CREATED81_8_, > consignees5_.CREATED_BY as CREATED21_81_8_, > consignees5_.LAST_UPDATED as LAST22_81_8_, > consignees5_.LAST_UPDATED_BY as LAST23_81_8_, > consignees5_.LAST_CHECKED_BY as LAST24_81_8_, > consignees5_.LAST_MAKED as LAST25_81_8_, > consignees5_.MAKER_CHECKER_STATUS as MAKER26_81_8_, > consignees5_.SHADOW_ID as SHADOW27_81_8_, > --(select now()) as formula74_8_, > consignees6_.MOD_ID as MOD2_81_9_, > consignees6_.COUNTRIES_ID as COUNTRIES3_81_9_, > consignees6_.CITIES_ID as CITIES4_81_9_, > consignees6_.REGIONS_ID as REGIONS5_81_9_, > consignees6_.SHORT_NAME as SHORT6_81_9_, > consignees6_.IS_COUNTERPARTY as IS7_81_9_, > consignees6_.NAME as NAME81_9_, > consignees6_.AIRPORTS_ID as AIRPORTS9_81_9_, > consignees6_.ADDRESS1 as ADDRESS10_81_9_, > consignees6_.ADDRESS2 as ADDRESS11_81_9_, > consignees6_.ADDRESS3 as ADDRESS12_81_9_, > consignees6_.ADDRESS4 as ADDRESS13_81_9_, > consignees6_.AWB_SPECIAL_CLAUSE as AWB14_81_9_, > consignees6_.ISSUING_CARRIER_AGENT_NAME as ISSUING15_81_9_, > consignees6_.AGENT_ADDRESS1 as AGENT16_81_9_, > consignees6_.AGENT_ADDRESS2 as AGENT17_81_9_, > consignees6_.POSTAL_CODE as POSTAL18_81_9_, > consignees6_.IS_DELETED as IS19_81_9_, > consignees6_.CREATED as CREATED81_9_, > consignees6_.CREATED_BY as CREATED21_81_9_, > consignees6_.LAST_UPDATED as LAST22_81_9_, > consignees6_.LAST_UPDATED_BY as LAST23_81_9_, > consignees6_.LAST_CHECKED_BY as LAST24_81_9_, > consignees6_.LAST_MAKED as LAST25_81_9_, > consignees6_.MAKER_CHECKER_STATUS as MAKER26_81_9_, > consignees6_.SHADOW_ID as SHADOW27_81_9_, > --(select now()) as formula74_9_, > shipmentty4_.MOD_ID as MOD2_8_10_, > shipmentty4_.CODE as CODE8_10_, > shipmentty4_.NAME as NAME8_10_, > shipmentty4_.REGIONS_ID as REGIONS5_8_10_, > shipmentty4_.IS_DELETED as IS6_8_10_, > shipmentty4_.CREATED as CREATED8_10_, > shipmentty4_.CREATED_BY as CREATED8_8_10_, > shipmentty4_.LAST_UPDATED as LAST9_8_10_, > shipmentty4_.LAST_UPDATED_BY as LAST10_8_10_, > shipmentty4_.LAST_CHECKED_BY as LAST11_8_10_, > shipmentty4_.LAST_MAKED as LAST12_8_10_, > shipmentty4_.MAKER_CHECKER_STATUS as MAKER13_8_10_, > shipmentty4_.SHADOW_ID as SHADOW14_8_10_, > --(select now()) as formula6_10_, > shipmentsc2_.MOD_ID as MOD2_78_11_, > shipmentsc2_.CARRIER_ID as CARRIER3_78_11_, > shipmentsc2_.ORIGIN_AIRPORTS_ID as ORIGIN4_78_11_, > shipmentsc2_.DEST_AIRPORTS_ID as DEST5_78_11_, > shipmentsc2_.SCHEDULE as SCHEDULE78_11_, > shipmentsc2_.ARRIVAL_DATE as ARRIVAL7_78_11_, > shipmentsc2_.EST_TIME_DEPARTURE as EST8_78_11_, > shipmentsc2_.EST_TIME_ARRIVAL as EST9_78_11_, > shipmentsc2_.ROUTE_LEG_SEQ_NO as ROUTE10_78_11_, > shipmentsc2_.CUTOFF_HOURS_BEFORE_DEPARTURE as CUTOFF11_78_11_, > shipmentsc2_.AVAILABLE_IN_A_WEEK as AVAILABLE12_78_11_, > shipmentsc2_.REMARKS as REMARKS78_11_, > shipmentsc2_.STATUS as STATUS78_11_, > shipmentsc2_.REGION_ID as REGION15_78_11_, > shipmentsc2_.IS_DELETED as IS16_78_11_, > shipmentsc2_.CREATED as CREATED78_11_, > shipmentsc2_.CREATED_BY as CREATED18_78_11_, > shipmentsc2_.LAST_UPDATED as LAST19_78_11_, > shipmentsc2_.LAST_UPDATED_BY as LAST20_78_11_, > shipmentsc2_.LAST_CHECKED_BY as LAST21_78_11_, > shipmentsc2_.LAST_MAKED as LAST22_78_11_, > shipmentsc2_.MAKER_CHECKER_STATUS as MAKER23_78_11_, > shipmentsc2_.SHADOW_ID as SHADOW24_78_11_, > --(select now()) as formula71_11_, > shipmentro1_.SHIPMENT_RECORD_ID as SHIPMENT5___, > shipmentro1_.FIN_ID as FIN1___ > from TBLS_SHIPMENT_RECORDS shipmentre0_ > inner join TBLS_SHIPMENT_RECORD_ROUTING shipmentro1_ on > shipmentre0_.FIN_ID = shipmentro1_.SHIPMENT_RECORD_ID > inner join TBLS_SHIPMENT_SCHEDULES shipmentsc2_ on > shipmentro1_.SHIPMENT_SCHEDULE_ID = shipmentsc2_.FIN_ID > inner join TBLS_CARRIERS carriers3_ on shipmentsc2_.CARRIER_ID = > carriers3_.FIN_ID > inner join TBLS_SHIPMENT_TYPES shipmentty4_ on > shipmentre0_.SHIPMENT_TYPE_ID = shipmentty4_.FIN_ID > inner join TBLS_CONSIGNEES consignees5_ on shipmentre0_.SHIPPER_ID = > consignees5_.FIN_ID > inner join TBLS_CONSIGNEES consignees6_ on shipmentre0_.CONSIGNEES_ID = > consignees6_.FIN_ID > inner join TBLS_WORKFLOW_STATES workflowst7_ on > shipmentre0_.SHIPMENT_STATUS_ID = workflowst7_.FIN_ID > inner join TBLS_WORKFLOW_STATES workflowst8_ on > shipmentre0_.SHIPMENT_CHARGE_STATUS = workflowst8_.FIN_ID > inner join TBLS_WORKFLOW_STATES workflowst9_ on > shipmentre0_.SHIPMENT_DOCUMENT_STATUS = workflowst9_.FIN_ID > inner join TBLS_WORKFLOW_STATES workflowst10_ on > shipmentre0_.VAULT_STATUS_ID = workflowst10_.FIN_ID > left outer join TBLS_SHIPMENT_METHODS shipmentme11_ on > shipmentre0_.SHIPMENT_METHOD_ID = shipmentme11_.FIN_ID > left outer join TBLS_BANK_NOTES_DEALS_LEGS deallegs12_ on > shipmentre0_.FIN_ID = deallegs12_.SHIPMENT_RECORDS_ID > where (shipmentro1_.LEG_NO = (select min(shipmentro13_.LEG_NO) > from TBLS_SHIPMENT_RECORD_ROUTING shipmentro13_ > where shipmentre0_.FIN_ID = shipmentro13_.SHIPMENT_RECORD_ID > and ((shipmentro13_.IS_DELETED = 'N')))) > and (shipmentre0_.IS_DELETED = 'N') > and (TO_CHAR(shipmentro1_.ARRIVAL_DATE, 'YYYY-MM-DD') <= '2019-08-29') > order by shipmentre0_.SHIPMENT_DATE > limit 25 > ; > > > On Mon, Sep 9, 2019 at 2:00 PM yash mehta <yash215@gmail.com> wrote: > >> We have a query that takes 1min to execute in postgres 10.6 and the same >> executes in 4 sec in Oracle database. The query is doing 'select distinct'. >> If I add a 'group by' clause, performance in postgres improves >> significantly and fetches results in 2 sec (better than oracle). But >> unfortunately, we cannot modify the query. Could you please suggest a way >> to improve performance in Postgres without modifying the query. >> >> *Original condition: time taken 1min* >> >> Sort Method: external merge Disk: 90656kB >> >> >> >> *After removing distinct from query: time taken 2sec* >> >> Sort Method: top-N heapsort Memory: 201kB >> >> >> >> *After increasing work_mem to 180MB; it takes 20sec* >> >> Sort Method: quicksort Memory: 172409kB >> >> >> >> SELECT * FROM pg_stat_statements ORDER BY total_time DESC limit 1; >> >> -[ RECORD 1 >> ]-------+----------------------------------------------------------------------------------------------------------------------------------------- >> >> userid | 174862 >> >> dbid | 174861 >> >> queryid | 1469376470 >> >> query | <query is too long. It selects around 300 columns> >> >> calls | 1 >> >> total_time | 59469.972661 >> >> min_time | 59469.972661 >> >> max_time | 59469.972661 >> >> mean_time | 59469.972661 >> >> stddev_time | 0 >> >> rows | 25 >> >> shared_blks_hit | 27436 >> >> shared_blks_read | 2542 >> >> shared_blks_dirtied | 0 >> >> shared_blks_written | 0 >> >> local_blks_hit | 0 >> >> local_blks_read | 0 >> >> local_blks_dirtied | 0 >> >> local_blks_written | 0 >> >> temp_blks_read | 257 >> >> temp_blks_written | 11333 >> >> blk_read_time | 0 >> >> blk_write_time | 0 >> > IMO, an explain analyze of the query would be useful in order for people to help you. e.g. https://explain.depesz.com Regards, Flo
-
Re: select distinct runs slow on pg 10.6
yash mehta <yash215@gmail.com> — 2019-09-09T10:29:53Z
Hi Flo, PFB the explain plan: "Limit (cost=5925.59..5944.03 rows=25 width=6994) (actual time=57997.219..58002.451 rows=25 loops=1)" " -> Unique (cost=5925.59..5969.10 rows=59 width=6994) (actual time=57997.218..58002.416 rows=25 loops=1)" " -> Sort (cost=5925.59..5925.74 rows=59 width=6994) (actual time=57997.214..57997.537 rows=550 loops=1)" " Sort Key: shipmentre0_.shipment_date, shipmentre0_.fin_id, workflowst10_.fin_id, carriers3_.fin_id, shipmentro1_.fin_id, shipmentme11_.fin_id, workflowst9_.fin_id, workflowst8_.fin_id, workflowst7_.fin_id, consignees5_.fin_id, consignees6_.fin_id, shipmentty4_.fin_id, shipmentsc2_.fin_id, shipmentre0_.mod_id, shipmentre0_.shipment_method_id, shipmentre0_.shipment_basis_id, shipmentre0_.shipment_arrangement_id, shipmentre0_.shipment_currency_id, shipmentre0_.carrier_crew_extn_id, shipmentre0_.end_time, shipmentre0_.shipment_value_usd, shipmentre0_.shipment_value_base, shipmentre0_.insurance_value_usd, shipmentre0_.insurance_value_base, shipmentre0_.remarks, shipmentre0_.deletion_remarks, shipmentre0_.insurance_provider, shipmentre0_.shipment_provider, shipmentre0_.security_provider_id, shipmentre0_.consignee_contact_name, shipmentre0_.signal, shipmentre0_.chargeable_wt, shipmentre0_.no_of_pieces, shipmentre0_.regions_id, shipmentre0_.created, shipmentre0_.created_by, shipmentre0_.last_updated, shipmentre0_.last_updated_by, shipmentre0_.last_checked_by, shipmentre0_.last_maked, shipmentre0_.maker_checker_status, shipmentre0_.shadow_id, workflowst10_.workflow_module, workflowst10_.name, workflowst10_.deal_display_module, workflowst10_.workflow_level, workflowst10_.is_deal_editable, workflowst10_.gen_confo, workflowst10_.gen_deal_ticket, workflowst10_.gen_settlements, workflowst10_.vault_start, workflowst10_.update_main_inv, workflowst10_.update_other_inv, workflowst10_.release_shipment, workflowst10_.is_deal_splittable, workflowst10_.send_email, workflowst10_.is_deleted, workflowst10_.created, workflowst10_.created_by, workflowst10_.last_updated, workflowst10_.last_updated_by, workflowst10_.last_checked_by, workflowst10_.last_maked, workflowst10_.mod_id, workflowst10_.maker_checker_status, workflowst10_.shadow_id, carriers3_.mod_id, carriers3_.cities_id, carriers3_.code, carriers3_.name, carriers3_.carrier_types, carriers3_.name_in_fl, carriers3_.iata_code, carriers3_.kc_code, carriers3_.airline_acct, carriers3_.address1, carriers3_.address2, carriers3_.address3, carriers3_.address4, carriers3_.terminal, carriers3_.airline_agent, carriers3_.accountinginfo, carriers3_.import_dept, carriers3_.import_after_office_hour, carriers3_.import_contact, carriers3_.import_fax, carriers3_.import_email, carriers3_.export_deptt, carriers3_.export_after_office_hour, carriers3_.export_contact, carriers3_.export_fax, carriers3_.import_contact_no, carriers3_.export_contact_no, carriers3_.export_email, carriers3_.awb_issued_by, carriers3_.is_deleted, carriers3_.created, carriers3_.created_by, carriers3_.last_updated, carriers3_.last_updated_by, carriers3_.last_checked_by, carriers3_.last_maked, carriers3_.maker_checker_status, carriers3_.shadow_id, shipmentro1_.mod_id, shipmentro1_.region_id, shipmentro1_.airway_bill_no, shipmentro1_.shipment_date, shipmentro1_.arrival_date, shipmentro1_.leg_no, shipmentro1_.no_of_pcs, shipmentro1_.chargeable_weight, shipmentro1_.carrier_crew_extn_id, shipmentro1_.is_deleted, shipmentro1_.created, shipmentro1_.created_by, shipmentro1_.last_updated, shipmentro1_.last_updated_by, shipmentro1_.last_checked_by, shipmentro1_.last_maked, shipmentro1_.maker_checker_status, shipmentro1_.shadow_id, shipmentme11_.mod_id, shipmentme11_.code, shipmentme11_.name, shipmentme11_.shipment_method_type, shipmentme11_.is_deleted, shipmentme11_.created, shipmentme11_.created_by, shipmentme11_.last_updated, shipmentme11_.last_updated_by, shipmentme11_.last_checked_by, shipmentme11_.last_maked, shipmentme11_.maker_checker_status, shipmentme11_.shadow_id, workflowst9_.workflow_module, workflowst9_.name, workflowst9_.deal_display_module, workflowst9_.workflow_level, workflowst9_.is_deal_editable, workflowst9_.gen_confo, workflowst9_.gen_deal_ticket, workflowst9_.gen_settlements, workflowst9_.vault_start, workflowst9_.update_main_inv, workflowst9_.update_other_inv, workflowst9_.release_shipment, workflowst9_.is_deal_splittable, workflowst9_.send_email, workflowst9_.is_deleted, workflowst9_.created, workflowst9_.created_by, workflowst9_.last_updated, workflowst9_.last_updated_by, workflowst9_.last_checked_by, workflowst9_.last_maked, workflowst9_.mod_id, workflowst9_.maker_checker_status, workflowst9_.shadow_id, workflowst8_.workflow_module, workflowst8_.name, workflowst8_.deal_display_module, workflowst8_.workflow_level, workflowst8_.is_deal_editable, workflowst8_.gen_confo, workflowst8_.gen_deal_ticket, workflowst8_.gen_settlements, workflowst8_.vault_start, workflowst8_.update_main_inv, workflowst8_.update_other_inv, workflowst8_.release_shipment, workflowst8_.is_deal_splittable, workflowst8_.send_email, workflowst8_.is_deleted, workflowst8_.created, workflowst8_.created_by, workflowst8_.last_updated, workflowst8_.last_updated_by, workflowst8_.last_checked_by, workflowst8_.last_maked, workflowst8_.mod_id, workflowst8_.maker_checker_status, workflowst8_.shadow_id, workflowst7_.workflow_module, workflowst7_.name, workflowst7_.deal_display_module, workflowst7_.workflow_level, workflowst7_.is_deal_editable, workflowst7_.gen_confo, workflowst7_.gen_deal_ticket, workflowst7_.gen_settlements, workflowst7_.vault_start, workflowst7_.update_main_inv, workflowst7_.update_other_inv, workflowst7_.release_shipment, workflowst7_.is_deal_splittable, workflowst7_.send_email, workflowst7_.is_deleted, workflowst7_.created, workflowst7_.created_by, workflowst7_.last_updated, workflowst7_.last_updated_by, workflowst7_.last_checked_by, workflowst7_.last_maked, workflowst7_.mod_id, workflowst7_.maker_checker_status, workflowst7_.shadow_id, consignees5_.mod_id, consignees5_.countries_id, consignees5_.cities_id, consignees5_.regions_id, consignees5_.short_name, consignees5_.is_counterparty, consignees5_.name, consignees5_.airports_id, consignees5_.address1, consignees5_.address2, consignees5_.address3, consignees5_.address4, consignees5_.awb_special_clause, consignees5_.issuing_carrier_agent_name, consignees5_.agent_address1, consignees5_.agent_address2, consignees5_.postal_code, consignees5_.is_deleted, consignees5_.created, consignees5_.created_by, consignees5_.last_updated, consignees5_.last_updated_by, consignees5_.last_checked_by, consignees5_.last_maked, consignees5_.maker_checker_status, consignees5_.shadow_id, consignees6_.mod_id, consignees6_.countries_id, consignees6_.cities_id, consignees6_.regions_id, consignees6_.short_name, consignees6_.is_counterparty, consignees6_.name, consignees6_.airports_id, consignees6_.address1, consignees6_.address2, consignees6_.address3, consignees6_.address4, consignees6_.awb_special_clause, consignees6_.issuing_carrier_agent_name, consignees6_.agent_address1, consignees6_.agent_address2, consignees6_.postal_code, consignees6_.is_deleted, consignees6_.created, consignees6_.created_by, consignees6_.last_updated, consignees6_.last_updated_by, consignees6_.last_checked_by, consignees6_.last_maked, consignees6_.maker_checker_status, consignees6_.shadow_id, shipmentty4_.mod_id, shipmentty4_.code, shipmentty4_.name, shipmentty4_.regions_id, shipmentty4_.is_deleted, shipmentty4_.created, shipmentty4_.created_by, shipmentty4_.last_updated, shipmentty4_.last_updated_by, shipmentty4_.last_checked_by, shipmentty4_.last_maked, shipmentty4_.maker_checker_status, shipmentty4_.shadow_id, shipmentsc2_.mod_id, shipmentsc2_.origin_airports_id, shipmentsc2_.dest_airports_id, shipmentsc2_.schedule, shipmentsc2_.arrival_date, shipmentsc2_.est_time_departure, shipmentsc2_.est_time_arrival, shipmentsc2_.route_leg_seq_no, shipmentsc2_.cutoff_hours_before_departure, shipmentsc2_.available_in_a_week, shipmentsc2_.remarks, shipmentsc2_.status, shipmentsc2_.region_id, shipmentsc2_.is_deleted, shipmentsc2_.created, shipmentsc2_.created_by, shipmentsc2_.last_updated, shipmentsc2_.last_updated_by, shipmentsc2_.last_checked_by, shipmentsc2_.last_maked, shipmentsc2_.maker_checker_status, shipmentsc2_.shadow_id" " Sort Method: external merge Disk: 90656kB" " -> Hash Right Join (cost=388.61..5923.86 rows=59 width=6994) (actual time=143.405..372.903 rows=42759 loops=1)" " Hash Cond: ((deallegs12_.shipment_records_id)::text = (shipmentre0_.fin_id)::text)" " -> Seq Scan on tbls_bank_notes_deals_legs deallegs12_ (cost=0.00..5337.57 rows=52557 width=16) (actual time=0.005..26.702 rows=52557 loops=1)" " -> Hash (cost=388.58..388.58 rows=2 width=6960) (actual time=143.371..143.371 rows=1442 loops=1)" " Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3107kB" " -> Nested Loop Left Join (cost=106.73..388.58 rows=2 width=6960) (actual time=55.316..134.874 rows=1442 loops=1)" " Join Filter: ((shipmentre0_.shipment_method_id)::text = (shipmentme11_.fin_id)::text)" " Rows Removed by Join Filter: 2350" " -> Nested Loop (cost=106.73..387.37 rows=2 width=6721) (actual time=55.300..130.529 rows=1442 loops=1)" " -> Nested Loop (cost=106.59..387.03 rows=2 width=6582) (actual time=55.282..124.351 rows=1442 loops=1)" " -> Nested Loop (cost=106.45..386.69 rows=2 width=6443) (actual time=55.267..118.047 rows=1442 loops=1)" " -> Nested Loop (cost=106.31..386.36 rows=2 width=6304) (actual time=55.250..111.408 rows=1442 loops=1)" " -> Nested Loop (cost=106.17..386.02 rows=2 width=6165) (actual time=55.228..105.002 rows=1442 loops=1)" " Join Filter: ((shipmentre0_.consignees_id)::text = (consignees6_.fin_id)::text)" " Rows Removed by Join Filter: 40376" " -> Seq Scan on tbls_consignees consignees6_ (cost=0.00..1.29 rows=29 width=1060) (actual time=0.012..0.021 rows=29 loops=1)" " -> Materialize (cost=106.17..383.86 rows=2 width=5105) (actual time=1.904..3.142 rows=1442 loops=29)" " -> Nested Loop (cost=106.17..383.85 rows=2 width=5105) (actual time=55.203..78.206 rows=1442 loops=1)" " Join Filter: ((shipmentre0_.shipper_id)::text = (consignees5_.fin_id)::text)" " Rows Removed by Join Filter: 40376" " -> Seq Scan on tbls_consignees consignees5_ (cost=0.00..1.29 rows=29 width=1060) (actual time=0.003..0.013 rows=29 loops=1)" " -> Materialize (cost=106.17..381.70 rows=2 width=4045) (actual time=0.524..2.244 rows=1442 loops=29)" " -> Nested Loop (cost=106.17..381.69 rows=2 width=4045) (actual time=15.195..53.051 rows=1442 loops=1)" " Join Filter: ((shipmentre0_.shipment_type_id)::text = (shipmentty4_.fin_id)::text)" " Rows Removed by Join Filter: 7210" " -> Seq Scan on tbls_shipment_types shipmentty4_ (cost=0.00..1.06 rows=6 width=95) (actual time=0.002..0.005 rows=6 loops=1)" " -> Materialize (cost=106.17..380.45 rows=2 width=3950) (actual time=2.478..8.157 rows=1442 loops=6)" " -> Nested Loop (cost=106.17..380.44 rows=2 width=3950) (actual time=14.856..43.625 rows=1442 loops=1)" " -> Nested Loop (cost=106.03..379.95 rows=2 width=1696) (actual time=14.824..38.885 rows=1442 loops=1)" " -> Hash Join (cost=105.76..379.20 rows=2 width=1371) (actual time=14.807..32.459 rows=1442 loops=1)" " Hash Cond: (((shipmentro1_.shipment_record_id)::text = (shipmentre0_.fin_id)::text) AND (shipmentro1_.leg_no = (SubPlan 1)))" " -> Seq Scan on tbls_shipment_record_routing shipmentro1_ (cost=0.00..69.80 rows=484 width=444) (actual time=0.017..2.534 rows=1452 loops=1)" " Filter: (to_char(arrival_date, 'YYYY-MM-DD'::text) <= '2019-08-29'::text)" " Rows Removed by Filter: 1" " -> Hash (cost=84.11..84.11 rows=1443 width=927) (actual time=14.762..14.763 rows=1443 loops=1)" " Buckets: 2048 Batches: 1 Memory Usage: 497kB" " -> Seq Scan on tbls_shipment_records shipmentre0_ (cost=0.00..84.11 rows=1443 width=927) (actual time=0.005..1.039 rows=1443 loops=1)" " Filter: ((is_deleted)::text = 'N'::text)" " Rows Removed by Filter: 6" " SubPlan 1" " -> Aggregate (cost=8.30..8.31 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=2885)" " -> Index Scan using xbls_shipment_record_rout001 on tbls_shipment_record_routing shipmentro13_ (cost=0.28..8.30 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=2885)" " Index Cond: ((shipmentre0_.fin_id)::text = (shipment_record_id)::text)" " Filter: ((is_deleted)::text = 'N'::text)" " Rows Removed by Filter: 0" " -> Index Scan using pk_bls_shipment_schedules on tbls_shipment_schedules shipmentsc2_ (cost=0.27..0.38 rows=1 width=325) (actual time=0.003..0.003 rows=1 loops=1442)" " Index Cond: ((fin_id)::text = (shipmentro1_.shipment_schedule_id)::text)" " -> Index Scan using pk_bls_carriers on tbls_carriers carriers3_ (cost=0.14..0.24 rows=1 width=2254) (actual time=0.002..0.002 rows=1 loops=1442)" " Index Cond: ((fin_id)::text = (shipmentsc2_.carrier_id)::text)" " -> Index Scan using pk_bls_workflow_states on tbls_workflow_states workflowst7_ (cost=0.14..0.17 rows=1 width=139) (actual time=0.003..0.003 rows=1 loops=1442)" " Index Cond: ((fin_id)::text = (shipmentre0_.shipment_status_id)::text)" " -> Index Scan using pk_bls_workflow_states on tbls_workflow_states workflowst8_ (cost=0.14..0.17 rows=1 width=139) (actual time=0.003..0.003 rows=1 loops=1442)" " Index Cond: ((fin_id)::text = (shipmentre0_.shipment_charge_status)::text)" " -> Index Scan using pk_bls_workflow_states on tbls_workflow_states workflowst9_ (cost=0.14..0.17 rows=1 width=139) (actual time=0.002..0.002 rows=1 loops=1442)" " Index Cond: ((fin_id)::text = (shipmentre0_.shipment_document_status)::text)" " -> Index Scan using pk_bls_workflow_states on tbls_workflow_states workflowst10_ (cost=0.14..0.17 rows=1 width=139) (actual time=0.002..0.002 rows=1 loops=1442)" " Index Cond: ((fin_id)::text = (shipmentre0_.vault_status_id)::text)" " -> Materialize (cost=0.00..1.07 rows=5 width=239) (actual time=0.000..0.001 rows=3 loops=1442)" " -> Seq Scan on tbls_shipment_methods shipmentme11_ (cost=0.00..1.05 rows=5 width=239) (actual time=0.006..0.010 rows=5 loops=1)" "Planning time: 368.495 ms" "Execution time: 58018.486 ms" On Mon, Sep 9, 2019 at 3:30 PM Flo Rance <trourance@gmail.com> wrote: > > > On Mon, Sep 9, 2019 at 10:38 AM yash mehta <yash215@gmail.com> wrote: > >> In addition to below mail, we have used btree indexes for primary key >> columns. Below is the query: >> >> select distinct shipmentre0_.FIN_ID as >> FIN1_53_0_, >> workflowst10_.FIN_ID as FIN1_57_1_, >> carriers3_.FIN_ID as FIN1_40_2_, >> shipmentro1_.FIN_ID as FIN1_33_3_, >> shipmentme11_.FIN_ID as FIN1_5_4_, >> workflowst9_.FIN_ID as FIN1_57_5_, >> workflowst8_.FIN_ID as FIN1_57_6_, >> workflowst7_.FIN_ID as FIN1_57_7_, >> consignees5_.FIN_ID as FIN1_81_8_, >> consignees6_.FIN_ID as FIN1_81_9_, >> shipmentty4_.FIN_ID as FIN1_8_10_, >> shipmentsc2_.FIN_ID as FIN1_78_11_, >> shipmentre0_.MOD_ID as MOD2_53_0_, >> shipmentre0_.SHIPMENT_METHOD_ID as SHIPMENT3_53_0_, >> shipmentre0_.SHIPPER_ID as SHIPPER4_53_0_, >> shipmentre0_.CONSIGNEES_ID as CONSIGNEES5_53_0_, >> shipmentre0_.SHIPMENT_BASIS_ID as SHIPMENT6_53_0_, >> shipmentre0_.SHIPMENT_TYPE_ID as SHIPMENT7_53_0_, >> shipmentre0_.SHIPMENT_ARRANGEMENT_ID as SHIPMENT8_53_0_, >> shipmentre0_.SHIPMENT_DATE as SHIPMENT9_53_0_, >> shipmentre0_.SHIPMENT_CURRENCY_ID as SHIPMENT10_53_0_, >> shipmentre0_.CARRIER_CREW_EXTN_ID as CARRIER11_53_0_, >> shipmentre0_.END_TIME as END12_53_0_, >> shipmentre0_.SHIPMENT_VALUE_USD as SHIPMENT13_53_0_, >> shipmentre0_.SHIPMENT_VALUE_BASE as SHIPMENT14_53_0_, >> shipmentre0_.INSURANCE_VALUE_USD as INSURANCE15_53_0_, >> shipmentre0_.INSURANCE_VALUE_BASE as INSURANCE16_53_0_, >> shipmentre0_.REMARKS as REMARKS53_0_, >> shipmentre0_.DELETION_REMARKS as DELETION18_53_0_, >> shipmentre0_.SHIPMENT_STATUS_ID as SHIPMENT19_53_0_, >> shipmentre0_.VAULT_STATUS_ID as VAULT20_53_0_, >> shipmentre0_.SHIPMENT_CHARGE_STATUS as SHIPMENT21_53_0_, >> shipmentre0_.SHIPMENT_DOCUMENT_STATUS as SHIPMENT22_53_0_, >> shipmentre0_.INSURANCE_PROVIDER as INSURANCE23_53_0_, >> shipmentre0_.SHIPMENT_PROVIDER as SHIPMENT24_53_0_, >> shipmentre0_.SECURITY_PROVIDER_ID as SECURITY25_53_0_, >> shipmentre0_.CONSIGNEE_CONTACT_NAME as CONSIGNEE26_53_0_, >> shipmentre0_.SIGNAL as SIGNAL53_0_, >> shipmentre0_.CHARGEABLE_WT as CHARGEABLE28_53_0_, >> shipmentre0_.NO_OF_PIECES as NO29_53_0_, >> shipmentre0_.REGIONS_ID as REGIONS30_53_0_, >> shipmentre0_.IS_DELETED as IS31_53_0_, >> shipmentre0_.CREATED as CREATED53_0_, >> shipmentre0_.CREATED_BY as CREATED33_53_0_, >> shipmentre0_.LAST_UPDATED as LAST34_53_0_, >> shipmentre0_.LAST_UPDATED_BY as LAST35_53_0_, >> shipmentre0_.LAST_CHECKED_BY as LAST36_53_0_, >> shipmentre0_.LAST_MAKED as LAST37_53_0_, >> shipmentre0_.MAKER_CHECKER_STATUS as MAKER38_53_0_, >> shipmentre0_.SHADOW_ID as SHADOW39_53_0_, >> --(select now()) as formula48_0_, >> workflowst10_.WORKFLOW_MODULE as WORKFLOW2_57_1_, >> workflowst10_.NAME as NAME57_1_, >> workflowst10_.DEAL_DISPLAY_MODULE as DEAL4_57_1_, >> workflowst10_.WORKFLOW_LEVEL as WORKFLOW5_57_1_, >> workflowst10_.IS_DEAL_EDITABLE as IS6_57_1_, >> workflowst10_.GEN_CONFO as GEN7_57_1_, >> workflowst10_.GEN_DEAL_TICKET as GEN8_57_1_, >> workflowst10_.GEN_SETTLEMENTS as GEN9_57_1_, >> workflowst10_.VAULT_START as VAULT10_57_1_, >> workflowst10_.UPDATE_MAIN_INV as UPDATE11_57_1_, >> workflowst10_.UPDATE_OTHER_INV as UPDATE12_57_1_, >> workflowst10_.RELEASE_SHIPMENT as RELEASE13_57_1_, >> workflowst10_.IS_DEAL_SPLITTABLE as IS14_57_1_, >> workflowst10_.SEND_EMAIL as SEND15_57_1_, >> workflowst10_.IS_DELETED as IS16_57_1_, >> workflowst10_.CREATED as CREATED57_1_, >> workflowst10_.CREATED_BY as CREATED18_57_1_, >> workflowst10_.LAST_UPDATED as LAST19_57_1_, >> workflowst10_.LAST_UPDATED_BY as LAST20_57_1_, >> workflowst10_.LAST_CHECKED_BY as LAST21_57_1_, >> workflowst10_.LAST_MAKED as LAST22_57_1_, >> workflowst10_.MOD_ID as MOD23_57_1_, >> workflowst10_.MAKER_CHECKER_STATUS as MAKER24_57_1_, >> workflowst10_.SHADOW_ID as SHADOW25_57_1_, >> --(select now()) as formula52_1_, >> carriers3_.MOD_ID as MOD2_40_2_, >> carriers3_.CITIES_ID as CITIES3_40_2_, >> carriers3_.CODE as CODE40_2_, >> carriers3_.NAME as NAME40_2_, >> carriers3_.CARRIER_TYPES as CARRIER6_40_2_, >> carriers3_.NAME_IN_FL as NAME7_40_2_, >> carriers3_.IATA_CODE as IATA8_40_2_, >> carriers3_.KC_CODE as KC9_40_2_, >> carriers3_.AIRLINE_ACCT as AIRLINE10_40_2_, >> carriers3_.ADDRESS1 as ADDRESS11_40_2_, >> carriers3_.ADDRESS2 as ADDRESS12_40_2_, >> carriers3_.ADDRESS3 as ADDRESS13_40_2_, >> carriers3_.ADDRESS4 as ADDRESS14_40_2_, >> carriers3_.TERMINAL as TERMINAL40_2_, >> carriers3_.AIRLINE_AGENT as AIRLINE16_40_2_, >> carriers3_.ACCOUNTINGINFO as ACCOUNT17_40_2_, >> carriers3_.IMPORT_DEPT as IMPORT18_40_2_, >> carriers3_.IMPORT_AFTER_OFFICE_HOUR as IMPORT19_40_2_, >> carriers3_.IMPORT_CONTACT as IMPORT20_40_2_, >> carriers3_.IMPORT_FAX as IMPORT21_40_2_, >> carriers3_.IMPORT_EMAIL as IMPORT22_40_2_, >> carriers3_.EXPORT_DEPTT as EXPORT23_40_2_, >> carriers3_.EXPORT_AFTER_OFFICE_HOUR as EXPORT24_40_2_, >> carriers3_.EXPORT_CONTACT as EXPORT25_40_2_, >> carriers3_.EXPORT_FAX as EXPORT26_40_2_, >> carriers3_.IMPORT_CONTACT_NO as IMPORT27_40_2_, >> carriers3_.EXPORT_CONTACT_NO as EXPORT28_40_2_, >> carriers3_.EXPORT_EMAIL as EXPORT29_40_2_, >> carriers3_.AWB_ISSUED_BY as AWB30_40_2_, >> carriers3_.IS_DELETED as IS31_40_2_, >> carriers3_.CREATED as CREATED40_2_, >> carriers3_.CREATED_BY as CREATED33_40_2_, >> carriers3_.LAST_UPDATED as LAST34_40_2_, >> carriers3_.LAST_UPDATED_BY as LAST35_40_2_, >> carriers3_.LAST_CHECKED_BY as LAST36_40_2_, >> carriers3_.LAST_MAKED as LAST37_40_2_, >> carriers3_.MAKER_CHECKER_STATUS as MAKER38_40_2_, >> carriers3_.SHADOW_ID as SHADOW39_40_2_, >> --(select now()) as formula36_2_, >> shipmentro1_.MOD_ID as MOD2_33_3_, >> shipmentro1_.REGION_ID as REGION3_33_3_, >> shipmentro1_.SHIPMENT_SCHEDULE_ID as SHIPMENT4_33_3_, >> shipmentro1_.SHIPMENT_RECORD_ID as SHIPMENT5_33_3_, >> shipmentro1_.AIRWAY_BILL_NO as AIRWAY6_33_3_, >> shipmentro1_.SHIPMENT_DATE as SHIPMENT7_33_3_, >> shipmentro1_.ARRIVAL_DATE as ARRIVAL8_33_3_, >> shipmentro1_.LEG_NO as LEG9_33_3_, >> shipmentro1_.NO_OF_PCS as NO10_33_3_, >> shipmentro1_.CHARGEABLE_WEIGHT as CHARGEABLE11_33_3_, >> shipmentro1_.CARRIER_CREW_EXTN_ID as CARRIER12_33_3_, >> shipmentro1_.IS_DELETED as IS13_33_3_, >> shipmentro1_.CREATED as CREATED33_3_, >> shipmentro1_.CREATED_BY as CREATED15_33_3_, >> shipmentro1_.LAST_UPDATED as LAST16_33_3_, >> shipmentro1_.LAST_UPDATED_BY as LAST17_33_3_, >> shipmentro1_.LAST_CHECKED_BY as LAST18_33_3_, >> shipmentro1_.LAST_MAKED as LAST19_33_3_, >> shipmentro1_.MAKER_CHECKER_STATUS as MAKER20_33_3_, >> shipmentro1_.SHADOW_ID as SHADOW21_33_3_, >> --(select now()) as formula29_3_, >> shipmentme11_.MOD_ID as MOD2_5_4_, >> shipmentme11_.CODE as CODE5_4_, >> shipmentme11_.NAME as NAME5_4_, >> shipmentme11_.SHIPMENT_METHOD_TYPE as SHIPMENT5_5_4_, >> shipmentme11_.IS_DELETED as IS6_5_4_, >> shipmentme11_.CREATED as CREATED5_4_, >> shipmentme11_.CREATED_BY as CREATED8_5_4_, >> shipmentme11_.LAST_UPDATED as LAST9_5_4_, >> shipmentme11_.LAST_UPDATED_BY as LAST10_5_4_, >> shipmentme11_.LAST_CHECKED_BY as LAST11_5_4_, >> shipmentme11_.LAST_MAKED as LAST12_5_4_, >> shipmentme11_.MAKER_CHECKER_STATUS as MAKER13_5_4_, >> shipmentme11_.SHADOW_ID as SHADOW14_5_4_, >> --(select now()) as formula4_4_, >> workflowst9_.WORKFLOW_MODULE as WORKFLOW2_57_5_, >> workflowst9_.NAME as NAME57_5_, >> workflowst9_.DEAL_DISPLAY_MODULE as DEAL4_57_5_, >> workflowst9_.WORKFLOW_LEVEL as WORKFLOW5_57_5_, >> workflowst9_.IS_DEAL_EDITABLE as IS6_57_5_, >> workflowst9_.GEN_CONFO as GEN7_57_5_, >> workflowst9_.GEN_DEAL_TICKET as GEN8_57_5_, >> workflowst9_.GEN_SETTLEMENTS as GEN9_57_5_, >> workflowst9_.VAULT_START as VAULT10_57_5_, >> workflowst9_.UPDATE_MAIN_INV as UPDATE11_57_5_, >> workflowst9_.UPDATE_OTHER_INV as UPDATE12_57_5_, >> workflowst9_.RELEASE_SHIPMENT as RELEASE13_57_5_, >> workflowst9_.IS_DEAL_SPLITTABLE as IS14_57_5_, >> workflowst9_.SEND_EMAIL as SEND15_57_5_, >> workflowst9_.IS_DELETED as IS16_57_5_, >> workflowst9_.CREATED as CREATED57_5_, >> workflowst9_.CREATED_BY as CREATED18_57_5_, >> workflowst9_.LAST_UPDATED as LAST19_57_5_, >> workflowst9_.LAST_UPDATED_BY as LAST20_57_5_, >> workflowst9_.LAST_CHECKED_BY as LAST21_57_5_, >> workflowst9_.LAST_MAKED as LAST22_57_5_, >> workflowst9_.MOD_ID as MOD23_57_5_, >> workflowst9_.MAKER_CHECKER_STATUS as MAKER24_57_5_, >> workflowst9_.SHADOW_ID as SHADOW25_57_5_, >> --(select now()) as formula52_5_, >> workflowst8_.WORKFLOW_MODULE as WORKFLOW2_57_6_, >> workflowst8_.NAME as NAME57_6_, >> workflowst8_.DEAL_DISPLAY_MODULE as DEAL4_57_6_, >> workflowst8_.WORKFLOW_LEVEL as WORKFLOW5_57_6_, >> workflowst8_.IS_DEAL_EDITABLE as IS6_57_6_, >> workflowst8_.GEN_CONFO as GEN7_57_6_, >> workflowst8_.GEN_DEAL_TICKET as GEN8_57_6_, >> workflowst8_.GEN_SETTLEMENTS as GEN9_57_6_, >> workflowst8_.VAULT_START as VAULT10_57_6_, >> workflowst8_.UPDATE_MAIN_INV as UPDATE11_57_6_, >> workflowst8_.UPDATE_OTHER_INV as UPDATE12_57_6_, >> workflowst8_.RELEASE_SHIPMENT as RELEASE13_57_6_, >> workflowst8_.IS_DEAL_SPLITTABLE as IS14_57_6_, >> workflowst8_.SEND_EMAIL as SEND15_57_6_, >> workflowst8_.IS_DELETED as IS16_57_6_, >> workflowst8_.CREATED as CREATED57_6_, >> workflowst8_.CREATED_BY as CREATED18_57_6_, >> workflowst8_.LAST_UPDATED as LAST19_57_6_, >> workflowst8_.LAST_UPDATED_BY as LAST20_57_6_, >> workflowst8_.LAST_CHECKED_BY as LAST21_57_6_, >> workflowst8_.LAST_MAKED as LAST22_57_6_, >> workflowst8_.MOD_ID as MOD23_57_6_, >> workflowst8_.MAKER_CHECKER_STATUS as MAKER24_57_6_, >> workflowst8_.SHADOW_ID as SHADOW25_57_6_, >> --(select now()) as formula52_6_, >> workflowst7_.WORKFLOW_MODULE as WORKFLOW2_57_7_, >> workflowst7_.NAME as NAME57_7_, >> workflowst7_.DEAL_DISPLAY_MODULE as DEAL4_57_7_, >> workflowst7_.WORKFLOW_LEVEL as WORKFLOW5_57_7_, >> workflowst7_.IS_DEAL_EDITABLE as IS6_57_7_, >> workflowst7_.GEN_CONFO as GEN7_57_7_, >> workflowst7_.GEN_DEAL_TICKET as GEN8_57_7_, >> workflowst7_.GEN_SETTLEMENTS as GEN9_57_7_, >> workflowst7_.VAULT_START as VAULT10_57_7_, >> workflowst7_.UPDATE_MAIN_INV as UPDATE11_57_7_, >> workflowst7_.UPDATE_OTHER_INV as UPDATE12_57_7_, >> workflowst7_.RELEASE_SHIPMENT as RELEASE13_57_7_, >> workflowst7_.IS_DEAL_SPLITTABLE as IS14_57_7_, >> workflowst7_.SEND_EMAIL as SEND15_57_7_, >> workflowst7_.IS_DELETED as IS16_57_7_, >> workflowst7_.CREATED as CREATED57_7_, >> workflowst7_.CREATED_BY as CREATED18_57_7_, >> workflowst7_.LAST_UPDATED as LAST19_57_7_, >> workflowst7_.LAST_UPDATED_BY as LAST20_57_7_, >> workflowst7_.LAST_CHECKED_BY as LAST21_57_7_, >> workflowst7_.LAST_MAKED as LAST22_57_7_, >> workflowst7_.MOD_ID as MOD23_57_7_, >> workflowst7_.MAKER_CHECKER_STATUS as MAKER24_57_7_, >> workflowst7_.SHADOW_ID as SHADOW25_57_7_, >> --(select now()) as formula52_7_, >> consignees5_.MOD_ID as MOD2_81_8_, >> consignees5_.COUNTRIES_ID as COUNTRIES3_81_8_, >> consignees5_.CITIES_ID as CITIES4_81_8_, >> consignees5_.REGIONS_ID as REGIONS5_81_8_, >> consignees5_.SHORT_NAME as SHORT6_81_8_, >> consignees5_.IS_COUNTERPARTY as IS7_81_8_, >> consignees5_.NAME as NAME81_8_, >> consignees5_.AIRPORTS_ID as AIRPORTS9_81_8_, >> consignees5_.ADDRESS1 as ADDRESS10_81_8_, >> consignees5_.ADDRESS2 as ADDRESS11_81_8_, >> consignees5_.ADDRESS3 as ADDRESS12_81_8_, >> consignees5_.ADDRESS4 as ADDRESS13_81_8_, >> consignees5_.AWB_SPECIAL_CLAUSE as AWB14_81_8_, >> consignees5_.ISSUING_CARRIER_AGENT_NAME as ISSUING15_81_8_, >> consignees5_.AGENT_ADDRESS1 as AGENT16_81_8_, >> consignees5_.AGENT_ADDRESS2 as AGENT17_81_8_, >> consignees5_.POSTAL_CODE as POSTAL18_81_8_, >> consignees5_.IS_DELETED as IS19_81_8_, >> consignees5_.CREATED as CREATED81_8_, >> consignees5_.CREATED_BY as CREATED21_81_8_, >> consignees5_.LAST_UPDATED as LAST22_81_8_, >> consignees5_.LAST_UPDATED_BY as LAST23_81_8_, >> consignees5_.LAST_CHECKED_BY as LAST24_81_8_, >> consignees5_.LAST_MAKED as LAST25_81_8_, >> consignees5_.MAKER_CHECKER_STATUS as MAKER26_81_8_, >> consignees5_.SHADOW_ID as SHADOW27_81_8_, >> --(select now()) as formula74_8_, >> consignees6_.MOD_ID as MOD2_81_9_, >> consignees6_.COUNTRIES_ID as COUNTRIES3_81_9_, >> consignees6_.CITIES_ID as CITIES4_81_9_, >> consignees6_.REGIONS_ID as REGIONS5_81_9_, >> consignees6_.SHORT_NAME as SHORT6_81_9_, >> consignees6_.IS_COUNTERPARTY as IS7_81_9_, >> consignees6_.NAME as NAME81_9_, >> consignees6_.AIRPORTS_ID as AIRPORTS9_81_9_, >> consignees6_.ADDRESS1 as ADDRESS10_81_9_, >> consignees6_.ADDRESS2 as ADDRESS11_81_9_, >> consignees6_.ADDRESS3 as ADDRESS12_81_9_, >> consignees6_.ADDRESS4 as ADDRESS13_81_9_, >> consignees6_.AWB_SPECIAL_CLAUSE as AWB14_81_9_, >> consignees6_.ISSUING_CARRIER_AGENT_NAME as ISSUING15_81_9_, >> consignees6_.AGENT_ADDRESS1 as AGENT16_81_9_, >> consignees6_.AGENT_ADDRESS2 as AGENT17_81_9_, >> consignees6_.POSTAL_CODE as POSTAL18_81_9_, >> consignees6_.IS_DELETED as IS19_81_9_, >> consignees6_.CREATED as CREATED81_9_, >> consignees6_.CREATED_BY as CREATED21_81_9_, >> consignees6_.LAST_UPDATED as LAST22_81_9_, >> consignees6_.LAST_UPDATED_BY as LAST23_81_9_, >> consignees6_.LAST_CHECKED_BY as LAST24_81_9_, >> consignees6_.LAST_MAKED as LAST25_81_9_, >> consignees6_.MAKER_CHECKER_STATUS as MAKER26_81_9_, >> consignees6_.SHADOW_ID as SHADOW27_81_9_, >> --(select now()) as formula74_9_, >> shipmentty4_.MOD_ID as MOD2_8_10_, >> shipmentty4_.CODE as CODE8_10_, >> shipmentty4_.NAME as NAME8_10_, >> shipmentty4_.REGIONS_ID as REGIONS5_8_10_, >> shipmentty4_.IS_DELETED as IS6_8_10_, >> shipmentty4_.CREATED as CREATED8_10_, >> shipmentty4_.CREATED_BY as CREATED8_8_10_, >> shipmentty4_.LAST_UPDATED as LAST9_8_10_, >> shipmentty4_.LAST_UPDATED_BY as LAST10_8_10_, >> shipmentty4_.LAST_CHECKED_BY as LAST11_8_10_, >> shipmentty4_.LAST_MAKED as LAST12_8_10_, >> shipmentty4_.MAKER_CHECKER_STATUS as MAKER13_8_10_, >> shipmentty4_.SHADOW_ID as SHADOW14_8_10_, >> --(select now()) as formula6_10_, >> shipmentsc2_.MOD_ID as MOD2_78_11_, >> shipmentsc2_.CARRIER_ID as CARRIER3_78_11_, >> shipmentsc2_.ORIGIN_AIRPORTS_ID as ORIGIN4_78_11_, >> shipmentsc2_.DEST_AIRPORTS_ID as DEST5_78_11_, >> shipmentsc2_.SCHEDULE as SCHEDULE78_11_, >> shipmentsc2_.ARRIVAL_DATE as ARRIVAL7_78_11_, >> shipmentsc2_.EST_TIME_DEPARTURE as EST8_78_11_, >> shipmentsc2_.EST_TIME_ARRIVAL as EST9_78_11_, >> shipmentsc2_.ROUTE_LEG_SEQ_NO as ROUTE10_78_11_, >> shipmentsc2_.CUTOFF_HOURS_BEFORE_DEPARTURE as CUTOFF11_78_11_, >> shipmentsc2_.AVAILABLE_IN_A_WEEK as AVAILABLE12_78_11_, >> shipmentsc2_.REMARKS as REMARKS78_11_, >> shipmentsc2_.STATUS as STATUS78_11_, >> shipmentsc2_.REGION_ID as REGION15_78_11_, >> shipmentsc2_.IS_DELETED as IS16_78_11_, >> shipmentsc2_.CREATED as CREATED78_11_, >> shipmentsc2_.CREATED_BY as CREATED18_78_11_, >> shipmentsc2_.LAST_UPDATED as LAST19_78_11_, >> shipmentsc2_.LAST_UPDATED_BY as LAST20_78_11_, >> shipmentsc2_.LAST_CHECKED_BY as LAST21_78_11_, >> shipmentsc2_.LAST_MAKED as LAST22_78_11_, >> shipmentsc2_.MAKER_CHECKER_STATUS as MAKER23_78_11_, >> shipmentsc2_.SHADOW_ID as SHADOW24_78_11_, >> --(select now()) as formula71_11_, >> shipmentro1_.SHIPMENT_RECORD_ID as SHIPMENT5___, >> shipmentro1_.FIN_ID as FIN1___ >> from TBLS_SHIPMENT_RECORDS shipmentre0_ >> inner join TBLS_SHIPMENT_RECORD_ROUTING shipmentro1_ on >> shipmentre0_.FIN_ID = shipmentro1_.SHIPMENT_RECORD_ID >> inner join TBLS_SHIPMENT_SCHEDULES shipmentsc2_ on >> shipmentro1_.SHIPMENT_SCHEDULE_ID = shipmentsc2_.FIN_ID >> inner join TBLS_CARRIERS carriers3_ on shipmentsc2_.CARRIER_ID = >> carriers3_.FIN_ID >> inner join TBLS_SHIPMENT_TYPES shipmentty4_ on >> shipmentre0_.SHIPMENT_TYPE_ID = shipmentty4_.FIN_ID >> inner join TBLS_CONSIGNEES consignees5_ on shipmentre0_.SHIPPER_ID = >> consignees5_.FIN_ID >> inner join TBLS_CONSIGNEES consignees6_ on shipmentre0_.CONSIGNEES_ID = >> consignees6_.FIN_ID >> inner join TBLS_WORKFLOW_STATES workflowst7_ on >> shipmentre0_.SHIPMENT_STATUS_ID = workflowst7_.FIN_ID >> inner join TBLS_WORKFLOW_STATES workflowst8_ on >> shipmentre0_.SHIPMENT_CHARGE_STATUS = workflowst8_.FIN_ID >> inner join TBLS_WORKFLOW_STATES workflowst9_ on >> shipmentre0_.SHIPMENT_DOCUMENT_STATUS = workflowst9_.FIN_ID >> inner join TBLS_WORKFLOW_STATES workflowst10_ on >> shipmentre0_.VAULT_STATUS_ID = workflowst10_.FIN_ID >> left outer join TBLS_SHIPMENT_METHODS shipmentme11_ on >> shipmentre0_.SHIPMENT_METHOD_ID = shipmentme11_.FIN_ID >> left outer join TBLS_BANK_NOTES_DEALS_LEGS deallegs12_ on >> shipmentre0_.FIN_ID = deallegs12_.SHIPMENT_RECORDS_ID >> where (shipmentro1_.LEG_NO = (select min(shipmentro13_.LEG_NO) >> from TBLS_SHIPMENT_RECORD_ROUTING shipmentro13_ >> where shipmentre0_.FIN_ID = shipmentro13_.SHIPMENT_RECORD_ID >> and ((shipmentro13_.IS_DELETED = 'N')))) >> and (shipmentre0_.IS_DELETED = 'N') >> and (TO_CHAR(shipmentro1_.ARRIVAL_DATE, 'YYYY-MM-DD') <= '2019-08-29') >> order by shipmentre0_.SHIPMENT_DATE >> limit 25 >> ; >> >> >> On Mon, Sep 9, 2019 at 2:00 PM yash mehta <yash215@gmail.com> wrote: >> >>> We have a query that takes 1min to execute in postgres 10.6 and the same >>> executes in 4 sec in Oracle database. The query is doing 'select distinct'. >>> If I add a 'group by' clause, performance in postgres improves >>> significantly and fetches results in 2 sec (better than oracle). But >>> unfortunately, we cannot modify the query. Could you please suggest a way >>> to improve performance in Postgres without modifying the query. >>> >>> *Original condition: time taken 1min* >>> >>> Sort Method: external merge Disk: 90656kB >>> >>> >>> >>> *After removing distinct from query: time taken 2sec* >>> >>> Sort Method: top-N heapsort Memory: 201kB >>> >>> >>> >>> *After increasing work_mem to 180MB; it takes 20sec* >>> >>> Sort Method: quicksort Memory: 172409kB >>> >>> >>> >>> SELECT * FROM pg_stat_statements ORDER BY total_time DESC limit 1; >>> >>> -[ RECORD 1 >>> ]-------+----------------------------------------------------------------------------------------------------------------------------------------- >>> >>> userid | 174862 >>> >>> dbid | 174861 >>> >>> queryid | 1469376470 >>> >>> query | <query is too long. It selects around 300 columns> >>> >>> calls | 1 >>> >>> total_time | 59469.972661 >>> >>> min_time | 59469.972661 >>> >>> max_time | 59469.972661 >>> >>> mean_time | 59469.972661 >>> >>> stddev_time | 0 >>> >>> rows | 25 >>> >>> shared_blks_hit | 27436 >>> >>> shared_blks_read | 2542 >>> >>> shared_blks_dirtied | 0 >>> >>> shared_blks_written | 0 >>> >>> local_blks_hit | 0 >>> >>> local_blks_read | 0 >>> >>> local_blks_dirtied | 0 >>> >>> local_blks_written | 0 >>> >>> temp_blks_read | 257 >>> >>> temp_blks_written | 11333 >>> >>> blk_read_time | 0 >>> >>> blk_write_time | 0 >>> >> > IMO, an explain analyze of the query would be useful in order for people > to help you. > > e.g. https://explain.depesz.com > > Regards, > Flo > -
Re: select distinct runs slow on pg 10.6
Justin Pryzby <pryzby@telsasoft.com> — 2019-09-09T10:39:30Z
On Mon, Sep 09, 2019 at 02:00:01PM +0530, yash mehta wrote: > We have a query that takes 1min to execute in postgres 10.6 and the same > executes in 4 sec in Oracle database. The query is doing 'select distinct'. > If I add a 'group by' clause, performance in postgres improves > significantly and fetches results in 2 sec (better than oracle). But > unfortunately, we cannot modify the query. Could you please suggest a way > to improve performance in Postgres without modifying the query. Not sure it helps, but I remember this: https://www.postgresql.org/message-id/CAKJS1f9q0j3BgMUsDbtf9%3DecfVLnqvkYB44MXj0gpVuamcN8Xw%40mail.gmail.com
-
Re: select distinct runs slow on pg 10.6
Flo Rance <trourance@gmail.com> — 2019-09-09T13:16:10Z
There are few things to consider: - you don't need to use distinct on all columns (and therefore sort all columns) - you should try to sort in memory, better than on-disk - it seems that the planner doesn't predict the good number of rows Regards, Florian On Mon, Sep 9, 2019 at 12:46 PM Justin Pryzby <pryzby@telsasoft.com> wrote: > On Mon, Sep 09, 2019 at 02:00:01PM +0530, yash mehta wrote: > > We have a query that takes 1min to execute in postgres 10.6 and the same > > executes in 4 sec in Oracle database. The query is doing 'select > distinct'. > > If I add a 'group by' clause, performance in postgres improves > > significantly and fetches results in 2 sec (better than oracle). But > > unfortunately, we cannot modify the query. Could you please suggest a way > > to improve performance in Postgres without modifying the query. > > Not sure it helps, but I remember this: > > https://www.postgresql.org/message-id/CAKJS1f9q0j3BgMUsDbtf9%3DecfVLnqvkYB44MXj0gpVuamcN8Xw%40mail.gmail.com > > >
-
Re: select distinct runs slow on pg 10.6
Michael Lewis <mlewis@entrata.com> — 2019-09-09T15:39:27Z
If you can't modify the query, then there is nothing more to be done to optimize the execution afaik. Distinct is much slower than group by in scenarios like this with many columns. You already identified the disk sort and increased work mem to get it faster by 3x. There are not any other tricks of which I am aware.
-
Re: select distinct runs slow on pg 10.6
yash mehta <yash215@gmail.com> — 2019-09-10T04:53:01Z
Hi Michael/Justin/Flo, Thank you all for your assistance. As Michael said, looks like there are no more tricks left. On Mon, Sep 9, 2019 at 9:09 PM Michael Lewis <mlewis@entrata.com> wrote: > If you can't modify the query, then there is nothing more to be done to > optimize the execution afaik. Distinct is much slower than group by in > scenarios like this with many columns. You already identified the disk sort > and increased work mem to get it faster by 3x. There are not any other > tricks of which I am aware. >
-
Re: select distinct runs slow on pg 10.6
Rick Otten <rottenwindfish@gmail.com> — 2019-09-10T11:56:54Z
On Tue, Sep 10, 2019 at 12:53 AM yash mehta <yash215@gmail.com> wrote: > Hi Michael/Justin/Flo, > > Thank you all for your assistance. As Michael said, looks like there are > no more tricks left. > > On Mon, Sep 9, 2019 at 9:09 PM Michael Lewis <mlewis@entrata.com> wrote: > >> If you can't modify the query, then there is nothing more to be done to >> optimize the execution afaik. Distinct is much slower than group by in >> scenarios like this with many columns. You already identified the disk sort >> and increased work mem to get it faster by 3x. There are not any other >> tricks of which I am aware. >> > Could you put a view in between the real table and the query that does the group by ? (since you can't change the query) I'm wondering if the sort/processing time would be faster when that distinct is invoked if the rows are already distinct.
-
Re: select distinct runs slow on pg 10.6
Merlin Moncure <mmoncure@gmail.com> — 2019-09-11T15:54:01Z
On Mon, Sep 9, 2019 at 3:55 AM yash mehta <yash215@gmail.com> wrote: > > We have a query that takes 1min to execute in postgres 10.6 and the same executes in 4 sec in Oracle database. The query is doing 'select distinct'. If I add a 'group by' clause, performance in postgres improves significantly and fetches results in 2 sec (better than oracle). But unfortunately, we cannot modify the query. Could you please suggest a way to improve performance in Postgres without modifying the query. Well, here's the bad news. Postgres doesn't optimize this specific formulation as well as oracle does. Normally tweaking the query along with some creativity would get the expected result; it's pretty rare that I can't coerce the planner to do something fairly optimally. I'm guessing this is an Oracle conversion app, and we do not have the ability to change the underlying source code? Can you elaborate why not? In lieu of changing the query in the application, we have high level strategies to consider. *) Eat the 20 seconds, and gripe to your oracle buddies (they will appreciate this) *) Mess around with with planner variables to get a better plan. Unfortunately, since we can't do tricks like SET before running the query, the changes will be global, and I'm not expecting this to bear fruit, unless we can have this query be separated from other queries at the connection level (we might be able to intervene on connect and set influential non-global planner settings there) *) Experiment with pg11/pg12 to see if upcoming versions can handle this strategy better. pg12 is in beta obviously, but an upgrade strategy would be the easiest out. *) Attempt to intervene with views. I think this is out, since all the tables are schema qualified. To avoid a global change, the typical strategy is to tuck some views into a private schema and manipulate search_path to have them resolve first, but that won't work if you don't have control of the query string. *) Try to change the query string anyways. Say, this is a compiled application for which you don't have the code, we might be able to locate the query text within the compiled binary and modify it. This is actually a pretty effective trick (although in many scenarios we'd want the query string to be the same length as before but you have plenty of whitespace to play with) although in certain legal/regulatory contexts we might not be able to do it. *) Hack some C to adjust the query in flight. This is *SUPER* hacky, but let's say that the application was dynamically linked against the libpq driver, but with some C change and a fearless attitude we could adjust the query after it leaves the application but before it hits the database. Other candidate interventions might be in the database itself or in pgbouncer. We could also do this in jdbc if your application connects via that driver. This is would be 'absolutely last resort' tactics, but sometimes you simply must find a solution. merlin
-
Re: select distinct runs slow on pg 10.6
Dinesh Somani <dinesh@opsveda.com> — 2019-09-11T16:38:19Z
I think Merlin has outlined pretty much all the options and very neatly. (As an asides Merlin could you possibly elaborate on the "C Hack" how that might be accomplished.) To OP, I am curious if the performance changes were the query rewritten such that all timestamp columns were listed first in the selection. I understand it might not be feasible to make this change in your real application without breaking the contract. Regards Dinesh On Wed, Sep 11, 2019 at 8:54 AM Merlin Moncure <mmoncure@gmail.com> wrote: > On Mon, Sep 9, 2019 at 3:55 AM yash mehta <yash215@gmail.com> wrote: > > > > We have a query that takes 1min to execute in postgres 10.6 and the same > executes in 4 sec in Oracle database. The query is doing 'select distinct'. > If I add a 'group by' clause, performance in postgres improves > significantly and fetches results in 2 sec (better than oracle). But > unfortunately, we cannot modify the query. Could you please suggest a way > to improve performance in Postgres without modifying the query. > > Well, here's the bad news. Postgres doesn't optimize this specific > formulation as well as oracle does. Normally tweaking the query along > with some creativity would get the expected result; it's pretty rare > that I can't coerce the planner to do something fairly optimally. I'm > guessing this is an Oracle conversion app, and we do not have the > ability to change the underlying source code? Can you elaborate why > not? > > In lieu of changing the query in the application, we have high level > strategies to consider. > *) Eat the 20 seconds, and gripe to your oracle buddies (they will > appreciate this) > > *) Mess around with with planner variables to get a better plan. > Unfortunately, since we can't do tricks like SET before running the > query, the changes will be global, and I'm not expecting this to bear > fruit, unless we can have this query be separated from other queries > at the connection level (we might be able to intervene on connect and > set influential non-global planner settings there) > > *) Experiment with pg11/pg12 to see if upcoming versions can handle > this strategy better. pg12 is in beta obviously, but an upgrade > strategy would be the easiest out. > > *) Attempt to intervene with views. I think this is out, since all > the tables are schema qualified. To avoid a global change, the typical > strategy is to tuck some views into a private schema and manipulate > search_path to have them resolve first, but that won't work if you > don't have control of the query string. > > *) Try to change the query string anyways. Say, this is a compiled > application for which you don't have the code, we might be able to > locate the query text within the compiled binary and modify it. This > is actually a pretty effective trick (although in many scenarios we'd > want the query string to be the same length as before but you have > plenty of whitespace to play with) although in certain > legal/regulatory contexts we might not be able to do it. > > *) Hack some C to adjust the query in flight. This is *SUPER* hacky, > but let's say that the application was dynamically linked against the > libpq driver, but with some C change and a fearless attitude we could > adjust the query after it leaves the application but before it hits > the database. Other candidate interventions might be in the database > itself or in pgbouncer. We could also do this in jdbc if your > application connects via that driver. This is would be 'absolutely > last resort' tactics, but sometimes you simply must find a solution. > > merlin > > >
-
Re: select distinct runs slow on pg 10.6
Rick Otten <rottenwindfish@gmail.com> — 2019-09-11T17:57:25Z
On Wed, Sep 11, 2019 at 12:38 PM Dinesh Somani <dinesh@opsveda.com> wrote: > I think Merlin has outlined pretty much all the options and very neatly. > (As an asides Merlin could you possibly elaborate on the "C Hack" how that > might be accomplished.) > > To OP, I am curious if the performance changes were the query rewritten > such that all timestamp columns were listed first in the selection. I > understand it might not be feasible to make this change in your real > application without breaking the contract. > > Regards > Dinesh > It looks like AWS has a pgbouncer query re-writer service that might be a starting point: https://aws.amazon.com/blogs/big-data/query-routing-and-rewrite-introducing-pgbouncer-rr-for-amazon-redshift-and-postgresql/ I've never used it.
-
Re: select distinct runs slow on pg 10.6
Merlin Moncure <mmoncure@gmail.com> — 2019-09-12T14:19:11Z
On Wed, Sep 11, 2019 at 12:57 PM Rick Otten <rottenwindfish@gmail.com> wrote: > > On Wed, Sep 11, 2019 at 12:38 PM Dinesh Somani <dinesh@opsveda.com> wrote: >> >> I think Merlin has outlined pretty much all the options and very neatly. (As an asides Merlin could you possibly elaborate on the "C Hack" how that might be accomplished.) >> >> To OP, I am curious if the performance changes were the query rewritten such that all timestamp columns were listed first in the selection. I understand it might not be feasible to make this change in your real application without breaking the contract. >> >> Regards >> Dinesh > > > It looks like AWS has a pgbouncer query re-writer service that might be a starting point: > https://aws.amazon.com/blogs/big-data/query-routing-and-rewrite-introducing-pgbouncer-rr-for-amazon-redshift-and-postgresql/ > > I've never used it. Yeah, I haven't either. Side note: this system also provides the ability to load balance queries across distributed system; that's a huge benefit. Say you have master server and five replica, it seems that you can round robin the read only queries using this system or other neat little tricks. I would be cautious about pgbouncer-rr becoming the bottleneck itself for certain workloads though. Anyways, a 'hack' strategy on linux might be to: *) Check and verify that libpq is dynamically linked (which is almost alwasys the case). ldd /your/application should give the dynamic library dependency to libpq. *) Grab postgres sources for same version as production *) configure *) switch to interfaces/libpq *) figure out which interface routine(s) being called into. The approach will be slightly different if the query is prepared/paramterized or not. Assuming it isn't, you'd have to modify the PQsendQuery routine to check for the signature (say, with strcmp), create a new string, and have that be put instead of the incoming const char* query. The parameterized versions (PQsendQueryParams) would be easier since you'd be able to use a static string rather than parsing it out. *) Build the library, do some testing with hand written C program *) inject the modified libpq with LD_LIBRARY_PATH It must be stated that some people might read this and be compelled to barf :-) -- it's pretty gross. Having said that, sometimes you have to find a solution. I would definitely try the pgbouncer-rr approach first however; this has a *lot* of potential benefit. merlin
-
Re: select distinct runs slow on pg 10.6
Dinesh Somani <dinesh@opsveda.com> — 2019-09-12T16:25:39Z
Thanks a lot, Merlin. Yes, it could appear kinda gross to some ;-) On Thu, Sep 12, 2019 at 7:19 AM Merlin Moncure <mmoncure@gmail.com> wrote: > On Wed, Sep 11, 2019 at 12:57 PM Rick Otten <rottenwindfish@gmail.com> > wrote: > > > > On Wed, Sep 11, 2019 at 12:38 PM Dinesh Somani <dinesh@opsveda.com> > wrote: > >> > >> I think Merlin has outlined pretty much all the options and very > neatly. (As an asides Merlin could you possibly elaborate on the "C Hack" > how that might be accomplished.) > >> > >> To OP, I am curious if the performance changes were the query rewritten > such that all timestamp columns were listed first in the selection. I > understand it might not be feasible to make this change in your real > application without breaking the contract. > >> > >> Regards > >> Dinesh > > > > > > It looks like AWS has a pgbouncer query re-writer service that might be > a starting point: > > > https://aws.amazon.com/blogs/big-data/query-routing-and-rewrite-introducing-pgbouncer-rr-for-amazon-redshift-and-postgresql/ > > > > I've never used it. > > Yeah, I haven't either. Side note: this system also provides the > ability to load balance queries across distributed system; that's a > huge benefit. Say you have master server and five replica, it seems > that you can round robin the read only queries using this system or > other neat little tricks. I would be cautious about pgbouncer-rr > becoming the bottleneck itself for certain workloads though. > > Anyways, a 'hack' strategy on linux might be to: > *) Check and verify that libpq is dynamically linked (which is almost > alwasys the case). ldd /your/application should give the dynamic > library dependency to libpq. > *) Grab postgres sources for same version as production > *) configure > *) switch to interfaces/libpq > *) figure out which interface routine(s) being called into. The > approach will be slightly different if the query is > prepared/paramterized or not. Assuming it isn't, you'd have to modify > the PQsendQuery routine to check for the signature (say, with > strcmp), create a new string, and have that be put instead of the > incoming const char* query. The parameterized versions > (PQsendQueryParams) would be easier since you'd be able to use a > static string rather than parsing it out. > *) Build the library, do some testing with hand written C program > *) inject the modified libpq with LD_LIBRARY_PATH > > It must be stated that some people might read this and be compelled to > barf :-) -- it's pretty gross. Having said that, sometimes you have to > find a solution. I would definitely try the pgbouncer-rr approach > first however; this has a *lot* of potential benefit. > > merlin > > >