Old question, but i'll put in my two cents.
The issue is when you join different page types. The Hierarchy Viewer does joins on the tables (the more page times, the larger the table), and sends all that from database over to the client and THEN applies the columns. This can result in megabytes of data sent over.
ALthough i'm kicking myself becuase i can't find the query i wrote, i ended up using this query that was showing so large, and rewriting a custom query that only selected the columns i needed, and set "null" for the column variations and just did a UNION ALL (much faster) and applied the where conditions, etc manually. This meant it did the logic on SQL, and only sent the results, not the entire join and apply reuslts later.
I really must make an updated Hierarchy viewer to automatically handle this better...