1

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 = 'bulksms@pg.co.za'  
  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

2
  • Please add the information mentioned in Asking query performance questions.
    – CL.
    May 28, 2020 at 8:56
  • For one, use inner join instead of an outer, because the latter is pointless in your query.
    – mustaccio
    May 28, 2020 at 14:15

1 Answer 1

0

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);
1
  • This improved performance significantly although performance is inconsistent. May 28, 2020 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.