Query Profiling Guide | Geode Database

<h2 id="query-profiling-guide" class="position-relative d-flex align-items-center group"> Query Profiling Guide <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-profiling-guide" aria-haspopup="dialog" aria-label="Share link: Query Profiling Guide"> 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>Understanding how Geode executes your queries is essential for optimization. This guide covers using EXPLAIN and PROFILE to analyze execution plans, identify bottlenecks, and systematically improve query performance. <h3 id="explain-vs-profile" class="position-relative d-flex align-items-center group"> EXPLAIN vs PROFILE <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="explain-vs-profile" aria-haspopup="dialog" aria-label="Share link: EXPLAIN vs PROFILE"> Share link </button> </h3>Geode provides two tools for query analysis: <table> <thead> <tr> <th>Tool</th> <th>What It Does</th> <th>When to Use</th> </tr> </thead> <tbody> <tr> <td>EXPLAIN</td> <td>Shows the execution plan without running</td> <td>Planning queries, understanding structure</td> </tr> <tr> <td>PROFILE</td> <td>Executes query and shows actual metrics</td> <td>Measuring real performance, finding bottlenecks</td> </tr> </tbody> </table> <h4 id="using-explain" class="position-relative d-flex align-items-center group"> Using EXPLAIN <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" aria-haspopup="dialog" aria-label="Share link: Using EXPLAIN"> Share link </button> </h4>EXPLAIN shows the planned execution strategy: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">EXPLAIN MATCH (user:User {email: "[email protected]"})-[:POSTED]->(post:Post) WHERE post.created_at > datetime() - duration('P7D') RETURN post.title, post.created_at ORDER BY post.created_at DESC LIMIT 10 </code></pre></div>Output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">+---------------------------+ | Plan | +---------------------------+ | Projection | | | | | +Sort | | | | | +Filter | | | | | +Expand(All) | | | | | +NodeIndexSeek | +---------------------------+ </code></pre></div> <h4 id="using-profile" class="position-relative d-flex align-items-center group"> Using PROFILE <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" aria-haspopup="dialog" aria-label="Share link: Using PROFILE"> Share link </button> </h4>PROFILE executes the query and collects metrics: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (user:User {email: "[email protected]"})-[:POSTED]->(post:Post) WHERE post.created_at > datetime() - duration('P7D') RETURN post.title, post.created_at ORDER BY post.created_at DESC LIMIT 10 </code></pre></div>Output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">+---------------------------+----------------+----------+------------+ | Operator | Rows | DB Hits | Time (ms) | +---------------------------+----------------+----------+------------+ | ProduceResults | 10 | 0 | 0.1 | | | | | | | | +Top | 10 | 0 | 0.2 | | | | | | | | +Projection | 42 | 84 | 0.3 | | | | | | | | +Filter | 42 | 42 | 0.5 | | | | | | | | +Expand(All) | 156 | 157 | 1.2 | | | | | | | | +NodeIndexSeek | 1 | 2 | 0.1 | +---------------------------+----------------+----------+------------+ Total database hits: 285 Total time: 2.4ms </code></pre></div> <h3 id="reading-execution-plans" class="position-relative d-flex align-items-center group"> Reading Execution Plans <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="reading-execution-plans" aria-haspopup="dialog" aria-label="Share link: Reading Execution Plans"> Share link </button> </h3> <h4 id="plan-structure" class="position-relative d-flex align-items-center group"> Plan Structure <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-structure" aria-haspopup="dialog" aria-label="Share link: Plan Structure"> Share link </button> </h4>Execution plans read from bottom to top - data flows upward through operators: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">ProduceResults <-- Final output (top) | Sort <-- Ordering | Filter <-- WHERE conditions | Expand <-- Relationship traversal | NodeIndexSeek <-- Starting point (bottom) </code></pre></div> <h4 id="common-operators" class="position-relative d-flex align-items-center group"> Common Operators <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-operators" aria-haspopup="dialog" aria-label="Share link: Common Operators"> Share link </button> </h4> <h5 id="data-access-operators" class="position-relative d-flex align-items-center group"> Data Access Operators <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="data-access-operators" aria-haspopup="dialog" aria-label="Share link: Data Access Operators"> Share link </button> </h5><table> <thead> <tr> <th>Operator</th> <th>Description</th> <th>Performance</th> </tr> </thead> <tbody> <tr> <td>NodeIndexSeek</td> <td>Uses index to find nodes</td> <td>Fast - O(log n)</td> </tr> <tr> <td>NodeByLabelScan</td> <td>Scans all nodes with label</td> <td>Slow - O(n)</td> </tr> <tr> <td>NodeByIdSeek</td> <td>Finds node by internal ID</td> <td>Very fast - O(1)</td> </tr> <tr> <td>AllNodesScan</td> <td>Scans every node</td> <td>Very slow - avoid</td> </tr> </tbody> </table> Example - Good (index seek): <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">EXPLAIN MATCH (u:User {email: "[email protected]"}) RETURN u // Plan shows: NodeIndexSeek </code></pre></div>Example - Bad (label scan): <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">EXPLAIN MATCH (u:User) WHERE u.signup_source = "organic" RETURN u // Plan shows: NodeByLabelScan + Filter // Consider: CREATE INDEX user_signup ON :User(signup_source) </code></pre></div> <h5 id="traversal-operators" class="position-relative d-flex align-items-center group"> Traversal Operators <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="traversal-operators" aria-haspopup="dialog" aria-label="Share link: Traversal Operators"> Share link </button> </h5><table> <thead> <tr> <th>Operator</th> <th>Description</th> <th>Performance</th> </tr> </thead> <tbody> <tr> <td>Expand(All)</td> <td>Follow relationships</td> <td>O(degree)</td> </tr> <tr> <td>Expand(Into)</td> <td>Check if relationship exists</td> <td>O(degree)</td> </tr> <tr> <td>VarLengthExpand</td> <td>Variable-length paths</td> <td>O(branching^depth)</td> </tr> <tr> <td>ShortestPath</td> <td>Find shortest path</td> <td>Expensive - use with caution</td> </tr> </tbody> </table> Example - Expand: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">EXPLAIN MATCH (u:User)-[:FOLLOWS]->(f:User) RETURN f // Plan shows: Expand(All) - follows FOLLOWS relationships </code></pre></div>Example - VarLengthExpand: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">EXPLAIN MATCH (u:User)-[:FOLLOWS*1..3]->(f:User) RETURN f // Plan shows: VarLengthExpand - WARNING: can be expensive </code></pre></div> <h5 id="processing-operators" class="position-relative d-flex align-items-center group"> Processing Operators <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="processing-operators" aria-haspopup="dialog" aria-label="Share link: Processing Operators"> Share link </button> </h5><table> <thead> <tr> <th>Operator</th> <th>Description</th> <th>Performance</th> </tr> </thead> <tbody> <tr> <td>Filter</td> <td>Apply WHERE conditions</td> <td>O(n) on input rows</td> </tr> <tr> <td>Projection</td> <td>Select/transform columns</td> <td>O(n) - lightweight</td> </tr> <tr> <td>Sort</td> <td>Order results</td> <td>O(n log n)</td> </tr> <tr> <td>Top</td> <td>Sort + limit combined</td> <td>More efficient than Sort + Limit</td> </tr> <tr> <td>Limit</td> <td>Restrict output rows</td> <td>Can short-circuit</td> </tr> <tr> <td>Skip</td> <td>Skip initial rows</td> <td>Must process skipped rows</td> </tr> <tr> <td>Distinct</td> <td>Remove duplicates</td> <td>O(n) with hash</td> </tr> </tbody> </table> <h5 id="aggregation-operators" class="position-relative d-flex align-items-center group"> Aggregation Operators <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-operators" aria-haspopup="dialog" aria-label="Share link: Aggregation Operators"> Share link </button> </h5><table> <thead> <tr> <th>Operator</th> <th>Description</th> <th>Performance</th> </tr> </thead> <tbody> <tr> <td>EagerAggregation</td> <td>GROUP BY with aggregates</td> <td>O(n) - must see all rows</td> </tr> <tr> <td>OrderedAggregation</td> <td>Aggregation on sorted input</td> <td>More efficient</td> </tr> <tr> <td>Count</td> <td>Count rows</td> <td>O(n)</td> </tr> </tbody> </table> <h5 id="join-operators" class="position-relative d-flex align-items-center group"> Join Operators <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="join-operators" aria-haspopup="dialog" aria-label="Share link: Join Operators"> Share link </button> </h5><table> <thead> <tr> <th>Operator</th> <th>Description</th> <th>Performance</th> </tr> </thead> <tbody> <tr> <td>NodeHashJoin</td> <td>Hash join on nodes</td> <td>O(n + m)</td> </tr> <tr> <td>ValueHashJoin</td> <td>Hash join on values</td> <td>O(n + m)</td> </tr> <tr> <td>CartesianProduct</td> <td>Cross product</td> <td>O(n * m) - WARNING</td> </tr> <tr> <td>Apply</td> <td>Nested loop join</td> <td>O(n * m) - can be expensive</td> </tr> </tbody> </table> <h4 id="key-metrics-to-watch" class="position-relative d-flex align-items-center group"> Key Metrics to Watch <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="key-metrics-to-watch" aria-haspopup="dialog" aria-label="Share link: Key Metrics to Watch"> Share link </button> </h4>When analyzing PROFILE output: <ol> <li> Rows: Number of rows passing through each operator <ul> <li>Large row counts early = may need better filtering</li> <li>Rows increasing dramatically = possible Cartesian product</li> </ul> </li> <li> DB Hits: Number of storage accesses <ul> <li>High DB hits = more I/O, slower query</li> <li>Compare DB hits to rows for efficiency</li> </ul> </li> <li> Time: Execution time per operator <ul> <li>Identify slowest operators</li> <li>Focus optimization efforts there</li> </ul> </li> <li> Estimated Rows (EXPLAIN): Planner’s row estimates <ul> <li>Compare to actual rows in PROFILE</li> <li>Large discrepancies indicate stale statistics</li> </ul> </li> </ol> <h3 id="identifying-bottlenecks" class="position-relative d-flex align-items-center group"> Identifying Bottlenecks <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="identifying-bottlenecks" aria-haspopup="dialog" aria-label="Share link: Identifying Bottlenecks"> Share link </button> </h3> <h4 id="high-row-counts" class="position-relative d-flex align-items-center group"> High Row Counts <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="high-row-counts" aria-haspopup="dialog" aria-label="Share link: High Row Counts"> Share link </button> </h4>Problem: Operators processing too many rows <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">+---------------------------+----------------+ | Operator | Rows | +---------------------------+----------------+ | Filter | 100 | | | | | | +Expand(All) | 1,000,000 | <-- Bottleneck! | | | | | +NodeByLabelScan | 10,000 | +---------------------------+----------------+ </code></pre></div>Solution: Add index or restructure query <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Before: Scanning all users, expanding all relationships MATCH (u:User)-[:FOLLOWS]->(f:User) WHERE f.verified = true RETURN f // After: Start from verified users (with index) MATCH (f:User {verified: true})<-[:FOLLOWS]-(u:User) RETURN f </code></pre></div> <h4 id="nodebylabelscan" class="position-relative d-flex align-items-center group"> NodeByLabelScan <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="nodebylabelscan" aria-haspopup="dialog" aria-label="Share link: NodeByLabelScan"> Share link </button> </h4>Problem: Full label scan instead of index <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">+---------------------------+ | Operator | +---------------------------+ | Filter | | | | | +NodeByLabelScan | <-- No index used +---------------------------+ </code></pre></div>Solution: Create appropriate index <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Identify the filtering property EXPLAIN MATCH (p:Product) WHERE p.category = "electronics" RETURN p // Create index CREATE INDEX product_category ON :Product(category) // Verify index usage EXPLAIN MATCH (p:Product {category: "electronics"}) RETURN p // Should show: NodeIndexSeek </code></pre></div> <h4 id="cartesianproduct" class="position-relative d-flex align-items-center group"> CartesianProduct <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="cartesianproduct" aria-haspopup="dialog" aria-label="Share link: CartesianProduct"> Share link </button> </h4>Problem: Unconnected patterns creating cross product <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">+---------------------------+----------------+ | Operator | Rows | +---------------------------+----------------+ | CartesianProduct | 10,000,000 | <-- Explosive! | |\ | | | | NodeByLabelScan | 10,000 | | | | | | +NodeByLabelScan | 1,000 | +---------------------------+----------------+ </code></pre></div>Solution: Connect patterns or split queries <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Before: Cartesian product MATCH (u:User), (p:Product) WHERE u.country = p.origin_country RETURN u, p // After: Connected pattern MATCH (u:User)-[:INTERESTED_IN]->(:Category)<-[:IN_CATEGORY]-(p:Product) WHERE u.country = p.origin_country RETURN u, p </code></pre></div> <h4 id="expensive-varlengthexpand" class="position-relative d-flex align-items-center group"> Expensive VarLengthExpand <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="expensive-varlengthexpand" aria-haspopup="dialog" aria-label="Share link: Expensive VarLengthExpand"> Share link </button> </h4>Problem: Unbounded or deep variable-length patterns <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">+---------------------------+----------------+----------+ | Operator | Rows | Time | +---------------------------+----------------+----------+ | VarLengthExpand | 5,000,000 | 4500ms | <-- Very slow! | | | | | | +NodeIndexSeek | 1 | 1ms | +---------------------------+----------------+----------+ </code></pre></div>Solution: Bound the depth, add intermediate filtering <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Before: Unbounded depth MATCH path = (start:Person)-[:KNOWS*]->(end:Person) RETURN path // After: Bounded depth MATCH path = (start:Person)-[:KNOWS*1..4]->(end:Person) RETURN path LIMIT 1000 // Even better: Filter at each hop MATCH path = (start:Person {id: $id})-[:KNOWS*1..3]->(end:Person) WHERE ALL(n IN nodes(path) WHERE n.active = true) RETURN path </code></pre></div> <h4 id="late-filtering" class="position-relative d-flex align-items-center group"> Late Filtering <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="late-filtering" aria-haspopup="dialog" aria-label="Share link: Late Filtering"> Share link </button> </h4>Problem: Filters applied after expensive operations <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">+---------------------------+----------------+----------+ | Operator | Rows | Time | +---------------------------+----------------+----------+ | Filter | 10 | 50ms | | | | | | | +Sort | 100,000 | 500ms | <-- Sorted everything | | | | | | +Expand | 100,000 | 200ms | | | | | | | +NodeByLabelScan | 10,000 | 100ms | +---------------------------+----------------+----------+ </code></pre></div>Solution: Filter earlier <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Before: Filter after sort MATCH (u:User)-[:POSTED]->(p:Post) RETURN p ORDER BY p.created_at LIMIT 10 // Then filtering for specific user in application // After: Filter first MATCH (u:User {id: $user_id})-[:POSTED]->(p:Post) RETURN p ORDER BY p.created_at LIMIT 10 </code></pre></div> <h3 id="query-optimization-workflow" class="position-relative d-flex align-items-center group"> Query Optimization Workflow <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-workflow" aria-haspopup="dialog" aria-label="Share link: Query Optimization Workflow"> Share link </button> </h3> <h4 id="step-1-baseline-measurement" class="position-relative d-flex align-items-center group"> Step 1: Baseline Measurement <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="step-1-baseline-measurement" aria-haspopup="dialog" aria-label="Share link: Step 1: Baseline Measurement"> Share link </button> </h4>Profile the original query to establish baseline: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (user:User)-[:PURCHASED]->(product:Product) WHERE product.category = "electronics" AND product.price > 100 RETURN user.name, product.name, product.price ORDER BY product.price DESC LIMIT 20 </code></pre></div>Record metrics: <ul> <li>Total time: 450ms</li> <li>Total DB hits: 125,000</li> <li>Rows at each stage</li> </ul> <h4 id="step-2-identify-bottlenecks" class="position-relative d-flex align-items-center group"> Step 2: Identify Bottlenecks <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="step-2-identify-bottlenecks" aria-haspopup="dialog" aria-label="Share link: Step 2: Identify Bottlenecks"> Share link </button> </h4>Analyze the profile output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">+---------------------------+----------------+----------+------------+ | Operator | Rows | DB Hits | Time (ms) | +---------------------------+----------------+----------+------------+ | Top | 20 | 0 | 1 | | | | | | | | +Projection | 500 | 1000 | 5 | | | | | | | | +Filter | 500 | 500 | 10 | | | | | | | | +Expand | 50,000 | 50,001 | 200 | <-- High rows | | | | | | | +NodeByLabelScan | 10,000 | 73,500 | 230 | <-- No index! +---------------------------+----------------+----------+------------+ </code></pre></div>Bottlenecks identified: <ol> <li>NodeByLabelScan on User (no index)</li> <li>50,000 rows from Expand before filtering</li> <li>Filter on Product properties applied late</li> </ol> <h4 id="step-3-apply-optimizations" class="position-relative d-flex align-items-center group"> Step 3: Apply Optimizations <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="step-3-apply-optimizations" aria-haspopup="dialog" aria-label="Share link: Step 3: Apply Optimizations"> Share link </button> </h4>Optimization 1: Start from indexed Product instead: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Create index on Product category and price CREATE INDEX product_category_price ON :Product(category, price) </code></pre></div>Optimization 2: Restructure query: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (product:Product) WHERE product.category = "electronics" AND product.price > 100 WITH product ORDER BY product.price DESC LIMIT 20 MATCH (user:User)-[:PURCHASED]->(product) RETURN user.name, product.name, product.price </code></pre></div> <h4 id="step-4-measure-improvement" class="position-relative d-flex align-items-center group"> Step 4: Measure Improvement <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="step-4-measure-improvement" aria-haspopup="dialog" aria-label="Share link: Step 4: Measure Improvement"> Share link </button> </h4>Profile the optimized query: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">+---------------------------+----------------+----------+------------+ | Operator | Rows | DB Hits | Time (ms) | +---------------------------+----------------+----------+------------+ | Projection | 20 | 40 | 1 | | | | | | | | +Expand | 20 | 40 | 2 | | | | | | | | +Top | 20 | 0 | 1 | | | | | | | | +NodeIndexSeek | 500 | 501 | 5 | +---------------------------+----------------+----------+------------+ </code></pre></div>Results: <ul> <li>Total time: 9ms (98% improvement)</li> <li>Total DB hits: 581 (99.5% reduction)</li> </ul> <h4 id="step-5-document-and-monitor" class="position-relative d-flex align-items-center group"> Step 5: Document and Monitor <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="step-5-document-and-monitor" aria-haspopup="dialog" aria-label="Share link: Step 5: Document and Monitor"> Share link </button> </h4>Record the optimization: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-markdown" data-lang="markdown">## Query: User Purchases by Category ### Original - Time: 450ms - DB Hits: 125,000 - Issue: Full User scan, late Product filtering ### Optimized - Time: 9ms - DB Hits: 581 - Changes: 1. Created composite index on Product(category, price) 2. Restructured to start from Product with early LIMIT 3. Expand to User as final step ### Index Added CREATE INDEX product_category_price ON :Product(category, price) </code></pre></div> <h3 id="beforeafter-comparisons" class="position-relative d-flex align-items-center group"> Before/After Comparisons <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="beforeafter-comparisons" aria-haspopup="dialog" aria-label="Share link: Before/After Comparisons"> Share link </button> </h3> <h4 id="example-1-adding-an-index" class="position-relative d-flex align-items-center group"> Example 1: Adding an 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="example-1-adding-an-index" aria-haspopup="dialog" aria-label="Share link: Example 1: Adding an Index"> Share link </button> </h4>Before (no index): <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (u:User) WHERE u.email = "[email protected]" RETURN u </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">+---------------------------+----------------+----------+ | Operator | Rows | DB Hits | +---------------------------+----------------+----------+ | Filter | 1 | 0 | | | | | | | +NodeByLabelScan | 1,000,000 | 2,000,000| +---------------------------+----------------+----------+ Time: 850ms </code></pre></div>After (with index): <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- CREATE INDEX user_email ON :User(email) PROFILE MATCH (u:User {email: "[email protected]"}) RETURN u </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">+---------------------------+----------------+----------+ | Operator | Rows | DB Hits | +---------------------------+----------------+----------+ | NodeIndexSeek | 1 | 2 | +---------------------------+----------------+----------+ Time: 1ms </code></pre></div>Improvement: 850x faster, 1,000,000x fewer DB hits <h4 id="example-2-restructuring-pattern-order" class="position-relative d-flex align-items-center group"> Example 2: Restructuring Pattern Order <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="example-2-restructuring-pattern-order" aria-haspopup="dialog" aria-label="Share link: Example 2: Restructuring Pattern Order"> Share link </button> </h4>Before (starting from less selective node): <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (post:Post)-[:WRITTEN_BY]->(author:Author {verified: true}) WHERE post.published = true RETURN post.title, author.name LIMIT 10 </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">+---------------------------+----------------+----------+ | Operator | Rows | DB Hits | +---------------------------+----------------+----------+ | Top | 10 | 0 | | | | | | | +Filter | 500 | 500 | | | | | | | +Expand | 10,000 | 10,001 | | | | | | | +NodeByLabelScan | 10,000 | 30,000 | +---------------------------+----------------+----------+ Time: 120ms </code></pre></div>After (starting from more selective indexed node): <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- CREATE INDEX author_verified ON :Author(verified) PROFILE MATCH (author:Author {verified: true})-[:WROTE]->(post:Post) WHERE post.published = true RETURN post.title, author.name LIMIT 10 </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">+---------------------------+----------------+----------+ | Operator | Rows | DB Hits | +---------------------------+----------------+----------+ | Top | 10 | 0 | | | | | | | +Filter | 50 | 50 | | | | | | | +Expand | 200 | 201 | | | | | | | +NodeIndexSeek | 100 | 101 | +---------------------------+----------------+----------+ Time: 8ms </code></pre></div>Improvement: 15x faster, 115x fewer DB hits <h4 id="example-3-using-with-for-early-aggregation" class="position-relative d-flex align-items-center group"> Example 3: Using WITH for Early Aggregation <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="example-3-using-with-for-early-aggregation" aria-haspopup="dialog" aria-label="Share link: Example 3: Using WITH for Early Aggregation"> Share link </button> </h4>Before (aggregating at the end): <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (user:User)-[:FOLLOWS]->(followed:User)-[:POSTED]->(post:Post) WHERE user.id = $user_id RETURN followed.name, count(post) AS post_count ORDER BY post_count DESC LIMIT 10 </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">+---------------------------+----------------+----------+ | Operator | Rows | DB Hits | +---------------------------+----------------+----------+ | Top | 10 | 0 | | | | | | | +EagerAggregation | 500 | 0 | | | | | | | +Expand | 50,000 | 50,001 | <-- Many posts | | | | | | +Expand | 500 | 501 | | | | | | | +NodeIndexSeek | 1 | 2 | +---------------------------+----------------+----------+ Time: 180ms </code></pre></div>After (limiting followed users first): <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (user:User {id: $user_id})-[:FOLLOWS]->(followed:User) WITH followed ORDER BY followed.follower_count DESC LIMIT 50 MATCH (followed)-[:POSTED]->(post:Post) RETURN followed.name, count(post) AS post_count ORDER BY post_count DESC LIMIT 10 </code></pre></div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">+---------------------------+----------------+----------+ | Operator | Rows | DB Hits | +---------------------------+----------------+----------+ | Top | 10 | 0 | | | | | | | +EagerAggregation | 50 | 0 | | | | | | | +Expand | 5,000 | 5,001 | <-- Reduced posts | | | | | | +Top | 50 | 0 | | | | | | | +Expand | 500 | 501 | | | | | | | +NodeIndexSeek | 1 | 2 | +---------------------------+----------------+----------+ Time: 25ms </code></pre></div>Improvement: 7x faster, 10x fewer rows processed <h3 id="advanced-profiling-techniques" class="position-relative d-flex align-items-center group"> Advanced Profiling 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="advanced-profiling-techniques" aria-haspopup="dialog" aria-label="Share link: Advanced Profiling Techniques"> Share link </button> </h3> <h4 id="comparing-plan-variants" class="position-relative d-flex align-items-center group"> Comparing Plan Variants <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-plan-variants" aria-haspopup="dialog" aria-label="Share link: Comparing Plan Variants"> Share link </button> </h4>Test multiple query formulations: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Variant A: Standard pattern PROFILE MATCH (a:Person)-[:KNOWS]->(b:Person)-[:KNOWS]->(c:Person) WHERE a.name = "Alice" RETURN DISTINCT c.name // Variant B: Using WITH PROFILE MATCH (a:Person {name: "Alice"})-[:KNOWS]->(b:Person) WITH DISTINCT b MATCH (b)-[:KNOWS]->(c:Person) RETURN DISTINCT c.name // Variant C: Using shortestPath (if applicable) PROFILE MATCH (a:Person {name: "Alice"}), (c:Person) WHERE (a)-[:KNOWS*2..2]->(c) RETURN DISTINCT c.name </code></pre></div>Compare total time and DB hits to choose the best variant. <h4 id="profiling-with-parameters" class="position-relative d-flex align-items-center group"> Profiling with 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="profiling-with-parameters" aria-haspopup="dialog" aria-label="Share link: Profiling with Parameters"> Share link </button> </h4>Always profile with realistic parameter values: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Profile with edge case (very connected user) :param user_id => "power_user_123" PROFILE MATCH (u:User {id: $user_id})-[:FOLLOWS]->(f:User) RETURN count(f) // Profile with typical case :param user_id => "average_user_456" PROFILE MATCH (u:User {id: $user_id})-[:FOLLOWS]->(f:User) RETURN count(f) </code></pre></div> <h4 id="analyzing-memory-usage" class="position-relative d-flex align-items-center group"> Analyzing Memory Usage <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="analyzing-memory-usage" aria-haspopup="dialog" aria-label="Share link: Analyzing Memory Usage"> Share link </button> </h4>For complex queries, check memory consumption: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (a)-[r*1..5]->(b) WHERE a.id = $id RETURN count(DISTINCT b) </code></pre></div>Watch for operators that accumulate state: <ul> <li><code>EagerAggregation</code>: Holds all groups in memory</li> <li><code>Sort</code>: Holds all rows to sort</li> <li><code>Distinct</code>: Maintains hash set of seen values</li> <li><code>Collect</code>: Accumulates list in memory</li> </ul> <h3 id="profiling-checklist" class="position-relative d-flex align-items-center group"> Profiling Checklist <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="profiling-checklist" aria-haspopup="dialog" aria-label="Share link: Profiling Checklist"> Share link </button> </h3>When optimizing a query: <ul> <li><input disabled="" type="checkbox"> Profile the original - Establish baseline metrics</li> <li><input disabled="" type="checkbox"> Identify expensive operators - Focus on highest time/DB hits</li> <li><input disabled="" type="checkbox"> Check for label scans - NodeByLabelScan often indicates missing index</li> <li><input disabled="" type="checkbox"> Look for Cartesian products - CartesianProduct is usually a bug</li> <li><input disabled="" type="checkbox"> Verify index usage - Ensure NodeIndexSeek where expected</li> <li><input disabled="" type="checkbox"> Check row counts - Large intermediate counts indicate filtering issues</li> <li><input disabled="" type="checkbox"> Test with realistic data - Small test data may hide problems</li> <li><input disabled="" type="checkbox"> Document improvements - Record before/after metrics</li> </ul> <h3 id="common-profiling-patterns" class="position-relative d-flex align-items-center group"> Common Profiling 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-profiling-patterns" aria-haspopup="dialog" aria-label="Share link: Common Profiling Patterns"> Share link </button> </h3> <h4 id="pattern-index-verification" class="position-relative d-flex align-items-center group"> Pattern: Index Verification <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="pattern-index-verification" aria-haspopup="dialog" aria-label="Share link: Pattern: Index Verification"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Check if index is used EXPLAIN MATCH (u:User {email: $email}) RETURN u // If NodeByLabelScan shown, create index: CREATE INDEX user_email ON :User(email) // Verify index usage: EXPLAIN MATCH (u:User {email: $email}) RETURN u // Should show NodeIndexSeek </code></pre></div> <h4 id="pattern-finding-hot-spots" class="position-relative d-flex align-items-center group"> Pattern: Finding Hot Spots <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="pattern-finding-hot-spots" aria-haspopup="dialog" aria-label="Share link: Pattern: Finding Hot Spots"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Profile and sort by time PROFILE MATCH (complex query here) RETURN results // Look at the Time column - operators taking >50% of total time // are optimization targets </code></pre></div> <h4 id="pattern-comparing-index-types" class="position-relative d-flex align-items-center group"> Pattern: Comparing Index Types <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="pattern-comparing-index-types" aria-haspopup="dialog" aria-label="Share link: Pattern: Comparing Index Types"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Test B-tree index CREATE INDEX user_age_btree ON :User(age) PROFILE MATCH (u:User) WHERE u.age > 25 AND u.age < 35 RETURN count(u) DROP INDEX user_age_btree // B-tree is better for range queries // Hash is better for equality-only queries </code></pre></div> <h3 id="summary" class="position-relative d-flex align-items-center group"> 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="summary" aria-haspopup="dialog" aria-label="Share link: Summary"> Share link </button> </h3>Effective query profiling follows these principles: <ol> <li>Use EXPLAIN for planning: Understand query structure before execution</li> <li>Use PROFILE for measurement: Get real execution metrics</li> <li>Read bottom-to-top: Data flows upward through operators</li> <li>Focus on bottlenecks: Optimize the slowest, most expensive operators</li> <li>Compare before/after: Quantify improvements</li> <li>Test with real data: Small datasets hide performance issues</li> <li>Document findings: Record optimizations for future reference</li> </ol> <h3 id="related-guides" class="position-relative d-flex align-items-center group"> Related Guides <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-guides" aria-haspopup="dialog" aria-label="Share link: Related Guides"> Share link </button> </h3><ul> <li><a href="/guides/query-performance/" >Query Performance Guide</a> - Writing efficient queries</li> <li><a href="/guides/index-strategy/" >Index Strategy Guide</a> - Creating effective indexes</li> <li><a href="/guides/graph-modeling/" >Graph Modeling Guide</a> - Schema design for performance</li> </ul> <hr> Questions? Discuss query profiling in our <a href="https://forum.geodedb.com" aria-label="forum – opens in new window" target="_blank" rel="noopener noreferrer" >forum ↗ </a> .

Related Guides

Query Performance Tuning Guide

Query Profiling with EXPLAIN and PROFILE