5

I have four tables to pull information from user: first_name, mongouser: email, card_status,transaction: transaction_type, balance, posted_at, is_atm, is_purchase, user_login: user_id, login_date, login_id...

Before I added the fourth table - user_login, everything was efficient. However, the fourth JOIN made everything slow. I wrote queries as shown below

SELECT * FROM (SELECT ssluserid, first_name, m.email, zipcode, date_part('year',age(birthday)) AS birthday, (current_date - DATE(created_date)) AS duration, CASE WHEN card_status = 'ACTIVE' THEN 1 ELSE 0 END AS IS_ACTIVE, SUM(CASE WHEN transaction_type = 'Credit' AND balance > 1.00 THEN balance END) AS LOAD_AMT, SUM(CASE WHEN transaction_type = 'Debit' AND balance > 1.00 THEN balance END) AS SPEND_AMT, COUNT(CASE WHEN transaction_type = 'Credit' AND balance > 1.00 THEN balance END) AS LOAD_CT, COUNT(CASE WHEN transaction_type = 'Debit' AND balance > 1.00 THEN balance END) AS SPEND_CT, MIN(CASE WHEN transaction_type = 'Credit' AND balance > 1.00 THEN DATE(posted_at) END) AS FIRST_LOAD, MAX(CASE WHEN transaction_type = 'Credit' AND balance > 1.00 THEN DATE(posted_at) END) AS LAST_LOAD, MIN(CASE WHEN transaction_type = 'Debit' AND balance > 1.00 THEN DATE(posted_at) END) AS FIRST_SPEND, MAX(CASE WHEN transaction_type = 'Debit' AND balance > 1.00 THEN DATE(posted_at) END) AS LAST_SPEND, SUM(CASE WHEN transaction_type = 'Debit' AND is_atm = 't' AND DATE(posted_at) >= CURRENT_DATE - INTERVAL '90 days' THEN balance END) AS ATM_AMT, SUM(CASE WHEN transaction_type = 'Debit' AND is_purchase = 't' AND DATE(posted_at) >= CURRENT_DATE - INTERVAL '90 days' THEN balance END) AS POS_AMT, SUM(CASE WHEN transaction_type = 'Credit' AND balance > 1.00 AND DATE(posted_at) >= CURRENT_DATE - INTERVAL '90 days' THEN balance END) AS LOAD_VOL, COUNT(CASE WHEN DATE(login_date) >= CURRENT_DATE - INTERVAL '90 days' THEN login_id END) AS CT_LOGIN FROM mongouser m LEFT OUTER JOIN user u ON m.userid = u.id LEFT OUTER JOIN transactions t ON u.id = t.user_id LEFT OUTER JOIN user_login l ON m.userid = l.user_id GROUP BY 1,2,3,4,5,6,7) t WHERE LAST_LOAD >= CURRENT_DATE - INTERVAL '90 days' ORDER BY 9 DESC;

This query has been running for almost 40 min...Are there any ways to optimize it?

Improve this question
2
  • Yes, there are plenty of ways to optimise it. You could use EXPLAIN to get a report on where the big costs are in your query and determine if there are any places that would benefit from better use of indexes, you could alter the table's indexes, you could limit the number of columns you fetch or the number of rows your fetch, etc. Or you could try removing the Debit/Credit flag and having debits stored as negative amounts, then you could eliminate all that CASE stuff. Have you tried to find any optimisations yourself or at least research where your query is being held up? Commented Dec 12, 2016 at 20:18
  • @GordonM thank you for the tips! Commented Dec 12, 2016 at 20:22

1 Answer 1

Reset to default
4

Focusing on just your statements you know where the problem is. You had this before

LEFT OUTER JOIN user u ON m.userid = u.id

And you say things "weren't slow." Then you add this,

LEFT OUTER JOIN user_login l ON m.userid = l.user_id

And you say things get slow. It's likely that you have an index on m.userid. Do you have an index on l.user_id?

CREATE INDEX foo ON user_login ( user_id );
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

you are right. the userid in mongouser table doesn't have an index on it while the user_id in user_login table has an index. However could you please explain more why adding user_login table could make everything slow down? it was 22 seconds before adding this table in.
22 seconds is already slow. I have no idea of the user, but if you don't have an index you have to run through ever table and join them in a nested loop (if ordered), or join them in a bitmap join. Both are slow.
Are there ways to optimize this query except for creating index? Sadly, as an analyst, i have no permission creating index on tables...
No. Ask for permission.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.