optimizing Postgresql query with left join and indexes

Question

I am trying to run the following query but it is taking forever to execute:

Select Distinct ON (S1.sql_id)
 S1.msgdata as replymg,
 S1.time as replytme,
 S2.receiver,
 S2.time,
 S2.msgdata,
 S1.sql_id as repsql_id ,
 S2.account,
 S2.identifier,
 S2.businessunit 
FROM sql_reciept_sms S2
 LEFT JOIN sql_reply_reciept_sms S1 
 ON S2.receiver=S1.sender AND S1.smsc_id=S2.smsc_id
WHERE S2.userid = '[email protected]' 
 AND S1.time BETWEEN '2020-05-28 00:00:00' AND '2020-05-29 00:00:00'
 Order By S1.sql_id, S2.time desc;

The database used: PostgreSQL 10.3

OS VERSION: CentOS 7

table Structure:

sql_reply_reciept_sms

 Table "public.sql_reply_reciept_sms"
 Column | Type | Collation | Nullable | Default
-------------------------+-----------------------------+-----------+----------+---------
 sql_id | integer | | not null |
 account | character varying(255) | | |
 alt_dcs | numeric(19,2) | | |
 binfo | character varying(255) | | |
 boxc_id | character varying(255) | | |
 charset | character varying(255) | | |
 client_track_id | character varying(255) | | |
 coding | numeric(19,2) | | |
 compress | numeric(19,2) | | |
 deferred | numeric(19,2) | | |
 delemail | character varying(255) | | |
 dlr_mask | numeric(19,2) | | |
 dlr_mask_del_failure | numeric(19,2) | | |
 dlr_mask_del_success | numeric(19,2) | | |
 dlr_mask_msg_buf | numeric(19,2) | | |
 dlr_mask_smsc_rej | numeric(19,2) | | |
 dlr_mask_smsc_sub | numeric(19,2) | | |
 dlr_url | character varying(255) | | |
 dsm | character varying(255) | | |
 foreign_id | character varying(255) | | |
 gctid | character varying(255) | | |
 gstid | character varying(255) | | |
 id | numeric(19,2) | | |
 identifier | character varying(255) | | |
 mclass | numeric(19,2) | | |
 meta_data | character varying(255) | | |
 mno | character varying(255) | | |
 msg_cost | character varying(255) | | |
 msgdata | text | | |
 mwi | numeric(19,2) | | |
 nextdayearliestsendtime | character varying(255) | | |
 pid | numeric(19,2) | | |
 receiptcallback | character varying(255) | | |
 receiver | character varying(255) | | |
 replycallback | character varying(255) | | |
 replyemail | character varying(255) | | |
 replyemailflag | character varying(255) | | |
 replymsg | character varying(255) | | |
 replytime | timestamp without time zone | | |
 requestresptime | character varying(255) | | |
 rpi | numeric(19,2) | | |
 sendreport | character varying(255) | | |
 sender | character varying(255) | | |
 sentemail | character varying(255) | | |
 service | character varying(255) | | |
 smsid | character varying(255) | | |
 sms_type | numeric(19,2) | | |
 smsc_id | character varying(255) | | |
 stid | character varying(255) | | |
 time | timestamp without time zone | | |
 time_del_failure | timestamp without time zone | | |
 time_del_success | timestamp without time zone | | |
 time_msg_buf | timestamp without time zone | | |
 time_smsc_rej | timestamp without time zone | | |
 time_smsc_sub | timestamp without time zone | | |
 udhdata | character varying(255) | | |
 userid | character varying(255) | | |
 validity | numeric(19,2) | | |
 replyfetched | boolean | | |

sql_reciept_sms

 Table "public.sql_reciept_sms"
 Column | Type | Collation | Nullable | Default
-------------------------+-----------------------------+-----------+----------+--------------------------------
 rec_id | integer | | not null | nextval('foo_a_seq'::regclass)
 status | numeric(19,2) | | |
 account | character varying(255) | | |
 alt_dcs | numeric(19,2) | | |
 binfo | text | | |
 boxc_id | character varying(255) | | |
 charset | character varying(255) | | |
 client_track_id | character varying(255) | | |
 coding | numeric(19,2) | | |
 compress | numeric(19,2) | | |
 deferred | numeric(19,2) | | |
 delemail | character varying(255) | | |
 dlr_mask | numeric(19,2) | | |
 dlr_mask_del_failure | numeric(19,2) | | |
 dlr_mask_del_success | numeric(19,2) | | |
 dlr_mask_msg_buf | numeric(19,2) | | |
 dlr_mask_smsc_rej | numeric(19,2) | | |
 dlr_mask_smsc_sub | numeric(19,2) | | |
 dlr_url | text | | |
 dsm | character varying(255) | | |
 foreign_id | character varying(255) | | |
 gctid | character varying(255) | | |
 gstid | character varying(255) | | |
 id | numeric(19,2) | | |
 identifier | character varying(255) | | |
 mclass | numeric(19,2) | | |
 meta_data | character varying(255) | | |
 mno | character varying(255) | | |
 msg_cost | character varying(255) | | |
 msgdata | text | | |
 mwi | numeric(19,2) | | |
 nextdayearliestsendtime | character varying(255) | | |
 pid | numeric(19,2) | | |
 receiptcallback | character varying(255) | | |
 receiver | character varying(255) | | |
 replycallback | character varying(255) | | |
 replyemail | character varying(255) | | |
 replyemailflag | character varying(255) | | |
 replymsg | character varying(255) | | |
 replytime | timestamp without time zone | | |
 requestresptime | character varying(255) | | |
 rpi | numeric(19,2) | | |
 sendreport | character varying(255) | | |
 sender | character varying(255) | | |
 sentemail | character varying(255) | | |
 servertime | timestamp without time zone | | |
 service | character varying(255) | | |
 showfinalstatus | boolean | | |
 smsid | character varying(255) | | |
 sms_type | numeric(19,2) | | |
 smsc_id | character varying(255) | | |
 stid | character varying(255) | | |
 time | timestamp without time zone | | |
 time_del_failure | timestamp without time zone | | |
 time_del_success | timestamp without time zone | | |
 time_msg_buf | timestamp without time zone | | |
 time_smsc_rej | timestamp without time zone | | |
 time_smsc_sub | timestamp without time zone | | |
 udhdata | character varying(255) | | |
 userid | character varying(255) | | |
 validity | numeric(19,2) | | |
 statusMessage | character varying(255) | | |
 statusmessage | character varying(255) | | |
 sentfrom | character varying(255) | | |
 hostip | character varying(255) | | |
 emailid | character varying(255) | | |
 clientref | character varying(255) | | |
 businessunit | character varying(255) | | |

The size of two tables are:

• sql_reply_reciept_sms: 665428 rows
• sql_reciept_sms: 2823449 rows

the indexes on both tables are:

enter image description here

Query Analysis

enter image description here

• CL.: Please add the information mentioned in Asking query performance questions.
• mustaccio: For one, use inner join instead of an outer, because the latter is pointless in your query.

Answer 1

Amazing. You have that many indexes on sql_reciept_sms (many of which are certainly superfluous), but you still managed to forget the crucial index for this query:

CREATE INDEX ON sql_reciept_sms (userid);

• Mudit Agrawal: This improved performance significantly although performance is inconsistent.