I'm using Postgres 9.6 on AWS

I've run into an issue where depending on the data range for the query the planner will chose to do merge joins (requiring slower sort operations). When the date interval for the query is 2 months it does a hash join.

set enable_mergejoin = off forces the planner back to hashjoin but is not something I want to do in production.

The query in question looks like this:

select  groupname,  count(person_id) as thecount
from (
  select distinct  S.first_device_type  as groupname, A.person_id
  from event_page as O
    join alias as A
    on (O.person_alias = A.alias)
    left outer join (
      session as S
        join alias as A1
        on (A1.alias = S.person_alias)
    on (
      (O.session_id = S.session_id)
      and (A.person_id = A1.person_id)
      and S.first_seen between '2017-01-12 23:24:57.896' and  '2017-04-13 22:24:57.896'
    O.timestamp_ between '2017-01-12 23:24:57.896' and '2017-04-13 22:24:57.896'
) as B
group by groupname;

The above query is from analytics database and is joining page views to sessions and counting the unique people that used a given device type.

When I run the above query for two month I get this plan: https://explain.depesz.com/s/tREc

When I run the above query for four months I get this plan:https://explain.depesz.com/s/yqV

Why does the planner choose the less efficient (in this case) merge join which requires sorting for the longer query? Is there anything I can do to optimize the above query without doing set enable_mergejoin = off;?

  • 2
    Please add an explain analyze plan of the 4 month query that was run with set enable_mergejoin = off, as well. – jjanes Apr 14 '17 at 1:07
  • Its the same plan as the two month query, it uses hashjoin – maxTrialfire Apr 17 '17 at 17:13
  • But the costs estimates will (presumably) be different for 4 months versus 2. We want to compare apples to apples. – jjanes Apr 18 '17 at 18:04

Your Answer

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

Browse other questions tagged or ask your own question.