Re: select distinct runs slow on pg 10.6

On Mon, Sep 9, 2019 at 10:38 AM yash mehta <yash215(at)gmail(dot)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(at)gmail(dot)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