Redshift combine two queries

Question

Consider the following queries to get counts:

Query A

SELECT
    COUNT(*)
FROM
    "user_notifications"
WHERE
    "user_notifications"."source_id" = 5196
    AND "user_notifications"."source_type" = 'MassGifting'
    AND "user_notifications"."status" = 'sent'
    AND "user_notifications"."read_at" IS NULL

Executation details: This takes about 6-10seconds.

Query B Its also on the same table, just where clause is slightly different, trying to check sounds of notifications based on their status, if they have been read based on source_id & source_type.:

    SELECT
    COUNT(*)
FROM
    "user_notifications"
WHERE
    "user_notifications"."source_id" = 5196
    AND "user_notifications"."source_type" = 'MassGifting'
    AND (
        "user_notifications"."read_at" IS NOT NULL
    )

Execution Details: Time taken: 5-6seconds. In total is takes about 30-60seconds to execute these two queries & render the report on our website.

I was wondering what are the way which we can speed this up?

How much data do you have in your user_notifications table? Do you have any sort key or dist key? Without sort key in where condition, it is quite difficult to speed up. — Rahul Gupta

Rahul Gupta Rahul Gupta · Accepted Answer · 2017-04-14T14:04:00

Try below query, and let me know how much time does it take?
It should give you the result for both the queries in 1 table scan.

SELECT
    SUM(case when        
    "user_notifications"."status" = 'sent'
    AND "user_notifications"."read_at" IS NULL
    then 1 else 0 end) as Result1,
    SUM(case when        
    "user_notifications"."read_at" IS NOT NULL
    then 1 else 0 end) as Result2,
FROM
    "user_notifications"
WHERE
    "user_notifications"."source_id" = 5196
    AND "user_notifications"."source_type" = 'MassGifting'

If I’ve made a bad assumption please comment and I’ll refocus my answer.

Redshift combine two queries

1 Answers