Is there a faster way to do an inner join with an OR clause. I have a query that looks something like this:

INNER JOIN (select u_id, b_id from t1)
  AS t2
  ON t2.u_id = t1.u_id
  OR (t2.b_id = t1.b_id AND t1.u_id is null)

but it takes over 6 seconds.

I think there's a way to replace the OR with two joins, but I could use a hand figuring it out.


Here's a SQL fiddle of my use case: http://sqlfiddle.com/#!12/cee6e/2

Basically, I'm looking for user_id and browser_id of an event with the name 'foo' (and in a specific time range) that happened sometime before 'bar'. First I search for the 'foo' events in a specific date range, and then join those onto the 'bar' events by matching the user_ids, or if no user_id, using the browser_ids.

One data constraint: Any events with matching browser_ids will also have matching user_ids (or nulls).

  • 1
    explain analyze ... – Craig Ringer Oct 16 '14 at 23:20
  • @CraigRinger explain.depesz.com/s/VM6Z – Sam Oct 16 '14 at 23:27
  • 1
    You'll need to translate 't1' and 't2' and the col names to those in the explain, too, otherwise there's no way to match what you're talking about above with the plan you showed. – Craig Ringer Oct 16 '14 at 23:31
  • @CraigRinger added the full query to the question. – Sam Oct 16 '14 at 23:46
  • 1
    Please provide table definitions and an explanation in plain English what you are trying to achieve. And table-qualify columns in your query, so we can tell where each column is coming from. Ideally, add an SSCCE with sample data and desired result, for instance in an SQL Fiddle. I am pretty sure your query can be rewritten to be dramatically faster. – Erwin Brandstetter Oct 19 '14 at 23:00

For starters, I suspect your current query is incorrect, missing an additional condition AND v.user_id is null. Your query, simplified with table aliases and improved somewhat:

SELECT DISTINCT ON (coalesce(e.user_id, e.browser_id), e.user_id)
       e.browser_id, e.user_id, v.time
FROM   events e
   SELECT DISTINCT ON (coalesce(user_id, browser_id), user_id)
          user_id, browser_id, time
   FROM   events         e
   LEFT   JOIN urls      u ON u.event_id = e.id
   LEFT   JOIN params    p ON p.url_id = u.id
   LEFT   JOIN referrers r ON r.event_id = e.id
   WHERE  e.project_id = 1
   AND    e.time >= '2014-09-16 07:00:00+0'
   AND    e.time <  '2014-10-16 07:00:00+0'
   ORDER  BY coalesce(user_id, browser_id), user_id, time
   ) v ON v.user_id = e.user_id
       OR v.browser_id = e.browser_id AND e.user_id IS NULL AND v.user_id IS NULL
WHERE  v.time <= e.time
AND    e.name = 'viewed-cart'
ORDER  BY coalesce(e.user_id, e.browser_id), e.user_id, v.time;

Else, you would join to all rows with the same browser_id where user_id IS NULL, even to rows with a non-null user_id. This may also be part of the performance problem - it is most probably a problem of correctness.

Your final ORDER BY item v.time is also a dubious choice for a tie breaker ...

And I replaced your condition AND events.time < '2014-10-16 06:59:59.999000+0000' with a sane alternative.

As commented, I suspect this can be completely rewritten, but I am not going to go deeper without complete information.

| improve this answer | |
  • I added an SQL fiddle: sqlfiddle.com/#!12/e5a7a/1 However, it's hard to see the performance issue I'm having in sql fiddle because there isn't much data. – Sam Oct 20 '14 at 6:35

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.