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 rowssql_reciept_sms: 2823449 rows
the indexes on both tables are:
Query Analysis
inner joininstead of an outer, because the latter is pointless in your query.