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' ) where 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;?