Joining 3 tables efficiently
atomix
✭
I am trying to join 3 tables in the same query. 1 larger fact table and 2 smaller dimension tables.
However, what I find is that the query uses up all the memory, as the first join between 1 dimension table (mlog.tmp_stg_xmode_dids_lookup_complete_b0) and the fact (dev.stg_xmode_log_b0) table will become the inner part of the next hash join, which then doesn't fit in the memory.
Is there a way to hint an optimizer that the result of the first join should be the OUTER part of the 2nd join, and user the 3rd table as the inner (mlog.d_user_agent_hash_user_agent40) ?
Thanks for any suggestions. Note that there are no statistics on the tables as they are populated as part of the transaction and wouldn't want to commit the transaction yet (ANALYZE_STATISTICS commits a transaction)
Access Path:
+-JOIN HASH [RightOuter] [Cost: 4M, Rows: 1 (NO STATISTICS)] (PATH ID: 1)
| Join Cond: (e.hash_user_agent40 = a.hash_user_agent40) AND (e.user_agent = a.user_agent)
| Materialize at Input: e.hash_did8, e.did, e.country, e.region, e.city, e.device_type, e.ts, e.ip, e.lat, e.lon, e.haccuracy, e.region2, e.zip_code, e.user_agent, e.hash_user_agent40, e.publisher_id
| Execute on: All Nodes
| +-- Outer -> STORAGE ACCESS for a [Cost: 355K, Rows: 4M] (PATH ID: 2)
| | Projection: mlog.d_user_agent_hash_user_agent40
| | Materialize: a.hash_user_agent40, a.user_agent, a.ua_id
| | Execute on: All Nodes
| | Runtime Filters: (SIP1(HashJoin): a.hash_user_agent40), (SIP2(HashJoin): a.user_agent), (SIP3(HashJoin): a.hash_user_agent40, a.user_agent)
| +-- Inner -> JOIN MERGEJOIN(inputs presorted) [Cost: 4M, Rows: 1 (NO STATISTICS)] (PATH ID: 3)
| | Join Cond: (e.hash_did8 = d.hash_did8) AND (e.did = d.did)
| | Execute on: All Nodes
| | +-- Outer -> STORAGE ACCESS for e [Cost: 3M, Rows: 1B] (PATH ID: 4)
| | | Projection: dev.stg_xmode_log_b0
| | | Materialize: e.hash_did8, e.did
| | | Filter: ((e.did IS NOT NULL) AND (e.did <> '00000000-0000-0000-0000-000000000000'))
| | | Filter: ((e.ts IS NOT NULL) AND (e.ts <= '2018-10-31 00:32:29.36579'::timestamp))
| | | Filter: ((e.lat >= (-90)) AND (e.lat <= 90))
| | | Filter: ((e.lon >= (-180)) AND (e.lon <= 180))
| | | Execute on: All Nodes
| | | Runtime Filters: (SIP4(MergeJoin): e.hash_did8), (SIP5(MergeJoin): e.did), (SIP6(MergeJoin): e.hash_did8, e.did)
| | +-- Inner -> STORAGE ACCESS for d [Cost: 1, Rows: 1 (NO STATISTICS)] (PATH ID: 5)
| | | Projection: mlog.tmp_stg_xmode_dids_lookup_complete_b0
| | | Materialize: d.did, d.hash_did8, d.device_id
| | | Filter: ((d.did IS NOT NULL) AND (d.did <> '00000000-0000-0000-0000-000000000000'))
| | | Execute on: All Nodes
Comments
You can instruct the optimizer to plan a query with a specified join ordering using a query hint - SYNTACTIC_JOIN. See documentation for more details - https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/SQLReferenceManual/LanguageElements/Hints/SyntacticJoin.htm
Ah, this is exactly what I was looking for, thank you! Query now works like a charm, runs on 25G of RAM without spilling to disk and much faster!