Query Optimization

<h2 id="query-optimization-in-geode" class="position-relative d-flex align-items-center group"> Query Optimization in Geode <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="query-optimization-in-geode" aria-haspopup="dialog" aria-label="Share link: Query Optimization in Geode"> Share link </button> </h2><div id="headingShareModal" class="heading-share-modal" role="dialog" aria-modal="true" aria-labelledby="headingShareTitle" hidden> <div class="hsm-dialog" role="document"> <div class="hsm-header"> <h2 id="headingShareTitle" class="h6 mb-0 fw-bold">Share this section</h2> <button type="button" class="hsm-close" aria-label="Close"> </button> </div> <div class="hsm-body"> <label for="headingShareInput" class="form-label small text-muted mb-1 text-uppercase fw-bold" style="font-size: 0.7rem; letter-spacing: 0.5px;">Permalink</label> <div class="input-group mb-4 hsm-url-group"> <input id="headingShareInput" type="text" class="form-control font-monospace" readonly aria-readonly="true" style="font-size: 0.85rem;" /> <button class="btn btn-primary hsm-copy" type="button" aria-label="Copy" title="Copy"> </button> </div> <div class="small fw-bold mb-2 text-muted text-uppercase" style="font-size: 0.7rem; letter-spacing: 0.5px;">Share via</div> <div class="hsm-share-grid"> <a id="share-twitter" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> Twitter </a> <a id="share-linkedin" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> LinkedIn </a> <a id="share-facebook" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> Facebook </a> </div> </div> </div> </div> <style> .heading-share-modal { position: fixed; inset: 0; display: flex; justify-content: center; align-items: center; background: rgba(0, 0, 0, 0.6); z-index: 1050; padding: 1rem; backdrop-filter: blur(4px); -webkit-backdrop-filter: blur(4px); } .heading-share-modal[hidden] { display: none !important; } .hsm-dialog { max-width: 420px; width: 100%; background: var(--bs-body-bg, #fff); color: var(--bs-body-color, #212529); border: 1px solid var(--bs-border-color, rgba(0,0,0,0.1)); border-radius: 1rem; box-shadow: 0 25px 50px -12px rgba(0, 0, 0, 0.25); overflow: hidden; animation: hsm-fade-in 0.2s ease-out; } @keyframes hsm-fade-in { from { opacity: 0; transform: scale(0.95); } to { opacity: 1; transform: scale(1); } } [data-bs-theme="dark"] .hsm-dialog { background: #1e293b; border-color: rgba(255,255,255,0.1); color: #f8f9fa; } .hsm-header { display: flex; justify-content: space-between; align-items: center; padding: 1rem 1.5rem; border-bottom: 1px solid var(--bs-border-color, rgba(0,0,0,0.1)); background: rgba(0,0,0,0.02); } [data-bs-theme="dark"] .hsm-header { background: rgba(255,255,255,0.02); border-color: rgba(255,255,255,0.1); } .hsm-close { background: transparent; border: none; color: inherit; opacity: 0.5; padding: 0.25rem 0.5rem; border-radius: 0.25rem; font-size: 1.2rem; line-height: 1; transition: opacity 0.2s; } .hsm-close:hover { opacity: 1; } .hsm-body { padding: 1.5rem; } .hsm-url-group { display: flex !important; align-items: stretch; } .hsm-url-group .form-control { flex: 1; min-width: 0; margin: 0; background: var(--bs-secondary-bg, #f8f9fa); border-color: var(--bs-border-color, #dee2e6); border-top-right-radius: 0; border-bottom-right-radius: 0; height: 42px; } .hsm-url-group .btn { flex: 0 0 auto; margin: 0; margin-left: -1px; border-top-left-radius: 0; border-bottom-left-radius: 0; height: 42px; display: flex; align-items: center; justify-content: center; padding: 0 1.25rem; z-index: 2; } [data-bs-theme="dark"] .hsm-url-group .form-control { background: #0f172a; border-color: #334155; color: #e2e8f0; } .hsm-share-grid { display: flex; flex-direction: column; gap: 0.5rem; } .hsm-share-grid .btn { display: flex; align-items: center; justify-content: center; font-size: 0.9rem; padding: 0.6rem; border-color: var(--bs-border-color); width: 100%; } [data-bs-theme="dark"] .hsm-share-grid .btn { color: #e2e8f0; border-color: #475569; } [data-bs-theme="dark"] .hsm-share-grid .btn:hover { background: #334155; border-color: #cbd5e1; } </style> <script> (function(){ const modal = document.getElementById('headingShareModal'); if(!modal) return; const input = modal.querySelector('#headingShareInput'); const copyBtn = modal.querySelector('.hsm-copy'); const twitter = modal.querySelector('#share-twitter'); const linkedin = modal.querySelector('#share-linkedin'); const facebook = modal.querySelector('#share-facebook'); const closeBtn = modal.querySelector('.hsm-close'); let lastFocus=null; let trapBound=false; function buildUrl(id){ return window.location.origin + window.location.pathname + '#' + id; } function isOpen(){ return !modal.hasAttribute('hidden'); } function hydrate(id){ const url=buildUrl(id); input.value=url; const enc=encodeURIComponent(url); const text=encodeURIComponent(document.title); if(twitter) twitter.href=`https://twitter.com/intent/tweet?url=${enc}&text=${text}`; if(linkedin) linkedin.href=`https://www.linkedin.com/sharing/share-offsite/?url=${enc}`; if(facebook) facebook.href=`https://www.facebook.com/sharer/sharer.php?u=${enc}`; } function openModal(id){ lastFocus=document.activeElement; hydrate(id); if(!isOpen()){ modal.removeAttribute('hidden'); } requestAnimationFrame(()=>{ input.focus(); }); trapFocus(); } function closeModal(){ if(!isOpen()) return; modal.setAttribute('hidden',''); if(lastFocus && typeof lastFocus.focus==='function') lastFocus.focus(); } function copyCurrent(){ try{ navigator.clipboard.writeText(input.value).then(()=>feedback(true),()=>fallback()); } catch(e){ fallback(); } } function fallback(){ input.select(); try{ document.execCommand('copy'); feedback(true);}catch(e){ feedback(false);} } function feedback(ok){ if(!copyBtn) return; const icon=copyBtn.querySelector('i'); if(!icon) return; const prev=copyBtn.getAttribute('data-prev')||icon.className; if(!copyBtn.getAttribute('data-prev')) copyBtn.setAttribute('data-prev',prev); icon.className= ok ? 'fa-duotone fa-clipboard-check':'fa-duotone fa-circle-exclamation'; setTimeout(()=>{ icon.className=prev; },1800); } function handleShareClick(e){ e.preventDefault(); const btn=e.currentTarget; const id=btn.getAttribute('data-share-target'); if(id) openModal(id); } function bindShareButtons(){ document.querySelectorAll('.h-share').forEach(btn=>{ if(!btn.dataset.hShareBound){ btn.addEventListener('click', handleShareClick); btn.dataset.hShareBound='1'; } }); } bindShareButtons(); if(document.readyState==='loading'){ document.addEventListener('DOMContentLoaded', bindShareButtons); } else { requestAnimationFrame(bindShareButtons); } document.addEventListener('click', function(e){ const shareBtn=e.target.closest && e.target.closest('.h-share'); if(shareBtn && !shareBtn.dataset.hShareBound){ handleShareClick.call(shareBtn, e); } }, true); document.addEventListener('click', e=>{ if(e.target===modal) closeModal(); if(e.target.closest && e.target.closest('.hsm-close')){ e.preventDefault(); closeModal(); } if(copyBtn && (e.target===copyBtn || (e.target.closest && e.target.closest('.hsm-copy')))) { e.preventDefault(); copyCurrent(); } }); document.addEventListener('keydown', e=>{ if(e.key==='Escape' && isOpen()) closeModal(); }); function trapFocus(){ if(trapBound) return; trapBound=true; modal.addEventListener('keydown', f=>{ if(f.key==='Tab' && isOpen()){ const focusable=[...modal.querySelectorAll('a[href],button,input,textarea,select,[tabindex]:not([tabindex="-1"])')].filter(el=>!el.hasAttribute('disabled')); if(!focusable.length) return; const first=focusable[0]; const last=focusable[focusable.length-1]; if(f.shiftKey && document.activeElement===first){ f.preventDefault(); last.focus(); } else if(!f.shiftKey && document.activeElement===last){ f.preventDefault(); first.focus(); } } }); } if(closeBtn) closeBtn.addEventListener('click', e=>{ e.preventDefault(); closeModal(); }); })(); </script>Query optimization is the process of analyzing and transforming database queries to execute them as efficiently as possible. In graph databases like Geode, optimization is particularly challenging due to the variable and unpredictable nature of graph traversals. Effective optimization can mean the difference between queries completing in milliseconds versus timing out after minutes. <h3 id="introduction-to-query-optimization" class="position-relative d-flex align-items-center group"> Introduction to Query Optimization <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="introduction-to-query-optimization" aria-haspopup="dialog" aria-label="Share link: Introduction to Query Optimization"> Share link </button> </h3>Query optimization operates on multiple levels: <ol> <li>Logical Optimization: Rewriting queries to equivalent but more efficient forms</li> <li>Physical Optimization: Choosing the best execution strategy (indexes, join algorithms)</li> <li>Cost-Based Optimization: Estimating execution costs and selecting minimum-cost plans</li> <li>Runtime Optimization: Adapting execution based on intermediate results</li> </ol> For graph queries, additional challenges include: <ul> <li>Highly variable result cardinalities from relationship traversals</li> <li>Complex pattern matching with multiple possible start points</li> <li>Trade-offs between depth-first and breadth-first traversal strategies</li> <li>Balancing memory usage against computation time</li> </ul> <h3 id="geodes-query-optimizer" class="position-relative d-flex align-items-center group"> Geode&rsquo;s Query Optimizer <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="geodes-query-optimizer" aria-haspopup="dialog" aria-label="Share link: Geodes Query Optimizer"> Share link </button> </h3>Geode implements a sophisticated cost-based optimizer that analyzes GQL queries through several stages: <h4 id="query-parsing-and-analysis" class="position-relative d-flex align-items-center group"> Query Parsing and Analysis <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="query-parsing-and-analysis" aria-haspopup="dialog" aria-label="Share link: Query Parsing and Analysis"> Share link </button> </h4>The optimizer first parses the GQL query into an abstract syntax tree (AST) and performs semantic analysis: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Original query MATCH (u:User {country: 'USA'})-[:PURCHASED]->(p:Product) WHERE p.price > 100 AND p.category = 'Electronics' RETURN u.name, p.name ORDER BY p.price DESC; </code></pre></div>Parsed structure: <ol> <li>MATCH clause with pattern and filters</li> <li>WHERE clause with additional predicates</li> <li>RETURN clause specifying projections</li> <li>ORDER BY clause with sorting</li> </ol> <h4 id="logical-query-rewriting" class="position-relative d-flex align-items-center group"> Logical Query Rewriting <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="logical-query-rewriting" aria-haspopup="dialog" aria-label="Share link: Logical Query Rewriting"> Share link </button> </h4>The optimizer applies transformation rules to create equivalent but more efficient logical plans: Predicate Pushdown: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Before: Filter after traversal MATCH (u:User)-[:PURCHASED]->(p:Product) WHERE u.country = 'USA' AND p.price > 100 RETURN u.name, p.name; -- After: Filter before traversal (implicit transformation) MATCH (u:User {country: 'USA'})-[:PURCHASED]->(p:Product {price > 100}) RETURN u.name, p.name; </code></pre></div>Join Reordering: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Optimizer evaluates multiple join orders: -- Option 1: Start from User, expand to Product -- Option 2: Start from Product, expand to User -- Chooses based on selectivity and cardinality estimates </code></pre></div>Subquery Elimination: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Before: Correlated subquery MATCH (u:User) WHERE EXISTS { MATCH (u)-[:PURCHASED]->(p:Product {category: 'Electronics'}) } RETURN u.name; -- After: Joined pattern (more efficient) MATCH (u:User)-[:PURCHASED]->(:Product {category: 'Electronics'}) RETURN DISTINCT u.name; </code></pre></div> <h4 id="physical-plan-generation" class="position-relative d-flex align-items-center group"> Physical Plan Generation <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="physical-plan-generation" aria-haspopup="dialog" aria-label="Share link: Physical Plan Generation"> Share link </button> </h4>The optimizer generates concrete execution strategies: Index Selection: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Query with multiple index opportunities MATCH (u:User {email: $email})-[:FRIEND]->(f:User {country: 'USA'}) RETURN f.name; -- Optimizer chooses: -- 1. IndexSeek on User.email (high selectivity, unique) -- 2. Then filter country during traversal -- Rather than scanning all USA users first </code></pre></div>Access Methods: <ul> <li>Index Seek: Direct lookup by key (O(log n))</li> <li>Index Scan: Range scan over sorted index</li> <li>Label Scan: All nodes with specific label</li> <li>Full Scan: All nodes (last resort)</li> </ul> Join Algorithms: <ul> <li>Nested Loop: For small inner sets</li> <li>Hash Join: For equi-joins with moderate cardinality</li> <li>Expand: Following relationship pointers (graph-specific)</li> </ul> <h3 id="using-explain-for-query-analysis" class="position-relative d-flex align-items-center group"> Using EXPLAIN for Query Analysis <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="using-explain-for-query-analysis" aria-haspopup="dialog" aria-label="Share link: Using EXPLAIN for Query Analysis"> Share link </button> </h3>EXPLAIN shows the query plan without executing it: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">EXPLAIN MATCH (u:User)-[:FOLLOWS*1..3]->(influencer:User) WHERE u.id = $user_id AND influencer.follower_count > 10000 RETURN influencer.name, influencer.follower_count ORDER BY influencer.follower_count DESC LIMIT 10; </code></pre></div>Sample output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">Limit(10) Sort(influencer.follower_count DESC) Filter(influencer.follower_count > 10000) VariableLengthExpand([:FOLLOWS*1..3]) IndexSeek(User.id = $user_id) Estimated rows: 10 Estimated cost: 2,450 </code></pre></div>Interpreting the plan: <ul> <li>Bottom-up execution: Read from bottom (IndexSeek) to top (Limit)</li> <li>Indentation: Shows nesting/pipeline structure</li> <li>Estimated rows: Expected result cardinality at each stage</li> <li>Cost: Relative execution cost (higher = more expensive)</li> </ul> <h4 id="common-plan-patterns" class="position-relative d-flex align-items-center group"> Common Plan Patterns <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="common-plan-patterns" aria-haspopup="dialog" aria-label="Share link: Common Plan Patterns"> Share link </button> </h4>Efficient Plan (Good): <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">Project(u.name, p.name) Expand((User)-[:PURCHASED]->(Product)) IndexSeek(User.country = 'USA') Cost: 150, Rows: 1,234 </code></pre></div>Inefficient Plan (Bad): <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">Filter(u.country = 'USA') CartesianProduct NodeScan(User) NodeScan(Product) Cost: 45,000, Rows: 1,234 </code></pre></div> <h3 id="optimization-techniques" class="position-relative d-flex align-items-center group"> Optimization Techniques <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="optimization-techniques" aria-haspopup="dialog" aria-label="Share link: Optimization Techniques"> Share link </button> </h3> <h4 id="technique-1-strategic-index-placement" class="position-relative d-flex align-items-center group"> Technique 1: Strategic Index Placement <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="technique-1-strategic-index-placement" aria-haspopup="dialog" aria-label="Share link: Technique 1: Strategic Index Placement"> Share link </button> </h4>Identify frequently filtered properties: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python"># Analyze query logs async def analyze_query_patterns(log_file): from collections import Counter property_filters = Counter() for query in parse_logs(log_file): for prop in extract_filtered_properties(query): property_filters[prop] += 1 # Create indexes for top properties for prop, count in property_filters.most_common(10): print(f"CREATE INDEX ON {prop.label}({prop.name});") </code></pre></div>Composite indexes for multi-property filters: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- If you frequently filter by both category AND price: CREATE INDEX product_cat_price ON Product(category, price); -- This query uses the composite index efficiently: MATCH (p:Product {category: 'Electronics'}) WHERE p.price BETWEEN 100 AND 500 RETURN p; </code></pre></div> <h4 id="technique-2-cardinality-aware-query-writing" class="position-relative d-flex align-items-center group"> Technique 2: Cardinality-Aware Query Writing <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="technique-2-cardinality-aware-query-writing" aria-haspopup="dialog" aria-label="Share link: Technique 2: Cardinality-Aware Query Writing"> Share link </button> </h4>Start from the most selective (smallest result set) patterns: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- BAD: Starts from high-cardinality pattern MATCH (p:Product)-[:MANUFACTURED_BY]->(company:Company) WHERE company.name = 'TechCorp' RETURN p.name; -- Scans all Products, filters later -- GOOD: Starts from selective filter MATCH (company:Company {name: 'TechCorp'})-[:MANUFACTURES]->(p:Product) RETURN p.name; -- Single Company lookup, then expand </code></pre></div>With multiple filters: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Analyze cardinality first: MATCH (u:User) RETURN count(*); -- 1,000,000 users MATCH (u:User {country: 'Luxembourg'}) RETURN count(*); -- 500 users MATCH (u:User {age: 25}) RETURN count(*); -- 40,000 users -- Start from most selective (country): MATCH (u:User {country: 'Luxembourg'}) WHERE u.age = 25 RETURN u; </code></pre></div> <h4 id="technique-3-variable-length-path-optimization" class="position-relative d-flex align-items-center group"> Technique 3: Variable-Length Path Optimization <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="technique-3-variable-length-path-optimization" aria-haspopup="dialog" aria-label="Share link: Technique 3: Variable-Length Path Optimization"> Share link </button> </h4>Variable-length paths can be expensive; optimize carefully: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- EXPENSIVE: Unbounded path MATCH (u:User)-[:FRIEND*]->(friend) WHERE u.id = $user_id RETURN friend.name; -- May explore millions of paths! -- BETTER: Limit maximum depth MATCH (u:User)-[:FRIEND*1..3]->(friend) WHERE u.id = $user_id RETURN DISTINCT friend.name; -- Bounded exploration -- BEST: Use algorithm-specific functions MATCH (u:User {id: $user_id}) CALL graph.bfs(u, [:FRIEND], {maxDepth: 3}) YIELD node AS friend RETURN friend.name; -- Optimized breadth-first search </code></pre></div> <h4 id="technique-4-aggregation-optimization" class="position-relative d-flex align-items-center group"> Technique 4: Aggregation Optimization <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="technique-4-aggregation-optimization" aria-haspopup="dialog" aria-label="Share link: Technique 4: Aggregation Optimization"> Share link </button> </h4>Push aggregation down: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- INEFFICIENT: Materialize all, then count MATCH (u:User)-[:PURCHASED]->(p:Product) WITH u, collect(p) AS products RETURN u.name, size(products) AS purchase_count; -- EFFICIENT: Count during traversal MATCH (u:User)-[:PURCHASED]->(p:Product) RETURN u.name, count(p) AS purchase_count; </code></pre></div>Use GROUP BY strategically: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Complex aggregation with filtering MATCH (u:User)-[:PURCHASED]->(p:Product)-[:IN_CATEGORY]->(c:Category) WHERE p.price > 50 WITH c.name AS category, count(DISTINCT u) AS buyer_count WHERE buyer_count > 100 RETURN category, buyer_count ORDER BY buyer_count DESC; </code></pre></div> <h4 id="technique-5-materialized-views-precomputed-results" class="position-relative d-flex align-items-center group"> Technique 5: Materialized Views (Precomputed Results) <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="technique-5-materialized-views-precomputed-results" aria-haspopup="dialog" aria-label="Share link: Technique 5: Materialized Views (Precomputed Results)"> Share link </button> </h4>For expensive repeated queries, precompute and store results: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Expensive query: User's recommendation score MATCH (u:User)-[:PURCHASED]->(p1:Product)<-[:PURCHASED]-(other:User) -[:PURCHASED]->(p2:Product) WHERE NOT (u)-[:PURCHASED]->(p2) RETURN u.id, p2.id, count(other) AS score ORDER BY score DESC; -- Instead, periodically compute and store: MATCH (u:User)-[:PURCHASED]->(p1:Product)<-[:PURCHASED]-(other:User) -[:PURCHASED]->(p2:Product) WHERE NOT (u)-[:PURCHASED]->(p2) WITH u, p2, count(other) AS score CREATE (u)-[:RECOMMENDATION_SCORE {score: score, computed_at: timestamp()}]->(p2); -- Then query is fast: MATCH (u:User {id: $user_id})-[r:RECOMMENDATION_SCORE]->(p:Product) RETURN p.name, r.score ORDER BY r.score DESC LIMIT 10; </code></pre></div> <h3 id="advanced-optimization-strategies" class="position-relative d-flex align-items-center group"> Advanced Optimization Strategies <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="advanced-optimization-strategies" aria-haspopup="dialog" aria-label="Share link: Advanced Optimization Strategies"> Share link </button> </h3> <h4 id="query-parameterization" class="position-relative d-flex align-items-center group"> Query Parameterization <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="query-parameterization" aria-haspopup="dialog" aria-label="Share link: Query Parameterization"> Share link </button> </h4>Parameterized queries enable plan caching: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python"># BAD: String concatenation prevents plan reuse async def get_user(user_id): query = f"MATCH (u:User {{id: '{user_id}'}}) RETURN u" result, _ = await client.query(query) return result # GOOD: Parameters enable plan caching async def get_user(user_id): query = "MATCH (u:User {id: $user_id}) RETURN u" result, _ = await client.query(query, {"user_id": user_id}) return result </code></pre></div> <h4 id="batch-processing" class="position-relative d-flex align-items-center group"> Batch Processing <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="batch-processing" aria-haspopup="dialog" aria-label="Share link: Batch Processing"> Share link </button> </h4>Process multiple items in single query: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Instead of N queries: -- MATCH (u:User {id: 1}) RETURN u; -- MATCH (u:User {id: 2}) RETURN u; -- ... (repeated 1000 times) -- Single batched query: UNWIND $user_ids AS uid MATCH (u:User {id: uid}) RETURN u; </code></pre></div> <h4 id="subquery-optimization" class="position-relative d-flex align-items-center group"> Subquery Optimization <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="subquery-optimization" aria-haspopup="dialog" aria-label="Share link: Subquery Optimization"> Share link </button> </h4>Use OPTIONAL MATCH carefully: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- EXPENSIVE: Optional match multiplies cardinality MATCH (u:User) OPTIONAL MATCH (u)-[:PURCHASED]->(p:Product) OPTIONAL MATCH (u)-[:REVIEWED]->(r:Review) RETURN u, collect(p), collect(r); -- Creates cartesian product of purchases × reviews! -- BETTER: Separate aggregations MATCH (u:User) OPTIONAL MATCH (u)-[:PURCHASED]->(p:Product) WITH u, collect(p) AS purchases OPTIONAL MATCH (u)-[:REVIEWED]->(r:Review) RETURN u, purchases, collect(r) AS reviews; </code></pre></div> <h3 id="performance-monitoring" class="position-relative d-flex align-items-center group"> Performance Monitoring <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="performance-monitoring" aria-haspopup="dialog" aria-label="Share link: Performance Monitoring"> Share link </button> </h3> <h4 id="using-profile-for-runtime-analysis" class="position-relative d-flex align-items-center group"> Using PROFILE for Runtime Analysis <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="using-profile-for-runtime-analysis" aria-haspopup="dialog" aria-label="Share link: Using PROFILE for Runtime Analysis"> Share link </button> </h4>PROFILE executes and measures the query: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (u:User {country: 'USA'})-[:PURCHASED]->(p:Product) WHERE p.price > 100 RETURN u.name, count(p) AS purchase_count ORDER BY purchase_count DESC LIMIT 10; </code></pre></div>Output with timings: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">Limit(10) - 0.05ms, 10 rows Sort(purchase_count DESC) - 2.3ms, 1,234 rows Aggregate(count(p) GROUP BY u) - 8.7ms, 1,234 rows Filter(p.price > 100) - 15.2ms, 5,678 rows Expand((User)-[:PURCHASED]->(Product)) - 45.3ms, 23,456 rows IndexSeek(User.country = 'USA') - 1.2ms, 10,234 rows Total: 72.8ms Cache hits: 85% Disk I/O: 234 pages </code></pre></div>What to look for: <ul> <li>Time hotspots: Operations taking >50% of total time</li> <li>Cardinality explosion: Row count increasing dramatically</li> <li>Cache miss rate: High disk I/O indicates insufficient memory</li> <li>Redundant work: Same subquery executed multiple times</li> </ul> <h4 id="comparing-alternative-queries" class="position-relative d-flex align-items-center group"> Comparing Alternative Queries <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="comparing-alternative-queries" aria-haspopup="dialog" aria-label="Share link: Comparing Alternative Queries"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python">import asyncio from geode_client import Client async def benchmark_queries(): queries = [ ("Option 1: Index first", """ MATCH (u:User {email: $email})-[:PURCHASED]->(p:Product) RETURN p.name """), ("Option 2: Label scan first", """ MATCH (u:User)-[:PURCHASED]->(p:Product) WHERE u.email = $email RETURN p.name """), ] client = Client(host="localhost", port=3141) async with client.connection() as conn: for name, query in queries: start = time.time() result, _ = await conn.query(query, {"email": "[email protected]"}) elapsed = time.time() - start print(f"{name}: {elapsed*1000:.2f}ms") </code></pre></div> <h3 id="troubleshooting-query-performance" class="position-relative d-flex align-items-center group"> Troubleshooting Query Performance <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="troubleshooting-query-performance" aria-haspopup="dialog" aria-label="Share link: Troubleshooting Query Performance"> Share link </button> </h3> <h4 id="problem-query-times-out" class="position-relative d-flex align-items-center group"> Problem: Query Times Out <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="problem-query-times-out" aria-haspopup="dialog" aria-label="Share link: Problem: Query Times Out"> Share link </button> </h4>Diagnosis: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">EXPLAIN [your query] </code></pre></div>Look for: <ul> <li>NodeScan/LabelScan instead of IndexSeek</li> <li>High estimated row counts (>100K)</li> <li>Cartesian products</li> <li>Unbounded variable-length paths</li> </ul> Solutions: <ol> <li>Add missing indexes</li> <li>Add LIMIT clauses to bound intermediate results</li> <li>Rewrite to start from more selective patterns</li> <li>Break into multiple smaller queries</li> </ol> <h4 id="problem-query-uses-wrong-index" class="position-relative d-flex align-items-center group"> Problem: Query Uses Wrong Index <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="problem-query-uses-wrong-index" aria-haspopup="dialog" aria-label="Share link: Problem: Query Uses Wrong Index"> Share link </button> </h4>Force specific index (if optimizer chooses poorly): <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Hint to use specific index MATCH (u:User) USING INDEX u:User(email) WHERE u.email = $email AND u.country = 'USA' RETURN u; </code></pre></div> <h4 id="problem-plan-changes-with-different-parameters" class="position-relative d-flex align-items-center group"> Problem: Plan Changes with Different Parameters <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="problem-plan-changes-with-different-parameters" aria-haspopup="dialog" aria-label="Share link: Problem: Plan Changes with Different Parameters"> Share link </button> </h4>Enable plan stability: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Query with stable plan regardless of parameters MATCH (u:User) WHERE u.country = $country WITH u LIMIT 10000 -- Bound worst-case MATCH (u)-[:PURCHASED]->(p:Product) RETURN u.name, p.name; </code></pre></div> <h3 id="best-practices-summary" class="position-relative d-flex align-items-center group"> Best Practices Summary <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="best-practices-summary" aria-haspopup="dialog" aria-label="Share link: Best Practices Summary"> Share link </button> </h3><ol> <li>Always use EXPLAIN during development</li> <li>Create indexes on frequently filtered properties</li> <li>Start queries from most selective patterns</li> <li>Bound variable-length paths with maximum depth</li> <li>Use parameters instead of string concatenation</li> <li>Batch operations when processing multiple items</li> <li>Monitor with PROFILE in production</li> <li>Cache expensive computations as materialized views</li> <li>Limit intermediate results with strategic LIMIT clauses</li> <li>Test with production data sizes (dev data often misleads)</li> </ol> <h3 id="query-plan-caching" class="position-relative d-flex align-items-center group"> Query Plan Caching <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="query-plan-caching" aria-haspopup="dialog" aria-label="Share link: Query Plan Caching"> Share link </button> </h3>Geode caches query execution plans to avoid repeated parsing and planning overhead: <h4 id="plan-cache-behavior" class="position-relative d-flex align-items-center group"> Plan Cache Behavior <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="plan-cache-behavior" aria-haspopup="dialog" aria-label="Share link: Plan Cache Behavior"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- First execution: Parse, plan, execute (20ms) MATCH (u:User {email: $email}) RETURN u; -- Subsequent executions with different parameters: Execute only (2ms) MATCH (u:User {email: $different_email}) RETURN u; </code></pre></div>The query structure is the same, so Geode reuses the cached plan. Only parameter values change. <h4 id="cache-invalidation" class="position-relative d-flex align-items-center group"> Cache Invalidation <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="cache-invalidation" aria-haspopup="dialog" aria-label="Share link: Cache Invalidation"> Share link </button> </h4>Plans are invalidated when schema changes occur: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- This invalidates cached plans involving User.email CREATE INDEX ON User(email); -- This invalidates plans for Product nodes ALTER TABLE Product ADD PROPERTY discount FLOAT; -- Statistics updates trigger re-planning ANALYZE User; </code></pre></div> <h4 id="monitoring-cache-effectiveness" class="position-relative d-flex align-items-center group"> Monitoring Cache Effectiveness <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="monitoring-cache-effectiveness" aria-haspopup="dialog" aria-label="Share link: Monitoring Cache Effectiveness"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python"># Check plan cache hit rate async def monitor_plan_cache(client): result, _ = await client.query(""" CALL system.plan_cache_stats() RETURN hits, misses, hit_rate """) stats = result.rows[0] print(f"Plan cache hit rate: {stats['hit_rate']:.2%}") if stats['hit_rate'] < 0.80: print("Warning: Low plan cache hit rate") print("Consider using parameterized queries") </code></pre></div> <h3 id="advanced-optimization-patterns" class="position-relative d-flex align-items-center group"> Advanced Optimization Patterns <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="advanced-optimization-patterns" aria-haspopup="dialog" aria-label="Share link: Advanced Optimization Patterns"> Share link </button> </h3> <h4 id="query-hints" class="position-relative d-flex align-items-center group"> Query Hints <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="query-hints" aria-haspopup="dialog" aria-label="Share link: Query Hints"> Share link </button> </h4>Sometimes you know better than the optimizer: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Force specific index usage MATCH (u:User) USING INDEX u:User(email) WHERE u.email = $email AND u.country = 'USA' RETURN u; -- Disable specific optimization MATCH (a:User)-[:KNOWS]->(b:User) OPTION (HASH_JOIN=OFF) WHERE a.country = b.country RETURN a, b; -- Force join order MATCH (small:Product {category: 'Rare'}), (large:User) OPTION (JOIN_ORDER=FIXED) WHERE (large)-[:PURCHASED]->(small) RETURN large, small; </code></pre></div> <h4 id="lateral-subqueries" class="position-relative d-flex align-items-center group"> Lateral Subqueries <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="lateral-subqueries" aria-haspopup="dialog" aria-label="Share link: Lateral Subqueries"> Share link </button> </h4>Process correlated data efficiently: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- For each user, get their top 3 purchases MATCH (u:User) LATERAL { MATCH (u)-[:PURCHASED]->(p:Product) RETURN p ORDER BY p.price DESC LIMIT 3 } AS top_purchases RETURN u.name, top_purchases; </code></pre></div>This is more efficient than collecting all purchases and filtering in application code. <h4 id="parallel-query-execution" class="position-relative d-flex align-items-center group"> Parallel Query Execution <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="parallel-query-execution" aria-haspopup="dialog" aria-label="Share link: Parallel Query Execution"> Share link </button> </h4>For independent subqueries, use UNION ALL with parallel execution: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- These execute in parallel MATCH (u:User {country: 'USA'}) RETURN u.name, 'USA' AS region UNION ALL MATCH (u:User {country: 'Canada'}) RETURN u.name, 'Canada' AS region UNION ALL MATCH (u:User {country: 'Mexico'}) RETURN u.name, 'Mexico' AS region; </code></pre></div> <h4 id="query-decomposition" class="position-relative d-flex align-items-center group"> Query Decomposition <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="query-decomposition" aria-haspopup="dialog" aria-label="Share link: Query Decomposition"> Share link </button> </h4>Break complex queries into steps with WITH clauses: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Step 1: Find high-value customers MATCH (u:User)-[p:PURCHASED]->(prod:Product) WITH u, SUM(prod.price) AS total_spent WHERE total_spent > 1000 WITH u -- Step 2: Find their referrals MATCH (u)-[:REFERRED]->(referee:User) WITH u, COUNT(referee) AS referral_count -- Step 3: Calculate score RETURN u.name, referral_count, referral_count * 100 AS influence_score ORDER BY influence_score DESC; </code></pre></div>Each step can be optimized independently. <h3 id="optimization-for-specific-patterns" class="position-relative d-flex align-items-center group"> Optimization for Specific Patterns <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="optimization-for-specific-patterns" aria-haspopup="dialog" aria-label="Share link: Optimization for Specific Patterns"> Share link </button> </h3> <h4 id="graph-traversal-optimization" class="position-relative d-flex align-items-center group"> Graph Traversal Optimization <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="graph-traversal-optimization" aria-haspopup="dialog" aria-label="Share link: Graph Traversal Optimization"> Share link </button> </h4>Bi-directional Search: For shortest path queries: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Geode automatically uses bi-directional search MATCH path = SHORTEST_PATH((a:User {id: 1})-[:KNOWS*]-(b:User {id: 999})) RETURN length(path); </code></pre></div>This searches from both ends simultaneously, meeting in the middle. Pruning: Stop traversal early when possible: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Find users within 3 hops OR until 100 users found MATCH (start:User {id: $start_id})-[:KNOWS*1..3]->(connected:User) RETURN DISTINCT connected LIMIT 100; </code></pre></div>The LIMIT causes traversal to stop after finding 100 users, even if depth < 3. <h4 id="aggregation-optimization" class="position-relative d-flex align-items-center group"> Aggregation Optimization <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="aggregation-optimization" aria-haspopup="dialog" aria-label="Share link: Aggregation Optimization"> Share link </button> </h4>Partial Aggregation: Pre-aggregate at lower levels: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Optimize hierarchical aggregation MATCH (country:Country)<-[:LOCATED_IN]-(city:City)<-[:IN]-(user:User) WITH country, city, COUNT(user) AS city_users WITH country, SUM(city_users) AS total_users RETURN country.name, total_users; </code></pre></div>Aggregating by city first reduces intermediate data size. Streaming Aggregation: For large result sets: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python"># Process aggregations in streaming fashion async def streaming_aggregation(client): query = """ MATCH (u:User)-[:PURCHASED]->(p:Product) RETURN p.category, COUNT(*) AS purchases GROUP BY p.category """ for row in client.rows.execute_streaming(query): category = row['p.category'] purchases = row['purchases'] # Process incrementally without loading all into memory await process_category_stats(category, purchases) </code></pre></div> <h4 id="write-optimization" class="position-relative d-flex align-items-center group"> Write Optimization <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="write-optimization" aria-haspopup="dialog" aria-label="Share link: Write Optimization"> Share link </button> </h4>Bulk Inserts: Batch multiple writes: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Instead of 1000 individual inserts UNWIND $user_list AS user_data CREATE (u:User) SET u = user_data; </code></pre></div>This is more efficient than individual CREATE statements. Deferred Index Updates: For bulk operations: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Disable index temporarily for bulk load ALTER INDEX user_email_idx DISABLE; -- Bulk insert LOAD CSV FROM 'users.csv' AS row CREATE (u:User {email: row.email, name: row.name}); -- Rebuild index once ALTER INDEX user_email_idx ENABLE; REBUILD INDEX user_email_idx; </code></pre></div> <h3 id="real-world-optimization-case-studies" class="position-relative d-flex align-items-center group"> Real-World Optimization Case Studies <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="real-world-optimization-case-studies" aria-haspopup="dialog" aria-label="Share link: Real-World Optimization Case Studies"> Share link </button> </h3> <h4 id="case-study-1-e-commerce-recommendation-engine" class="position-relative d-flex align-items-center group"> Case Study 1: E-Commerce Recommendation Engine <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="case-study-1-e-commerce-recommendation-engine" aria-haspopup="dialog" aria-label="Share link: Case Study 1: E-Commerce Recommendation Engine"> Share link </button> </h4>Problem: Recommendation query taking 45 seconds. <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Original slow query MATCH (user:User {id: $user_id})-[:PURCHASED]->(p1:Product) <-[:PURCHASED]-(other:User)-[:PURCHASED]->(p2:Product) WHERE NOT (user)-[:PURCHASED]->(p2) RETURN p2.name, COUNT(*) AS score ORDER BY score DESC LIMIT 10; </code></pre></div>Optimization Steps: <ol> <li>Add indexes:</li> </ol> <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE INDEX ON User(id); CREATE INDEX ON Product(id); </code></pre></div><ol start="2"> <li>Limit intermediate results:</li> </ol> <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">MATCH (user:User {id: $user_id})-[:PURCHASED]->(p1:Product) <-[:PURCHASED]-(other:User) WITH other LIMIT 100 -- Only consider top 100 similar users MATCH (other)-[:PURCHASED]->(p2:Product) WHERE NOT (user)-[:PURCHASED]->(p2) RETURN p2.name, COUNT(*) AS score ORDER BY score DESC LIMIT 10; </code></pre></div><ol start="3"> <li>Materialize frequently accessed data:</li> </ol> <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Pre-compute recommendations nightly MATCH (user:User)-[:PURCHASED]->(p1:Product) <-[:PURCHASED]-(other:User)-[:PURCHASED]->(p2:Product) WHERE NOT (user)-[:PURCHASED]->(p2) WITH user, p2, COUNT(*) AS score ORDER BY score DESC WITH user, COLLECT({product: p2, score: score})[0..50] AS recommendations SET user.recommendations = recommendations; -- Fast query at runtime MATCH (user:User {id: $user_id}) RETURN user.recommendations; </code></pre></div>Result: Query time reduced from 45s to 8ms (5,625x faster). <h4 id="case-study-2-social-network-friend-suggestions" class="position-relative d-flex align-items-center group"> Case Study 2: Social Network Friend Suggestions <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="case-study-2-social-network-friend-suggestions" aria-haspopup="dialog" aria-label="Share link: Case Study 2: Social Network Friend Suggestions"> Share link </button> </h4>Problem: Finding friend suggestions consuming excessive memory. <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Original memory-intensive query MATCH (user:User {id: $user_id})-[:FRIENDS]->(friend)-[:FRIENDS]->(fof:User) WHERE NOT (user)-[:FRIENDS]->(fof) AND user <> fof WITH user, fof, COUNT(*) AS mutual_friends ORDER BY mutual_friends DESC RETURN fof.name, mutual_friends LIMIT 20; </code></pre></div>Optimization: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Stream results, don't collect all MATCH (user:User {id: $user_id})-[:FRIENDS]->(friend) WITH user, friend LIMIT 50 -- Bound friend traversal MATCH (friend)-[:FRIENDS]->(fof:User) WHERE NOT (user)-[:FRIENDS]->(fof) AND user <> fof WITH user, fof, COUNT(*) AS mutual_friends ORDER BY mutual_friends DESC LIMIT 20 RETURN fof.name, mutual_friends; </code></pre></div>Result: Memory usage reduced from 2GB to 50MB. <h4 id="case-study-3-fraud-detection-pattern-matching" class="position-relative d-flex align-items-center group"> Case Study 3: Fraud Detection Pattern Matching <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="case-study-3-fraud-detection-pattern-matching" aria-haspopup="dialog" aria-label="Share link: Case Study 3: Fraud Detection Pattern Matching"> Share link </button> </h4>Problem: Detecting circular payment patterns timing out. Optimization: Use specialized graph algorithms: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Instead of generic pattern matching CALL graph.detect_cycles( start_nodes: [:Account], relationship_type: 'PAYMENT', max_cycle_length: 5, min_amount: 1000 ) YIELD cycle RETURN cycle; </code></pre></div>Specialized algorithms are more efficient than generic pattern matching for specific graph problems. <h3 id="optimization-tooling" class="position-relative d-flex align-items-center group"> Optimization Tooling <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="optimization-tooling" aria-haspopup="dialog" aria-label="Share link: Optimization Tooling"> Share link </button> </h3> <h4 id="query-performance-dashboard" class="position-relative d-flex align-items-center group"> Query Performance Dashboard <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="query-performance-dashboard" aria-haspopup="dialog" aria-label="Share link: Query Performance Dashboard"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python"># Automated query performance tracking import asyncio from collections import defaultdict class QueryPerformanceMonitor: def __init__(self, client): self.client = client self.metrics = defaultdict(list) async def execute_tracked(self, query, params=None): start = time.time() result, _ = await self.client.query(query, params) duration = time.time() - start # Track query performance query_hash = hashlib.md5(query.encode()).hexdigest()[:8] self.metrics[query_hash].append({ 'duration': duration, 'timestamp': time.time(), 'row_count': len(result.rows) }) # Alert on slow queries if duration > 1.0: print(f"SLOW QUERY ({duration:.2f}s): {query[:100]}...") return result def get_slowest_queries(self, n=10): query_stats = {} for query_hash, executions in self.metrics.items(): avg_duration = sum(e['duration'] for e in executions) / len(executions) query_stats[query_hash] = { 'avg_duration': avg_duration, 'execution_count': len(executions), 'total_time': sum(e['duration'] for e in executions) } return sorted(query_stats.items(), key=lambda x: x[1]['avg_duration'], reverse=True)[:n] </code></pre></div> <h4 id="automated-index-recommendations" class="position-relative d-flex align-items-center group"> Automated Index Recommendations <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="automated-index-recommendations" aria-haspopup="dialog" aria-label="Share link: Automated Index Recommendations"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python">async def recommend_indexes(client, query_log_file): """Analyze query log and recommend indexes""" from collections import Counter property_access = Counter() # Parse query log for query in parse_query_log(query_log_file): # Extract filtered properties for prop in extract_property_filters(query): property_access[prop] += 1 # Generate recommendations recommendations = [] for (label, property), count in property_access.most_common(20): # Check if index exists existing, _ = await client.query( "SHOW INDEXES WHERE label = $label AND property = $prop", label=label, prop=property ) if not existing.rows: recommendations.append({ 'label': label, 'property': property, 'access_count': count, 'create_ddl': f"CREATE INDEX ON {label}({property});" }) return recommendations </code></pre></div> <h3 id="related-topics" class="position-relative d-flex align-items-center group"> Related Topics <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="related-topics" aria-haspopup="dialog" aria-label="Share link: Related Topics"> Share link </button> </h3><ul> <li><a href="/tags/performance/" >Performance</a> : General performance optimization</li> <li><a href="/tags/indexing/" >Indexing</a> : Index types and strategies</li> <li><a href="/tags/query-language/" >Query Language</a> : GQL syntax and patterns</li> <li><a href="/tags/profiling/" >Profiling</a> : Detailed profiling techniques</li> <li><a href="/tags/tuning/" >Tuning</a> : Database tuning parameters</li> <li><a href="/tags/explain/" >Explain</a> : EXPLAIN command reference</li> </ul> <h3 id="further-reading" class="position-relative d-flex align-items-center group"> Further Reading <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="further-reading" aria-haspopup="dialog" aria-label="Share link: Further Reading"> Share link </button> </h3><ul> <li>Query Optimization Guide: <code>/docs/performance/query-optimization/</code></li> <li>Index Selection Best Practices: <code>/docs/indexing/choosing-indexes/</code></li> <li>GQL Query Patterns: <code>/docs/gql/query-patterns/</code></li> <li>Performance Tuning: <code>/docs/performance/tuning-guide/</code></li> </ul>

Popular

Related Articles

Querying, Indexing, and Query Optimization

Constraints and Indexes Guide

Query Performance Tuning Guide

Query Performance Guide

Index Strategy Guide

Query Profiling Guide

Index Types Reference

EXPLAIN Command

Guides

Full-Text Search with BM25 Ranking

Materialized Views System