I'm on Postgres 9.5 and working with a web analytics database that records visitor traffic.
I'm trying to optimize a very slow query that gives me a count of the unique people that visited a given page grouped by their session device type.
The query looks like so:
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 on (O.session_id = S.session_id) join alias as A1 on ( A1.alias = S.person_alias and A.person_id = A1.person_id) where O.timestamp_ between timestamp '2017-02-11 23:22:20.146' and timestamp '2017-03-13 23:22:20.146' and O.location_host = 'www.foo.bar.ca' and S.first_seen between timestamp '2017-02-11 23:22:20.146' and timestamp '2017-03-13 23:22:20.146' ) as alias_120134400 group by groupname
The above SQL runs in over 2.5 minutes and the output looks like this.
device type count -------------------------- Computer | 163304 Game console | 41 Mobile | 33519 Tablet | 10465 Unknown | 5
There are a couple of peculiarities about the schema and the above query, I need to point out:
- The event_page table is a union of monthly tables
- Joining the alias table twice is necessary because an alias may change during a session. aliases to personIDs are many to one. Hence the page view alias and session alias may be different but point to the same personID.
- All statistics are up to date (everything vacuum analyzed)!
As the explain below shows it appears that the Quick Sort is the bulk of my problem:
In addition to that, there is a seq scan occurring (line 23 in the explain) on the session table yet that condition should be easily handled by the index.
I've run the same query on a different (albeit smaller) dataset and the query plan seems more sensible. This one executes in only 3 seconds. Here is the explanation:
Any ideas as to why the former explains chooses quick sort and the latter use HashAaggregate to achieve the DISTINCT?
What can I do to optimize the query and avoid quick sorting?
Why (in both cases) is the planner choosing a seq scan of the session when that filter is covered by an index?
Thanks in advance for any help.