select distinct runs slow on pg 10.6

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

select distinct runs slow on pg 10.6

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

Reply | Threaded
Open this post in threaded view
|

Re: select distinct runs slow on pg 10.6

yash mehta
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 <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: select distinct runs slow on pg 10.6

Flo Rance


On Mon, Sep 9, 2019 at 10:38 AM yash mehta <[hidden email]> 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 <[hidden email]> 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.


Regards,
Flo
Reply | Threaded
Open this post in threaded view
|

Re: select distinct runs slow on pg 10.6

yash mehta
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 <[hidden email]> wrote:


On Mon, Sep 9, 2019 at 10:38 AM yash mehta <[hidden email]> 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 <[hidden email]> 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.


Regards,
Flo
Reply | Threaded
Open this post in threaded view
|

Re: select distinct runs slow on pg 10.6

Justin Pryzby
In reply to this post by yash mehta
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


Reply | Threaded
Open this post in threaded view
|

Re: select distinct runs slow on pg 10.6

Flo Rance
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 <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: select distinct runs slow on pg 10.6

Michael Lewis
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.
Reply | Threaded
Open this post in threaded view
|

Re: select distinct runs slow on pg 10.6

yash mehta
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 <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: select distinct runs slow on pg 10.6

Rick Otten-2

On Tue, Sep 10, 2019 at 12:53 AM yash mehta <[hidden email]> 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 <[hidden email]> 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.
 
Reply | Threaded
Open this post in threaded view
|

Re: select distinct runs slow on pg 10.6

Merlin Moncure-2
In reply to this post by yash mehta
On Mon, Sep 9, 2019 at 3:55 AM yash mehta <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: select distinct runs slow on pg 10.6

Dinesh Somani
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 <[hidden email]> wrote:
On Mon, Sep 9, 2019 at 3:55 AM yash mehta <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: select distinct runs slow on pg 10.6

Rick Otten-2


On Wed, Sep 11, 2019 at 12:38 PM Dinesh Somani <[hidden email]> 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:

I've never used it.

 
Reply | Threaded
Open this post in threaded view
|

Re: select distinct runs slow on pg 10.6

Merlin Moncure-2
On Wed, Sep 11, 2019 at 12:57 PM Rick Otten <[hidden email]> wrote:

>
> On Wed, Sep 11, 2019 at 12:38 PM Dinesh Somani <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: select distinct runs slow on pg 10.6

Dinesh Somani
Thanks a lot, Merlin.

Yes, it could appear kinda gross to some ;-) 

On Thu, Sep 12, 2019 at 7:19 AM Merlin Moncure <[hidden email]> wrote:
On Wed, Sep 11, 2019 at 12:57 PM Rick Otten <[hidden email]> wrote:
>
> On Wed, Sep 11, 2019 at 12:38 PM Dinesh Somani <[hidden email]> 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