Re: select distinct runs slow on pg 10.6 - Mailing list pgsql-performance

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