Redshift slow sub query (if it includes certain columns)

Question

I have a query as follows:

SELECT
    e.event_id,
    (
        SELECT
            event_id
        FROM atomic.events
        WHERE
            domain_userid = e.domain_userid
        ORDER BY collector_tstamp
        LIMIT 1
    ) AS parent_event_id
FROM snowplow_intermediary.events_enriched e
LIMIT 1

I'm trying to find the first event for each user. This is fairly fast ~5s. If I'm trying to find the user by user_ipaddress instead of domain_userid it's way slower. After 300s it's not done.

SELECT
    e.event_id,
    (
        SELECT
            event_id
        FROM atomic.events
        WHERE
            user_ipaddress = e.user_ipaddress
        ORDER BY collector_tstamp
        LIMIT 1
    ) AS parent_event_id
FROM snowplow_intermediary.events_enriched e
LIMIT 1

The data type is domain_userid varchar(36) encode runlength and user_ipaddress varchar(45) encode runlength.

Here's EXPLAIN's for the queries:

https://gist.github.com/mortenstarfly/4ce3be9b3a19aac2601a

https://gist.github.com/mortenstarfly/2008b0f737259df30695

I really would like to speed up the second query. Any suggestions?

Your execution plans look way more complicated than your queries. — Gordon Linoff, Jun 01 '15 at 20:18
I agree - but I'm not too familiar with Redshift query plans and can't seem to find any help there — Morten Petersen, Jun 01 '15 at 20:40
You can use window function: http://docs.aws.amazon.com/redshift/latest/dg/r_WF_first_value.html — Guy, Jun 02 '15 at 21:50
I don't see how it can be that long for such a small difference (same encoding, rougfly same size). Any update would be of huge interest. — Boris Charpentier, Aug 03 '16 at 21:37

score -1 · Answer 1 · answered Jul 02 '17 at 11:18

It could be because of your short key..if your data is sorted based on user id then data will be retrieved fast(for the first query) as redshifts will know in which segment your data is residing (based on zone map) and can skip lot of slices and your io will be significantly low.

Redshift slow sub query (if it includes certain columns)

1 Answers1