Re: select distinct runs slow on pg 10.6

Hi Flo,

PFB the explain plan:

"Limit (cost=5925.59..5944.03 rows=25 width=6994) (actual
time=57997.219..58002.451 rows=25 loops=1)"

" -> Unique (cost=5925.59..5969.10 rows=59 width=6994) (actual
time=57997.218..58002.416 rows=25 loops=1)"

" -> Sort (cost=5925.59..5925.74 rows=59 width=6994) (actual
time=57997.214..57997.537 rows=550 loops=1)"

" Sort Key: shipmentre0_.shipment_date, shipmentre0_.fin_id,
workflowst10_.fin_id, carriers3_.fin_id, shipmentro1_.fin_id,
shipmentme11_.fin_id, workflowst9_.fin_id, workflowst8_.fin_id,
workflowst7_.fin_id, consignees5_.fin_id, consignees6_.fin_id,
shipmentty4_.fin_id, shipmentsc2_.fin_id, shipmentre0_.mod_id,
shipmentre0_.shipment_method_id, shipmentre0_.shipment_basis_id,
shipmentre0_.shipment_arrangement_id, shipmentre0_.shipment_currency_id,
shipmentre0_.carrier_crew_extn_id, shipmentre0_.end_time,
shipmentre0_.shipment_value_usd, shipmentre0_.shipment_value_base,
shipmentre0_.insurance_value_usd, shipmentre0_.insurance_value_base,
shipmentre0_.remarks, shipmentre0_.deletion_remarks,
shipmentre0_.insurance_provider, shipmentre0_.shipment_provider,
shipmentre0_.security_provider_id, shipmentre0_.consignee_contact_name,
shipmentre0_.signal, shipmentre0_.chargeable_wt, shipmentre0_.no_of_pieces,
shipmentre0_.regions_id, shipmentre0_.created, shipmentre0_.created_by,
shipmentre0_.last_updated, shipmentre0_.last_updated_by,
shipmentre0_.last_checked_by, shipmentre0_.last_maked,
shipmentre0_.maker_checker_status, shipmentre0_.shadow_id,
workflowst10_.workflow_module, workflowst10_.name,
workflowst10_.deal_display_module, workflowst10_.workflow_level,
workflowst10_.is_deal_editable, workflowst10_.gen_confo,
workflowst10_.gen_deal_ticket, workflowst10_.gen_settlements,
workflowst10_.vault_start, workflowst10_.update_main_inv,
workflowst10_.update_other_inv, workflowst10_.release_shipment,
workflowst10_.is_deal_splittable, workflowst10_.send_email,
workflowst10_.is_deleted, workflowst10_.created, workflowst10_.created_by,
workflowst10_.last_updated, workflowst10_.last_updated_by,
workflowst10_.last_checked_by, workflowst10_.last_maked,
workflowst10_.mod_id, workflowst10_.maker_checker_status,
workflowst10_.shadow_id, carriers3_.mod_id, carriers3_.cities_id,
carriers3_.code, carriers3_.name, carriers3_.carrier_types,
carriers3_.name_in_fl, carriers3_.iata_code, carriers3_.kc_code,
carriers3_.airline_acct, carriers3_.address1, carriers3_.address2,
carriers3_.address3, carriers3_.address4, carriers3_.terminal,
carriers3_.airline_agent, carriers3_.accountinginfo,
carriers3_.import_dept, carriers3_.import_after_office_hour,
carriers3_.import_contact, carriers3_.import_fax, carriers3_.import_email,
carriers3_.export_deptt, carriers3_.export_after_office_hour,
carriers3_.export_contact, carriers3_.export_fax,
carriers3_.import_contact_no, carriers3_.export_contact_no,
carriers3_.export_email, carriers3_.awb_issued_by, carriers3_.is_deleted,
carriers3_.created, carriers3_.created_by, carriers3_.last_updated,
carriers3_.last_updated_by, carriers3_.last_checked_by,
carriers3_.last_maked, carriers3_.maker_checker_status,
carriers3_.shadow_id, shipmentro1_.mod_id, shipmentro1_.region_id,
shipmentro1_.airway_bill_no, shipmentro1_.shipment_date,
shipmentro1_.arrival_date, shipmentro1_.leg_no, shipmentro1_.no_of_pcs,
shipmentro1_.chargeable_weight, shipmentro1_.carrier_crew_extn_id,
shipmentro1_.is_deleted, shipmentro1_.created, shipmentro1_.created_by,
shipmentro1_.last_updated, shipmentro1_.last_updated_by,
shipmentro1_.last_checked_by, shipmentro1_.last_maked,
shipmentro1_.maker_checker_status, shipmentro1_.shadow_id,
shipmentme11_.mod_id, shipmentme11_.code, shipmentme11_.name,
shipmentme11_.shipment_method_type, shipmentme11_.is_deleted,
shipmentme11_.created, shipmentme11_.created_by,
shipmentme11_.last_updated, shipmentme11_.last_updated_by,
shipmentme11_.last_checked_by, shipmentme11_.last_maked,
shipmentme11_.maker_checker_status, shipmentme11_.shadow_id,
workflowst9_.workflow_module, workflowst9_.name,
workflowst9_.deal_display_module, workflowst9_.workflow_level,
workflowst9_.is_deal_editable, workflowst9_.gen_confo,
workflowst9_.gen_deal_ticket, workflowst9_.gen_settlements,
workflowst9_.vault_start, workflowst9_.update_main_inv,
workflowst9_.update_other_inv, workflowst9_.release_shipment,
workflowst9_.is_deal_splittable, workflowst9_.send_email,
workflowst9_.is_deleted, workflowst9_.created, workflowst9_.created_by,
workflowst9_.last_updated, workflowst9_.last_updated_by,
workflowst9_.last_checked_by, workflowst9_.last_maked, workflowst9_.mod_id,
workflowst9_.maker_checker_status, workflowst9_.shadow_id,
workflowst8_.workflow_module, workflowst8_.name,
workflowst8_.deal_display_module, workflowst8_.workflow_level,
workflowst8_.is_deal_editable, workflowst8_.gen_confo,
workflowst8_.gen_deal_ticket, workflowst8_.gen_settlements,
workflowst8_.vault_start, workflowst8_.update_main_inv,
workflowst8_.update_other_inv, workflowst8_.release_shipment,
workflowst8_.is_deal_splittable, workflowst8_.send_email,
workflowst8_.is_deleted, workflowst8_.created, workflowst8_.created_by,
workflowst8_.last_updated, workflowst8_.last_updated_by,
workflowst8_.last_checked_by, workflowst8_.last_maked, workflowst8_.mod_id,
workflowst8_.maker_checker_status, workflowst8_.shadow_id,
workflowst7_.workflow_module, workflowst7_.name,
workflowst7_.deal_display_module, workflowst7_.workflow_level,
workflowst7_.is_deal_editable, workflowst7_.gen_confo,
workflowst7_.gen_deal_ticket, workflowst7_.gen_settlements,
workflowst7_.vault_start, workflowst7_.update_main_inv,
workflowst7_.update_other_inv, workflowst7_.release_shipment,
workflowst7_.is_deal_splittable, workflowst7_.send_email,
workflowst7_.is_deleted, workflowst7_.created, workflowst7_.created_by,
workflowst7_.last_updated, workflowst7_.last_updated_by,
workflowst7_.last_checked_by, workflowst7_.last_maked, workflowst7_.mod_id,
workflowst7_.maker_checker_status, workflowst7_.shadow_id,
consignees5_.mod_id, consignees5_.countries_id, consignees5_.cities_id,
consignees5_.regions_id, consignees5_.short_name,
consignees5_.is_counterparty, consignees5_.name, consignees5_.airports_id,
consignees5_.address1, consignees5_.address2, consignees5_.address3,
consignees5_.address4, consignees5_.awb_special_clause,
consignees5_.issuing_carrier_agent_name, consignees5_.agent_address1,
consignees5_.agent_address2, consignees5_.postal_code,
consignees5_.is_deleted, consignees5_.created, consignees5_.created_by,
consignees5_.last_updated, consignees5_.last_updated_by,
consignees5_.last_checked_by, consignees5_.last_maked,
consignees5_.maker_checker_status, consignees5_.shadow_id,
consignees6_.mod_id, consignees6_.countries_id, consignees6_.cities_id,
consignees6_.regions_id, consignees6_.short_name,
consignees6_.is_counterparty, consignees6_.name, consignees6_.airports_id,
consignees6_.address1, consignees6_.address2, consignees6_.address3,
consignees6_.address4, consignees6_.awb_special_clause,
consignees6_.issuing_carrier_agent_name, consignees6_.agent_address1,
consignees6_.agent_address2, consignees6_.postal_code,
consignees6_.is_deleted, consignees6_.created, consignees6_.created_by,
consignees6_.last_updated, consignees6_.last_updated_by,
consignees6_.last_checked_by, consignees6_.last_maked,
consignees6_.maker_checker_status, consignees6_.shadow_id,
shipmentty4_.mod_id, shipmentty4_.code, shipmentty4_.name,
shipmentty4_.regions_id, shipmentty4_.is_deleted, shipmentty4_.created,
shipmentty4_.created_by, shipmentty4_.last_updated,
shipmentty4_.last_updated_by, shipmentty4_.last_checked_by,
shipmentty4_.last_maked, shipmentty4_.maker_checker_status,
shipmentty4_.shadow_id, shipmentsc2_.mod_id,
shipmentsc2_.origin_airports_id, shipmentsc2_.dest_airports_id,
shipmentsc2_.schedule, shipmentsc2_.arrival_date,
shipmentsc2_.est_time_departure, shipmentsc2_.est_time_arrival,
shipmentsc2_.route_leg_seq_no, shipmentsc2_.cutoff_hours_before_departure,
shipmentsc2_.available_in_a_week, shipmentsc2_.remarks,
shipmentsc2_.status, shipmentsc2_.region_id, shipmentsc2_.is_deleted,
shipmentsc2_.created, shipmentsc2_.created_by, shipmentsc2_.last_updated,
shipmentsc2_.last_updated_by, shipmentsc2_.last_checked_by,
shipmentsc2_.last_maked, shipmentsc2_.maker_checker_status,
shipmentsc2_.shadow_id"

" Sort Method: external merge Disk: 90656kB"

" -> Hash Right Join (cost=388.61..5923.86 rows=59
width=6994) (actual time=143.405..372.903 rows=42759 loops=1)"

" Hash Cond: ((deallegs12_.shipment_records_id)::text =
(shipmentre0_.fin_id)::text)"

" -> Seq Scan on tbls_bank_notes_deals_legs
deallegs12_ (cost=0.00..5337.57 rows=52557 width=16) (actual
time=0.005..26.702 rows=52557 loops=1)"

" -> Hash (cost=388.58..388.58 rows=2 width=6960)
(actual time=143.371..143.371 rows=1442 loops=1)"

" Buckets: 2048 (originally 1024) Batches: 1
(originally 1) Memory Usage: 3107kB"

" -> Nested Loop Left Join (cost=106.73..388.58
rows=2 width=6960) (actual time=55.316..134.874 rows=1442 loops=1)"

" Join Filter:
((shipmentre0_.shipment_method_id)::text = (shipmentme11_.fin_id)::text)"

" Rows Removed by Join Filter: 2350"

" -> Nested Loop (cost=106.73..387.37
rows=2 width=6721) (actual time=55.300..130.529 rows=1442 loops=1)"

" -> Nested Loop
(cost=106.59..387.03 rows=2 width=6582) (actual time=55.282..124.351
rows=1442 loops=1)"

" -> Nested Loop
(cost=106.45..386.69 rows=2 width=6443) (actual time=55.267..118.047
rows=1442 loops=1)"

" -> Nested Loop
(cost=106.31..386.36 rows=2 width=6304) (actual time=55.250..111.408
rows=1442 loops=1)"

" -> Nested Loop
(cost=106.17..386.02 rows=2 width=6165) (actual time=55.228..105.002
rows=1442 loops=1)"

" Join Filter:
((shipmentre0_.consignees_id)::text = (consignees6_.fin_id)::text)"

" Rows Removed
by Join Filter: 40376"

" -> Seq Scan
on tbls_consignees consignees6_ (cost=0.00..1.29 rows=29 width=1060)
(actual time=0.012..0.021 rows=29 loops=1)"

" ->
Materialize (cost=106.17..383.86 rows=2 width=5105) (actual
time=1.904..3.142 rows=1442 loops=29)"

" ->
Nested Loop (cost=106.17..383.85 rows=2 width=5105) (actual
time=55.203..78.206 rows=1442 loops=1)"

"
Join Filter: ((shipmentre0_.shipper_id)::text =
(consignees5_.fin_id)::text)"

"
Rows Removed by Join Filter: 40376"

"
-> Seq Scan on tbls_consignees consignees5_ (cost=0.00..1.29 rows=29
width=1060) (actual time=0.003..0.013 rows=29 loops=1)"

"
-> Materialize (cost=106.17..381.70 rows=2 width=4045) (actual
time=0.524..2.244 rows=1442 loops=29)"

"
->
Nested Loop (cost=106.17..381.69 rows=2 width=4045) (actual
time=15.195..53.051 rows=1442 loops=1)"

"
Join Filter: ((shipmentre0_.shipment_type_id)::text =
(shipmentty4_.fin_id)::text)"

"
Rows Removed by Join Filter: 7210"

"
-> Seq Scan on
tbls_shipment_types shipmentty4_ (cost=0.00..1.06 rows=6 width=95) (actual
time=0.002..0.005 rows=6 loops=1)"

"
-> Materialize (cost=106.17..380.45 rows=2 width=3950) (actual
time=2.478..8.157 rows=1442 loops=6)"

"
-> Nested Loop (cost=106.17..380.44 rows=2 width=3950) (actual
time=14.856..43.625 rows=1442 loops=1)"

"
-> Nested Loop (cost=106.03..379.95 rows=2 width=1696) (actual
time=14.824..38.885 rows=1442 loops=1)"

"
-> Hash Join (cost=105.76..379.20 rows=2 width=1371) (actual
time=14.807..32.459 rows=1442 loops=1)"

"
Hash Cond:
(((shipmentro1_.shipment_record_id)::text = (shipmentre0_.fin_id)::text)
AND (shipmentro1_.leg_no = (SubPlan 1)))"

"
-> Seq Scan on
tbls_shipment_record_routing shipmentro1_ (cost=0.00..69.80 rows=484
width=444) (actual time=0.017..2.534 rows=1452 loops=1)"

"
Filter:
(to_char(arrival_date, 'YYYY-MM-DD'::text) <= '2019-08-29'::text)"

"
Rows Removed by Filter: 1"

"
-> Hash (cost=84.11..84.11 rows=1443 width=927) (actual
time=14.762..14.763 rows=1443 loops=1)"

"

Buckets:
2048 Batches: 1 Memory Usage: 497kB"

"
-> Seq Scan on tbls_shipment_records shipmentre0_ (cost=0.00..84.11
rows=1443 width=927) (actual time=0.005..1.039 rows=1443 loops=1)"

"
Filter: ((is_deleted)::text = 'N'::text)"

"
Rows Removed by Filter: 6"

"
SubPlan 1"

"
-> Aggregate (cost=8.30..8.31 rows=1 width=8) (actual time=0.008..0.008
rows=1 loops=2885)"

"
-> Index Scan using xbls_shipment_record_rout001 on
tbls_shipment_record_routing shipmentro13_ (cost=0.28..8.30 rows=1
width=8) (actual time=0.006..0.007 rows=1 loops=2885)"

"
Index Cond: ((shipmentre0_.fin_id)::text = (shipment_record_id)::text)"

"
Filter: ((is_deleted)::text = 'N'::text)"

"
Rows Removed by Filter: 0"

"
-> Index Scan using pk_bls_shipment_schedules on tbls_shipment_schedules
shipmentsc2_ (cost=0.27..0.38 rows=1 width=325) (actual time=0.003..0.003
rows=1 loops=1442)"

"
Index Cond: ((fin_id)::text = (shipmentro1_.shipment_schedule_id)::text)"

"
-> Index Scan using pk_bls_carriers on tbls_carriers carriers3_
(cost=0.14..0.24 rows=1 width=2254) (actual time=0.002..0.002 rows=1
loops=1442)"

"
Index Cond: ((fin_id)::text = (shipmentsc2_.carrier_id)::text)"

" -> Index Scan
using pk_bls_workflow_states on tbls_workflow_states workflowst7_
(cost=0.14..0.17 rows=1 width=139) (actual time=0.003..0.003 rows=1
loops=1442)"

" Index Cond:
((fin_id)::text = (shipmentre0_.shipment_status_id)::text)"

" -> Index Scan using
pk_bls_workflow_states on tbls_workflow_states workflowst8_
(cost=0.14..0.17 rows=1 width=139) (actual time=0.003..0.003 rows=1
loops=1442)"

" Index Cond:
((fin_id)::text = (shipmentre0_.shipment_charge_status)::text)"

" -> Index Scan using
pk_bls_workflow_states on tbls_workflow_states workflowst9_
(cost=0.14..0.17 rows=1 width=139) (actual time=0.002..0.002 rows=1
loops=1442)"

" Index Cond:
((fin_id)::text = (shipmentre0_.shipment_document_status)::text)"

" -> Index Scan using
pk_bls_workflow_states on tbls_workflow_states workflowst10_
(cost=0.14..0.17 rows=1 width=139) (actual time=0.002..0.002 rows=1
loops=1442)"

" Index Cond: ((fin_id)::text =
(shipmentre0_.vault_status_id)::text)"

" -> Materialize (cost=0.00..1.07 rows=5
width=239) (actual time=0.000..0.001 rows=3 loops=1442)"

" -> Seq Scan on
tbls_shipment_methods shipmentme11_ (cost=0.00..1.05 rows=5 width=239)
(actual time=0.006..0.010 rows=5 loops=1)"

"Planning time: 368.495 ms"

"Execution time: 58018.486 ms"

On Mon, Sep 9, 2019 at 3:30 PM Flo Rance <trourance(at)gmail(dot)com> wrote:

>
>
> On Mon, Sep 9, 2019 at 10:38 AM yash mehta <yash215(at)gmail(dot)com> wrote:
>
>> In addition to below mail, we have used btree indexes for primary key
>> columns. Below is the query:
>>
>> select distinct shipmentre0_.FIN_ID as
>> FIN1_53_0_,
>> workflowst10_.FIN_ID as FIN1_57_1_,
>> carriers3_.FIN_ID as FIN1_40_2_,
>> shipmentro1_.FIN_ID as FIN1_33_3_,
>> shipmentme11_.FIN_ID as FIN1_5_4_,
>> workflowst9_.FIN_ID as FIN1_57_5_,
>> workflowst8_.FIN_ID as FIN1_57_6_,
>> workflowst7_.FIN_ID as FIN1_57_7_,
>> consignees5_.FIN_ID as FIN1_81_8_,
>> consignees6_.FIN_ID as FIN1_81_9_,
>> shipmentty4_.FIN_ID as FIN1_8_10_,
>> shipmentsc2_.FIN_ID as FIN1_78_11_,
>> shipmentre0_.MOD_ID as MOD2_53_0_,
>> shipmentre0_.SHIPMENT_METHOD_ID as SHIPMENT3_53_0_,
>> shipmentre0_.SHIPPER_ID as SHIPPER4_53_0_,
>> shipmentre0_.CONSIGNEES_ID as CONSIGNEES5_53_0_,
>> shipmentre0_.SHIPMENT_BASIS_ID as SHIPMENT6_53_0_,
>> shipmentre0_.SHIPMENT_TYPE_ID as SHIPMENT7_53_0_,
>> shipmentre0_.SHIPMENT_ARRANGEMENT_ID as SHIPMENT8_53_0_,
>> shipmentre0_.SHIPMENT_DATE as SHIPMENT9_53_0_,
>> shipmentre0_.SHIPMENT_CURRENCY_ID as SHIPMENT10_53_0_,
>> shipmentre0_.CARRIER_CREW_EXTN_ID as CARRIER11_53_0_,
>> shipmentre0_.END_TIME as END12_53_0_,
>> shipmentre0_.SHIPMENT_VALUE_USD as SHIPMENT13_53_0_,
>> shipmentre0_.SHIPMENT_VALUE_BASE as SHIPMENT14_53_0_,
>> shipmentre0_.INSURANCE_VALUE_USD as INSURANCE15_53_0_,
>> shipmentre0_.INSURANCE_VALUE_BASE as INSURANCE16_53_0_,
>> shipmentre0_.REMARKS as REMARKS53_0_,
>> shipmentre0_.DELETION_REMARKS as DELETION18_53_0_,
>> shipmentre0_.SHIPMENT_STATUS_ID as SHIPMENT19_53_0_,
>> shipmentre0_.VAULT_STATUS_ID as VAULT20_53_0_,
>> shipmentre0_.SHIPMENT_CHARGE_STATUS as SHIPMENT21_53_0_,
>> shipmentre0_.SHIPMENT_DOCUMENT_STATUS as SHIPMENT22_53_0_,
>> shipmentre0_.INSURANCE_PROVIDER as INSURANCE23_53_0_,
>> shipmentre0_.SHIPMENT_PROVIDER as SHIPMENT24_53_0_,
>> shipmentre0_.SECURITY_PROVIDER_ID as SECURITY25_53_0_,
>> shipmentre0_.CONSIGNEE_CONTACT_NAME as CONSIGNEE26_53_0_,
>> shipmentre0_.SIGNAL as SIGNAL53_0_,
>> shipmentre0_.CHARGEABLE_WT as CHARGEABLE28_53_0_,
>> shipmentre0_.NO_OF_PIECES as NO29_53_0_,
>> shipmentre0_.REGIONS_ID as REGIONS30_53_0_,
>> shipmentre0_.IS_DELETED as IS31_53_0_,
>> shipmentre0_.CREATED as CREATED53_0_,
>> shipmentre0_.CREATED_BY as CREATED33_53_0_,
>> shipmentre0_.LAST_UPDATED as LAST34_53_0_,
>> shipmentre0_.LAST_UPDATED_BY as LAST35_53_0_,
>> shipmentre0_.LAST_CHECKED_BY as LAST36_53_0_,
>> shipmentre0_.LAST_MAKED as LAST37_53_0_,
>> shipmentre0_.MAKER_CHECKER_STATUS as MAKER38_53_0_,
>> shipmentre0_.SHADOW_ID as SHADOW39_53_0_,
>> --(select now()) as formula48_0_,
>> workflowst10_.WORKFLOW_MODULE as WORKFLOW2_57_1_,
>> workflowst10_.NAME as NAME57_1_,
>> workflowst10_.DEAL_DISPLAY_MODULE as DEAL4_57_1_,
>> workflowst10_.WORKFLOW_LEVEL as WORKFLOW5_57_1_,
>> workflowst10_.IS_DEAL_EDITABLE as IS6_57_1_,
>> workflowst10_.GEN_CONFO as GEN7_57_1_,
>> workflowst10_.GEN_DEAL_TICKET as GEN8_57_1_,
>> workflowst10_.GEN_SETTLEMENTS as GEN9_57_1_,
>> workflowst10_.VAULT_START as VAULT10_57_1_,
>> workflowst10_.UPDATE_MAIN_INV as UPDATE11_57_1_,
>> workflowst10_.UPDATE_OTHER_INV as UPDATE12_57_1_,
>> workflowst10_.RELEASE_SHIPMENT as RELEASE13_57_1_,
>> workflowst10_.IS_DEAL_SPLITTABLE as IS14_57_1_,
>> workflowst10_.SEND_EMAIL as SEND15_57_1_,
>> workflowst10_.IS_DELETED as IS16_57_1_,
>> workflowst10_.CREATED as CREATED57_1_,
>> workflowst10_.CREATED_BY as CREATED18_57_1_,
>> workflowst10_.LAST_UPDATED as LAST19_57_1_,
>> workflowst10_.LAST_UPDATED_BY as LAST20_57_1_,
>> workflowst10_.LAST_CHECKED_BY as LAST21_57_1_,
>> workflowst10_.LAST_MAKED as LAST22_57_1_,
>> workflowst10_.MOD_ID as MOD23_57_1_,
>> workflowst10_.MAKER_CHECKER_STATUS as MAKER24_57_1_,
>> workflowst10_.SHADOW_ID as SHADOW25_57_1_,
>> --(select now()) as formula52_1_,
>> carriers3_.MOD_ID as MOD2_40_2_,
>> carriers3_.CITIES_ID as CITIES3_40_2_,
>> carriers3_.CODE as CODE40_2_,
>> carriers3_.NAME as NAME40_2_,
>> carriers3_.CARRIER_TYPES as CARRIER6_40_2_,
>> carriers3_.NAME_IN_FL as NAME7_40_2_,
>> carriers3_.IATA_CODE as IATA8_40_2_,
>> carriers3_.KC_CODE as KC9_40_2_,
>> carriers3_.AIRLINE_ACCT as AIRLINE10_40_2_,
>> carriers3_.ADDRESS1 as ADDRESS11_40_2_,
>> carriers3_.ADDRESS2 as ADDRESS12_40_2_,
>> carriers3_.ADDRESS3 as ADDRESS13_40_2_,
>> carriers3_.ADDRESS4 as ADDRESS14_40_2_,
>> carriers3_.TERMINAL as TERMINAL40_2_,
>> carriers3_.AIRLINE_AGENT as AIRLINE16_40_2_,
>> carriers3_.ACCOUNTINGINFO as ACCOUNT17_40_2_,
>> carriers3_.IMPORT_DEPT as IMPORT18_40_2_,
>> carriers3_.IMPORT_AFTER_OFFICE_HOUR as IMPORT19_40_2_,
>> carriers3_.IMPORT_CONTACT as IMPORT20_40_2_,
>> carriers3_.IMPORT_FAX as IMPORT21_40_2_,
>> carriers3_.IMPORT_EMAIL as IMPORT22_40_2_,
>> carriers3_.EXPORT_DEPTT as EXPORT23_40_2_,
>> carriers3_.EXPORT_AFTER_OFFICE_HOUR as EXPORT24_40_2_,
>> carriers3_.EXPORT_CONTACT as EXPORT25_40_2_,
>> carriers3_.EXPORT_FAX as EXPORT26_40_2_,
>> carriers3_.IMPORT_CONTACT_NO as IMPORT27_40_2_,
>> carriers3_.EXPORT_CONTACT_NO as EXPORT28_40_2_,
>> carriers3_.EXPORT_EMAIL as EXPORT29_40_2_,
>> carriers3_.AWB_ISSUED_BY as AWB30_40_2_,
>> carriers3_.IS_DELETED as IS31_40_2_,
>> carriers3_.CREATED as CREATED40_2_,
>> carriers3_.CREATED_BY as CREATED33_40_2_,
>> carriers3_.LAST_UPDATED as LAST34_40_2_,
>> carriers3_.LAST_UPDATED_BY as LAST35_40_2_,
>> carriers3_.LAST_CHECKED_BY as LAST36_40_2_,
>> carriers3_.LAST_MAKED as LAST37_40_2_,
>> carriers3_.MAKER_CHECKER_STATUS as MAKER38_40_2_,
>> carriers3_.SHADOW_ID as SHADOW39_40_2_,
>> --(select now()) as formula36_2_,
>> shipmentro1_.MOD_ID as MOD2_33_3_,
>> shipmentro1_.REGION_ID as REGION3_33_3_,
>> shipmentro1_.SHIPMENT_SCHEDULE_ID as SHIPMENT4_33_3_,
>> shipmentro1_.SHIPMENT_RECORD_ID as SHIPMENT5_33_3_,
>> shipmentro1_.AIRWAY_BILL_NO as AIRWAY6_33_3_,
>> shipmentro1_.SHIPMENT_DATE as SHIPMENT7_33_3_,
>> shipmentro1_.ARRIVAL_DATE as ARRIVAL8_33_3_,
>> shipmentro1_.LEG_NO as LEG9_33_3_,
>> shipmentro1_.NO_OF_PCS as NO10_33_3_,
>> shipmentro1_.CHARGEABLE_WEIGHT as CHARGEABLE11_33_3_,
>> shipmentro1_.CARRIER_CREW_EXTN_ID as CARRIER12_33_3_,
>> shipmentro1_.IS_DELETED as IS13_33_3_,
>> shipmentro1_.CREATED as CREATED33_3_,
>> shipmentro1_.CREATED_BY as CREATED15_33_3_,
>> shipmentro1_.LAST_UPDATED as LAST16_33_3_,
>> shipmentro1_.LAST_UPDATED_BY as LAST17_33_3_,
>> shipmentro1_.LAST_CHECKED_BY as LAST18_33_3_,
>> shipmentro1_.LAST_MAKED as LAST19_33_3_,
>> shipmentro1_.MAKER_CHECKER_STATUS as MAKER20_33_3_,
>> shipmentro1_.SHADOW_ID as SHADOW21_33_3_,
>> --(select now()) as formula29_3_,
>> shipmentme11_.MOD_ID as MOD2_5_4_,
>> shipmentme11_.CODE as CODE5_4_,
>> shipmentme11_.NAME as NAME5_4_,
>> shipmentme11_.SHIPMENT_METHOD_TYPE as SHIPMENT5_5_4_,
>> shipmentme11_.IS_DELETED as IS6_5_4_,
>> shipmentme11_.CREATED as CREATED5_4_,
>> shipmentme11_.CREATED_BY as CREATED8_5_4_,
>> shipmentme11_.LAST_UPDATED as LAST9_5_4_,
>> shipmentme11_.LAST_UPDATED_BY as LAST10_5_4_,
>> shipmentme11_.LAST_CHECKED_BY as LAST11_5_4_,
>> shipmentme11_.LAST_MAKED as LAST12_5_4_,
>> shipmentme11_.MAKER_CHECKER_STATUS as MAKER13_5_4_,
>> shipmentme11_.SHADOW_ID as SHADOW14_5_4_,
>> --(select now()) as formula4_4_,
>> workflowst9_.WORKFLOW_MODULE as WORKFLOW2_57_5_,
>> workflowst9_.NAME as NAME57_5_,
>> workflowst9_.DEAL_DISPLAY_MODULE as DEAL4_57_5_,
>> workflowst9_.WORKFLOW_LEVEL as WORKFLOW5_57_5_,
>> workflowst9_.IS_DEAL_EDITABLE as IS6_57_5_,
>> workflowst9_.GEN_CONFO as GEN7_57_5_,
>> workflowst9_.GEN_DEAL_TICKET as GEN8_57_5_,
>> workflowst9_.GEN_SETTLEMENTS as GEN9_57_5_,
>> workflowst9_.VAULT_START as VAULT10_57_5_,
>> workflowst9_.UPDATE_MAIN_INV as UPDATE11_57_5_,
>> workflowst9_.UPDATE_OTHER_INV as UPDATE12_57_5_,
>> workflowst9_.RELEASE_SHIPMENT as RELEASE13_57_5_,
>> workflowst9_.IS_DEAL_SPLITTABLE as IS14_57_5_,
>> workflowst9_.SEND_EMAIL as SEND15_57_5_,
>> workflowst9_.IS_DELETED as IS16_57_5_,
>> workflowst9_.CREATED as CREATED57_5_,
>> workflowst9_.CREATED_BY as CREATED18_57_5_,
>> workflowst9_.LAST_UPDATED as LAST19_57_5_,
>> workflowst9_.LAST_UPDATED_BY as LAST20_57_5_,
>> workflowst9_.LAST_CHECKED_BY as LAST21_57_5_,
>> workflowst9_.LAST_MAKED as LAST22_57_5_,
>> workflowst9_.MOD_ID as MOD23_57_5_,
>> workflowst9_.MAKER_CHECKER_STATUS as MAKER24_57_5_,
>> workflowst9_.SHADOW_ID as SHADOW25_57_5_,
>> --(select now()) as formula52_5_,
>> workflowst8_.WORKFLOW_MODULE as WORKFLOW2_57_6_,
>> workflowst8_.NAME as NAME57_6_,
>> workflowst8_.DEAL_DISPLAY_MODULE as DEAL4_57_6_,
>> workflowst8_.WORKFLOW_LEVEL as WORKFLOW5_57_6_,
>> workflowst8_.IS_DEAL_EDITABLE as IS6_57_6_,
>> workflowst8_.GEN_CONFO as GEN7_57_6_,
>> workflowst8_.GEN_DEAL_TICKET as GEN8_57_6_,
>> workflowst8_.GEN_SETTLEMENTS as GEN9_57_6_,
>> workflowst8_.VAULT_START as VAULT10_57_6_,
>> workflowst8_.UPDATE_MAIN_INV as UPDATE11_57_6_,
>> workflowst8_.UPDATE_OTHER_INV as UPDATE12_57_6_,
>> workflowst8_.RELEASE_SHIPMENT as RELEASE13_57_6_,
>> workflowst8_.IS_DEAL_SPLITTABLE as IS14_57_6_,
>> workflowst8_.SEND_EMAIL as SEND15_57_6_,
>> workflowst8_.IS_DELETED as IS16_57_6_,
>> workflowst8_.CREATED as CREATED57_6_,
>> workflowst8_.CREATED_BY as CREATED18_57_6_,
>> workflowst8_.LAST_UPDATED as LAST19_57_6_,
>> workflowst8_.LAST_UPDATED_BY as LAST20_57_6_,
>> workflowst8_.LAST_CHECKED_BY as LAST21_57_6_,
>> workflowst8_.LAST_MAKED as LAST22_57_6_,
>> workflowst8_.MOD_ID as MOD23_57_6_,
>> workflowst8_.MAKER_CHECKER_STATUS as MAKER24_57_6_,
>> workflowst8_.SHADOW_ID as SHADOW25_57_6_,
>> --(select now()) as formula52_6_,
>> workflowst7_.WORKFLOW_MODULE as WORKFLOW2_57_7_,
>> workflowst7_.NAME as NAME57_7_,
>> workflowst7_.DEAL_DISPLAY_MODULE as DEAL4_57_7_,
>> workflowst7_.WORKFLOW_LEVEL as WORKFLOW5_57_7_,
>> workflowst7_.IS_DEAL_EDITABLE as IS6_57_7_,
>> workflowst7_.GEN_CONFO as GEN7_57_7_,
>> workflowst7_.GEN_DEAL_TICKET as GEN8_57_7_,
>> workflowst7_.GEN_SETTLEMENTS as GEN9_57_7_,
>> workflowst7_.VAULT_START as VAULT10_57_7_,
>> workflowst7_.UPDATE_MAIN_INV as UPDATE11_57_7_,
>> workflowst7_.UPDATE_OTHER_INV as UPDATE12_57_7_,
>> workflowst7_.RELEASE_SHIPMENT as RELEASE13_57_7_,
>> workflowst7_.IS_DEAL_SPLITTABLE as IS14_57_7_,
>> workflowst7_.SEND_EMAIL as SEND15_57_7_,
>> workflowst7_.IS_DELETED as IS16_57_7_,
>> workflowst7_.CREATED as CREATED57_7_,
>> workflowst7_.CREATED_BY as CREATED18_57_7_,
>> workflowst7_.LAST_UPDATED as LAST19_57_7_,
>> workflowst7_.LAST_UPDATED_BY as LAST20_57_7_,
>> workflowst7_.LAST_CHECKED_BY as LAST21_57_7_,
>> workflowst7_.LAST_MAKED as LAST22_57_7_,
>> workflowst7_.MOD_ID as MOD23_57_7_,
>> workflowst7_.MAKER_CHECKER_STATUS as MAKER24_57_7_,
>> workflowst7_.SHADOW_ID as SHADOW25_57_7_,
>> --(select now()) as formula52_7_,
>> consignees5_.MOD_ID as MOD2_81_8_,
>> consignees5_.COUNTRIES_ID as COUNTRIES3_81_8_,
>> consignees5_.CITIES_ID as CITIES4_81_8_,
>> consignees5_.REGIONS_ID as REGIONS5_81_8_,
>> consignees5_.SHORT_NAME as SHORT6_81_8_,
>> consignees5_.IS_COUNTERPARTY as IS7_81_8_,
>> consignees5_.NAME as NAME81_8_,
>> consignees5_.AIRPORTS_ID as AIRPORTS9_81_8_,
>> consignees5_.ADDRESS1 as ADDRESS10_81_8_,
>> consignees5_.ADDRESS2 as ADDRESS11_81_8_,
>> consignees5_.ADDRESS3 as ADDRESS12_81_8_,
>> consignees5_.ADDRESS4 as ADDRESS13_81_8_,
>> consignees5_.AWB_SPECIAL_CLAUSE as AWB14_81_8_,
>> consignees5_.ISSUING_CARRIER_AGENT_NAME as ISSUING15_81_8_,
>> consignees5_.AGENT_ADDRESS1 as AGENT16_81_8_,
>> consignees5_.AGENT_ADDRESS2 as AGENT17_81_8_,
>> consignees5_.POSTAL_CODE as POSTAL18_81_8_,
>> consignees5_.IS_DELETED as IS19_81_8_,
>> consignees5_.CREATED as CREATED81_8_,
>> consignees5_.CREATED_BY as CREATED21_81_8_,
>> consignees5_.LAST_UPDATED as LAST22_81_8_,
>> consignees5_.LAST_UPDATED_BY as LAST23_81_8_,
>> consignees5_.LAST_CHECKED_BY as LAST24_81_8_,
>> consignees5_.LAST_MAKED as LAST25_81_8_,
>> consignees5_.MAKER_CHECKER_STATUS as MAKER26_81_8_,
>> consignees5_.SHADOW_ID as SHADOW27_81_8_,
>> --(select now()) as formula74_8_,
>> consignees6_.MOD_ID as MOD2_81_9_,
>> consignees6_.COUNTRIES_ID as COUNTRIES3_81_9_,
>> consignees6_.CITIES_ID as CITIES4_81_9_,
>> consignees6_.REGIONS_ID as REGIONS5_81_9_,
>> consignees6_.SHORT_NAME as SHORT6_81_9_,
>> consignees6_.IS_COUNTERPARTY as IS7_81_9_,
>> consignees6_.NAME as NAME81_9_,
>> consignees6_.AIRPORTS_ID as AIRPORTS9_81_9_,
>> consignees6_.ADDRESS1 as ADDRESS10_81_9_,
>> consignees6_.ADDRESS2 as ADDRESS11_81_9_,
>> consignees6_.ADDRESS3 as ADDRESS12_81_9_,
>> consignees6_.ADDRESS4 as ADDRESS13_81_9_,
>> consignees6_.AWB_SPECIAL_CLAUSE as AWB14_81_9_,
>> consignees6_.ISSUING_CARRIER_AGENT_NAME as ISSUING15_81_9_,
>> consignees6_.AGENT_ADDRESS1 as AGENT16_81_9_,
>> consignees6_.AGENT_ADDRESS2 as AGENT17_81_9_,
>> consignees6_.POSTAL_CODE as POSTAL18_81_9_,
>> consignees6_.IS_DELETED as IS19_81_9_,
>> consignees6_.CREATED as CREATED81_9_,
>> consignees6_.CREATED_BY as CREATED21_81_9_,
>> consignees6_.LAST_UPDATED as LAST22_81_9_,
>> consignees6_.LAST_UPDATED_BY as LAST23_81_9_,
>> consignees6_.LAST_CHECKED_BY as LAST24_81_9_,
>> consignees6_.LAST_MAKED as LAST25_81_9_,
>> consignees6_.MAKER_CHECKER_STATUS as MAKER26_81_9_,
>> consignees6_.SHADOW_ID as SHADOW27_81_9_,
>> --(select now()) as formula74_9_,
>> shipmentty4_.MOD_ID as MOD2_8_10_,
>> shipmentty4_.CODE as CODE8_10_,
>> shipmentty4_.NAME as NAME8_10_,
>> shipmentty4_.REGIONS_ID as REGIONS5_8_10_,
>> shipmentty4_.IS_DELETED as IS6_8_10_,
>> shipmentty4_.CREATED as CREATED8_10_,
>> shipmentty4_.CREATED_BY as CREATED8_8_10_,
>> shipmentty4_.LAST_UPDATED as LAST9_8_10_,
>> shipmentty4_.LAST_UPDATED_BY as LAST10_8_10_,
>> shipmentty4_.LAST_CHECKED_BY as LAST11_8_10_,
>> shipmentty4_.LAST_MAKED as LAST12_8_10_,
>> shipmentty4_.MAKER_CHECKER_STATUS as MAKER13_8_10_,
>> shipmentty4_.SHADOW_ID as SHADOW14_8_10_,
>> --(select now()) as formula6_10_,
>> shipmentsc2_.MOD_ID as MOD2_78_11_,
>> shipmentsc2_.CARRIER_ID as CARRIER3_78_11_,
>> shipmentsc2_.ORIGIN_AIRPORTS_ID as ORIGIN4_78_11_,
>> shipmentsc2_.DEST_AIRPORTS_ID as DEST5_78_11_,
>> shipmentsc2_.SCHEDULE as SCHEDULE78_11_,
>> shipmentsc2_.ARRIVAL_DATE as ARRIVAL7_78_11_,
>> shipmentsc2_.EST_TIME_DEPARTURE as EST8_78_11_,
>> shipmentsc2_.EST_TIME_ARRIVAL as EST9_78_11_,
>> shipmentsc2_.ROUTE_LEG_SEQ_NO as ROUTE10_78_11_,
>> shipmentsc2_.CUTOFF_HOURS_BEFORE_DEPARTURE as CUTOFF11_78_11_,
>> shipmentsc2_.AVAILABLE_IN_A_WEEK as AVAILABLE12_78_11_,
>> shipmentsc2_.REMARKS as REMARKS78_11_,
>> shipmentsc2_.STATUS as STATUS78_11_,
>> shipmentsc2_.REGION_ID as REGION15_78_11_,
>> shipmentsc2_.IS_DELETED as IS16_78_11_,
>> shipmentsc2_.CREATED as CREATED78_11_,
>> shipmentsc2_.CREATED_BY as CREATED18_78_11_,
>> shipmentsc2_.LAST_UPDATED as LAST19_78_11_,
>> shipmentsc2_.LAST_UPDATED_BY as LAST20_78_11_,
>> shipmentsc2_.LAST_CHECKED_BY as LAST21_78_11_,
>> shipmentsc2_.LAST_MAKED as LAST22_78_11_,
>> shipmentsc2_.MAKER_CHECKER_STATUS as MAKER23_78_11_,
>> shipmentsc2_.SHADOW_ID as SHADOW24_78_11_,
>> --(select now()) as formula71_11_,
>> shipmentro1_.SHIPMENT_RECORD_ID as SHIPMENT5___,
>> shipmentro1_.FIN_ID as FIN1___
>> from TBLS_SHIPMENT_RECORDS shipmentre0_
>> inner join TBLS_SHIPMENT_RECORD_ROUTING shipmentro1_ on
>> shipmentre0_.FIN_ID = shipmentro1_.SHIPMENT_RECORD_ID
>> inner join TBLS_SHIPMENT_SCHEDULES shipmentsc2_ on
>> shipmentro1_.SHIPMENT_SCHEDULE_ID = shipmentsc2_.FIN_ID
>> inner join TBLS_CARRIERS carriers3_ on shipmentsc2_.CARRIER_ID =
>> carriers3_.FIN_ID
>> inner join TBLS_SHIPMENT_TYPES shipmentty4_ on
>> shipmentre0_.SHIPMENT_TYPE_ID = shipmentty4_.FIN_ID
>> inner join TBLS_CONSIGNEES consignees5_ on shipmentre0_.SHIPPER_ID =
>> consignees5_.FIN_ID
>> inner join TBLS_CONSIGNEES consignees6_ on shipmentre0_.CONSIGNEES_ID =
>> consignees6_.FIN_ID
>> inner join TBLS_WORKFLOW_STATES workflowst7_ on
>> shipmentre0_.SHIPMENT_STATUS_ID = workflowst7_.FIN_ID
>> inner join TBLS_WORKFLOW_STATES workflowst8_ on
>> shipmentre0_.SHIPMENT_CHARGE_STATUS = workflowst8_.FIN_ID
>> inner join TBLS_WORKFLOW_STATES workflowst9_ on
>> shipmentre0_.SHIPMENT_DOCUMENT_STATUS = workflowst9_.FIN_ID
>> inner join TBLS_WORKFLOW_STATES workflowst10_ on
>> shipmentre0_.VAULT_STATUS_ID = workflowst10_.FIN_ID
>> left outer join TBLS_SHIPMENT_METHODS shipmentme11_ on
>> shipmentre0_.SHIPMENT_METHOD_ID = shipmentme11_.FIN_ID
>> left outer join TBLS_BANK_NOTES_DEALS_LEGS deallegs12_ on
>> shipmentre0_.FIN_ID = deallegs12_.SHIPMENT_RECORDS_ID
>> where (shipmentro1_.LEG_NO = (select min(shipmentro13_.LEG_NO)
>> from TBLS_SHIPMENT_RECORD_ROUTING shipmentro13_
>> where shipmentre0_.FIN_ID = shipmentro13_.SHIPMENT_RECORD_ID
>> and ((shipmentro13_.IS_DELETED = 'N'))))
>> and (shipmentre0_.IS_DELETED = 'N')
>> and (TO_CHAR(shipmentro1_.ARRIVAL_DATE, 'YYYY-MM-DD') <= '2019-08-29')
>> order by shipmentre0_.SHIPMENT_DATE
>> limit 25
>> ;
>>
>>
>> On Mon, Sep 9, 2019 at 2:00 PM yash mehta <yash215(at)gmail(dot)com> wrote:
>>
>>> We have a query that takes 1min to execute in postgres 10.6 and the same
>>> executes in 4 sec in Oracle database. The query is doing 'select distinct'.
>>> If I add a 'group by' clause, performance in postgres improves
>>> significantly and fetches results in 2 sec (better than oracle). But
>>> unfortunately, we cannot modify the query. Could you please suggest a way
>>> to improve performance in Postgres without modifying the query.
>>>
>>> *Original condition: time taken 1min*
>>>
>>> Sort Method: external merge Disk: 90656kB
>>>
>>>
>>>
>>> *After removing distinct from query: time taken 2sec*
>>>
>>> Sort Method: top-N heapsort Memory: 201kB
>>>
>>>
>>>
>>> *After increasing work_mem to 180MB; it takes 20sec*
>>>
>>> Sort Method: quicksort Memory: 172409kB
>>>
>>>
>>>
>>> SELECT * FROM pg_stat_statements ORDER BY total_time DESC limit 1;
>>>
>>> -[ RECORD 1
>>> ]-------+-----------------------------------------------------------------------------------------------------------------------------------------
>>>
>>> userid | 174862
>>>
>>> dbid | 174861
>>>
>>> queryid | 1469376470
>>>
>>> query | <query is too long. It selects around 300 columns>
>>>
>>> calls | 1
>>>
>>> total_time | 59469.972661
>>>
>>> min_time | 59469.972661
>>>
>>> max_time | 59469.972661
>>>
>>> mean_time | 59469.972661
>>>
>>> stddev_time | 0
>>>
>>> rows | 25
>>>
>>> shared_blks_hit | 27436
>>>
>>> shared_blks_read | 2542
>>>
>>> shared_blks_dirtied | 0
>>>
>>> shared_blks_written | 0
>>>
>>> local_blks_hit | 0
>>>
>>> local_blks_read | 0
>>>
>>> local_blks_dirtied | 0
>>>
>>> local_blks_written | 0
>>>
>>> temp_blks_read | 257
>>>
>>> temp_blks_written | 11333
>>>
>>> blk_read_time | 0
>>>
>>> blk_write_time | 0
>>>
>>
> IMO, an explain analyze of the query would be useful in order for people
> to help you.
>
> e.g. https://explain.depesz.com
>
> Regards,
> Flo
>