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