Query Execution Plans (EXPLAIN)

EXPLAIN and PROFILE are essential tools for understanding and optimizing query performance in Geode. They provide visibility into how the query planner executes your GQL queries, helping you identify bottlenecks and optimize performance. <h3 id="what-is-explain" class="position-relative d-flex align-items-center group"> What is 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="what-is-explain" aria-haspopup="dialog" aria-label="Share link: What is EXPLAIN?"> Share link </button> </h3><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>EXPLAIN shows the query execution plan without actually running the query. It reveals: <ul> <li>Execution Steps: What operations will be performed</li> <li>Order of Operations: The sequence of execution</li> <li>Index Usage: Which indexes will be used</li> <li>Estimated Costs: Relative cost estimates for each operation</li> <li>Optimization Decisions: How the query planner optimized your query</li> </ul> PROFILE goes further by executing the query and providing actual runtime statistics including row counts, execution times, and memory usage. <h3 id="basic-usage" class="position-relative d-flex align-items-center group"> Basic 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="basic-usage" aria-haspopup="dialog" aria-label="Share link: Basic Usage"> Share link </button> </h3> <h4 id="explain-syntax" class="position-relative d-flex align-items-center group"> EXPLAIN Syntax <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-syntax" aria-haspopup="dialog" aria-label="Share link: EXPLAIN Syntax"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Show execution plan without running query EXPLAIN MATCH (u:User)-[:FOLLOWS]->(f:User) WHERE u.id = 123 RETURN f.name, f.email // Output shows plan but doesn't execute query </code></pre></div> <h4 id="profile-syntax" class="position-relative d-flex align-items-center group"> PROFILE Syntax <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="profile-syntax" aria-haspopup="dialog" aria-label="Share link: PROFILE Syntax"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Show execution plan AND run query with actual statistics PROFILE MATCH (u:User)-[:FOLLOWS]->(f:User) WHERE u.id = 123 RETURN f.name, f.email // Output includes actual row counts, timing, and memory usage </code></pre></div> <h3 id="understanding-execution-plans" class="position-relative d-flex align-items-center group"> Understanding 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="understanding-execution-plans" aria-haspopup="dialog" aria-label="Share link: Understanding 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><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">EXPLAIN MATCH (u:User {email: 'alice@example.com'})-[:POSTED]->(p:Post) WHERE p.published = true RETURN p.title, p.created_at ORDER BY p.created_at DESC LIMIT 10 </code></pre></div>Example Output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">Limit (rows=10) Sort (key=p.created_at DESC) Filter (p.published = true) Expand (u)-[:POSTED]->(p:Post) NodeIndexSeek (u:User, email='[email protected]') [Index: User.email] </code></pre></div>Reading the Plan (bottom to top): <ol> <li>NodeIndexSeek: Use index on User.email to find user</li> <li>Expand: Traverse POSTED relationships to find posts</li> <li>Filter: Keep only published posts</li> <li>Sort: Order results by created_at descending</li> <li>Limit: Take only first 10 results</li> </ol> <h4 id="common-plan-operators" class="position-relative d-flex align-items-center group"> Common Plan 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-plan-operators" aria-haspopup="dialog" aria-label="Share link: Common Plan Operators"> Share link </button> </h4> <h5 id="index-operations" class="position-relative d-flex align-items-center group"> Index Operations <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="index-operations" aria-haspopup="dialog" aria-label="Share link: Index Operations"> Share link </button> </h5><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// NodeIndexSeek - Fast index lookup EXPLAIN MATCH (u:User {email: 'alice@example.com'}) RETURN u // Plan: NodeIndexSeek (u:User, email='alice@example.com') // NodeIndexScan - Scan entire index EXPLAIN MATCH (u:User) WHERE u.verified = true RETURN u // Plan: NodeIndexScan (u:User, verified=true) // NodeLabelScan - Scan all nodes with label EXPLAIN MATCH (u:User) RETURN u // Plan: NodeLabelScan (u:User) // AllNodesScan - Scan all nodes (slowest) EXPLAIN MATCH (n) RETURN n // Plan: AllNodesScan </code></pre></div> <h5 id="relationship-traversal" class="position-relative d-flex align-items-center group"> Relationship Traversal <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="relationship-traversal" aria-haspopup="dialog" aria-label="Share link: Relationship Traversal"> Share link </button> </h5><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Expand - Traverse relationships EXPLAIN MATCH (u:User)-[:FOLLOWS]->(f:User) RETURN f // Plan: Expand (u)-[:FOLLOWS]->(f:User) // VarLengthExpand - Variable-length path traversal EXPLAIN MATCH (u:User)-[:KNOWS*1..3]->(friend) RETURN friend // Plan: VarLengthExpand (u)-[:KNOWS*1..3]->(friend) // OptionalExpand - Left outer join EXPLAIN MATCH (u:User) OPTIONAL MATCH (u)-[:POSTED]->(p:Post) RETURN u, p // Plan: OptionalExpand (u)-[:POSTED]->(p:Post) </code></pre></div> <h5 id="filtering-and-processing" class="position-relative d-flex align-items-center group"> Filtering and 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="filtering-and-processing" aria-haspopup="dialog" aria-label="Share link: Filtering and Processing"> Share link </button> </h5><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Filter - Apply WHERE conditions EXPLAIN MATCH (u:User) WHERE u.age >= 18 AND u.verified = true RETURN u // Plan: Filter (u.age >= 18 AND u.verified = true) // Sort - ORDER BY clause EXPLAIN MATCH (u:User) RETURN u.name ORDER BY u.created_at DESC // Plan: Sort (u.created_at DESC) // Limit - LIMIT clause EXPLAIN MATCH (u:User) RETURN u LIMIT 10 // Plan: Limit (rows=10) // Skip - OFFSET clause EXPLAIN MATCH (u:User) RETURN u OFFSET 20 LIMIT 10 // Plan: Limit (rows=10) // Skip (rows=20) </code></pre></div> <h5 id="aggregation" class="position-relative d-flex align-items-center group"> 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="aggregation" aria-haspopup="dialog" aria-label="Share link: Aggregation"> Share link </button> </h5><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Aggregate - Grouping and aggregation EXPLAIN MATCH (u:User)-[:POSTED]->(p:Post) RETURN u.name, COUNT(p) AS post_count // Plan: Aggregate (key=[u.name], agg=[COUNT(p)]) // Distinct - Remove duplicates EXPLAIN MATCH (u:User)-[:POSTED]->(p:Post)-[:HAS_TAG]->(t:Tag) RETURN DISTINCT t.name // Plan: Distinct (t.name) </code></pre></div> <h3 id="profile-statistics" class="position-relative d-flex align-items-center group"> PROFILE Statistics <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="profile-statistics" aria-haspopup="dialog" aria-label="Share link: PROFILE Statistics"> Share link </button> </h3> <h4 id="runtime-metrics" class="position-relative d-flex align-items-center group"> Runtime Metrics <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="runtime-metrics" aria-haspopup="dialog" aria-label="Share link: Runtime Metrics"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (u:User)-[:POSTED]->(p:Post) WHERE u.verified = true AND p.published = true RETURN u.name, COUNT(p) AS posts ORDER BY posts DESC LIMIT 10 </code></pre></div>Example PROFILE Output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">Limit (rows=10) [actual_rows=10, time=0.2ms] Sort (key=posts DESC) [actual_rows=1523, time=5.4ms, memory=256KB] Aggregate (key=[u.name], agg=[COUNT(p)]) [actual_rows=1523, time=12.3ms] Filter (p.published = true) [actual_rows=42891, time=8.7ms] Expand (u)-[:POSTED]->(p:Post) [actual_rows=45203, time=15.2ms] Filter (u.verified = true) [actual_rows=8234, time=3.1ms] NodeLabelScan (u:User) [actual_rows=10000, time=2.8ms] [scanned: 10000 nodes] </code></pre></div>Key Metrics: <ul> <li>actual_rows: Number of rows produced by this step</li> <li>time: Time spent in this operation</li> <li>memory: Memory used (for operations like Sort, Aggregate)</li> <li>scanned: Number of nodes/relationships examined</li> </ul> <h4 id="identifying-performance-issues" class="position-relative d-flex align-items-center group"> Identifying Performance Issues <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-performance-issues" aria-haspopup="dialog" aria-label="Share link: Identifying Performance Issues"> Share link </button> </h4> <h5 id="high-row-count-discrepancy" class="position-relative d-flex align-items-center group"> High Row Count Discrepancy <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-count-discrepancy" aria-haspopup="dialog" aria-label="Share link: High Row Count Discrepancy"> Share link </button> </h5><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Problem: Scanning many rows, returning few PROFILE MATCH (u:User)-[:POSTED]->(p:Post) WHERE u.email = 'alice@example.com' RETURN p // Output shows: // Expand [...] [actual_rows=100] // NodeLabelScan (u:User) [actual_rows=1000000] // // ISSUE: Scanning 1M users to find 1 user! // SOLUTION: Add index on User.email </code></pre></div> <h5 id="expensive-sorts" class="position-relative d-flex align-items-center group"> Expensive Sorts <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-sorts" aria-haspopup="dialog" aria-label="Share link: Expensive Sorts"> Share link </button> </h5><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Problem: Sorting large result sets PROFILE MATCH (p:Post) RETURN p.title ORDER BY p.created_at DESC LIMIT 10 // Output shows: // Limit [...] [actual_rows=10] // Sort [...] [actual_rows=10000000, time=5000ms, memory=2GB] // // ISSUE: Sorting 10M rows to get 10 results // SOLUTION: Use index on created_at for sorted scan </code></pre></div> <h5 id="cartesian-products" class="position-relative d-flex align-items-center group"> Cartesian Products <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="cartesian-products" aria-haspopup="dialog" aria-label="Share link: Cartesian Products"> Share link </button> </h5><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Problem: Unintended Cartesian product PROFILE MATCH (u:User), (p:Post) WHERE u.location = p.location RETURN u, p // Output shows: // Filter [...] [actual_rows=5000] // CartesianProduct [actual_rows=100000000] // NodeLabelScan (u:User) [actual_rows=10000] // NodeLabelScan (p:Post) [actual_rows=10000] // // ISSUE: 10K * 10K = 100M intermediate rows! // SOLUTION: Add relationship or index-backed join </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="index-creation" class="position-relative d-flex align-items-center group"> Index Creation <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="index-creation" aria-haspopup="dialog" aria-label="Share link: Index Creation"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Identify missing index PROFILE MATCH (u:User) WHERE u.email = 'alice@example.com' RETURN u // Output shows NodeLabelScan - no index! // Plan: NodeLabelScan (u:User) [actual_rows=1000000] // Filter (u.email = 'alice@example.com') [actual_rows=1] // Solution: Create index CREATE INDEX FOR (u:User) ON (u.email) // Verify index usage EXPLAIN MATCH (u:User) WHERE u.email = 'alice@example.com' RETURN u // Plan now shows: NodeIndexSeek (u:User, email='alice@example.com') </code></pre></div> <h4 id="filter-pushdown" class="position-relative d-flex align-items-center group"> Filter Pushdown <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="filter-pushdown" aria-haspopup="dialog" aria-label="Share link: Filter Pushdown"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Before optimization PROFILE MATCH (u:User)-[:POSTED]->(p:Post) WHERE u.verified = true RETURN p // Better: Filter early PROFILE MATCH (u:User) WHERE u.verified = true MATCH (u)-[:POSTED]->(p:Post) RETURN p // Best: Use inline filter PROFILE MATCH (u:User {verified: true})-[:POSTED]->(p:Post) RETURN p </code></pre></div> <h4 id="limit-early" class="position-relative d-flex align-items-center group"> Limit Early <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="limit-early" aria-haspopup="dialog" aria-label="Share link: Limit Early"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Inefficient: Sort then limit PROFILE MATCH (u:User) RETURN u.name ORDER BY u.created_at DESC LIMIT 10 // Efficient: Use top-N optimization // (Geode automatically optimizes this pattern) PROFILE MATCH (u:User) RETURN u.name ORDER BY u.created_at DESC LIMIT 10 </code></pre></div> <h4 id="index-backed-ordering" class="position-relative d-flex align-items-center group"> Index-Backed Ordering <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="index-backed-ordering" aria-haspopup="dialog" aria-label="Share link: Index-Backed Ordering"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Create index for ordering CREATE INDEX FOR (p:Post) ON (p.created_at) // Before index EXPLAIN MATCH (p:Post) RETURN p.title ORDER BY p.created_at DESC // Plan: Sort (p.created_at DESC) // NodeLabelScan (p:Post) // After index EXPLAIN MATCH (p:Post) RETURN p.title ORDER BY p.created_at DESC // Plan: NodeIndexScan (p:Post, order=created_at DESC) // No separate Sort operation needed! </code></pre></div> <h3 id="advanced-analysis" class="position-relative d-flex align-items-center group"> Advanced 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="advanced-analysis" aria-haspopup="dialog" aria-label="Share link: Advanced Analysis"> Share link </button> </h3> <h4 id="comparing-query-alternatives" class="position-relative d-flex align-items-center group"> Comparing Query Alternatives <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-query-alternatives" aria-haspopup="dialog" aria-label="Share link: Comparing Query Alternatives"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Option 1: Aggregate then filter EXPLAIN MATCH (u:User)-[:POSTED]->(p:Post) WITH u, COUNT(p) AS post_count WHERE post_count > 10 RETURN u.name, post_count // Option 2: Filter then aggregate EXPLAIN MATCH (u:User)-[:POSTED]->(p:Post) WHERE p.published = true WITH u, COUNT(p) AS post_count WHERE post_count > 10 RETURN u.name, post_count // Compare plans to see which is more efficient </code></pre></div> <h4 id="analyzing-variable-length-paths" class="position-relative d-flex align-items-center group"> Analyzing Variable-Length Paths <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-variable-length-paths" aria-haspopup="dialog" aria-label="Share link: Analyzing Variable-Length Paths"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Check path expansion cost PROFILE MATCH path = (a:User {id: 1})-[:KNOWS*1..3]->(b:User) RETURN b.name, LENGTH(path) AS hops // Output shows: // VarLengthExpand [...] [actual_rows=15234, time=234ms] // NodeIndexSeek (a:User, id=1) [actual_rows=1] // // If too expensive, consider: // - Reducing max hops // - Adding relationship property filters // - Using shortest path instead </code></pre></div> <h4 id="subquery-performance" class="position-relative d-flex align-items-center group"> Subquery 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="subquery-performance" aria-haspopup="dialog" aria-label="Share link: Subquery Performance"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Analyze subquery efficiency PROFILE MATCH (u:User) WHERE EXISTS { MATCH (u)-[:POSTED]->(p:Post) WHERE p.likes > 100 } RETURN u.name // Look for Semi-Apply or Anti-Apply operators // Plan: SemiApply // Expand (u)-[:POSTED]->(p:Post) // Filter (p.likes > 100) // NodeLabelScan (u:User) </code></pre></div> <h3 id="real-world-optimization-examples" class="position-relative d-flex align-items-center group"> Real-World Optimization Examples <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-examples" aria-haspopup="dialog" aria-label="Share link: Real-World Optimization Examples"> Share link </button> </h3> <h4 id="example-1-slow-user-profile-query" class="position-relative d-flex align-items-center group"> Example 1: Slow User Profile Query <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-slow-user-profile-query" aria-haspopup="dialog" aria-label="Share link: Example 1: Slow User Profile Query"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Original query (slow) PROFILE MATCH (u:User)-[:POSTED]->(p:Post) WHERE u.username = 'alice' RETURN u.name, COUNT(p) AS posts // Plan shows: // Aggregate [...] [actual_rows=1] // Expand (u)-[:POSTED]->(p) [actual_rows=100] // Filter (u.username = 'alice') [actual_rows=1] // NodeLabelScan (u:User) [actual_rows=1000000, time=500ms] // Problem: Scanning 1M users // Solution: Add index CREATE INDEX FOR (u:User) ON (u.username) // Optimized query PROFILE MATCH (u:User {username: 'alice'})-[:POSTED]->(p:Post) RETURN u.name, COUNT(p) AS posts // New plan: // Aggregate [...] [actual_rows=1] // Expand (u)-[:POSTED]->(p) [actual_rows=100] // NodeIndexSeek (u:User, username='alice') [actual_rows=1, time=0.1ms] // Improvement: 5000x faster! </code></pre></div> <h4 id="example-2-expensive-recommendation-query" class="position-relative d-flex align-items-center group"> Example 2: Expensive Recommendation Query <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-expensive-recommendation-query" aria-haspopup="dialog" aria-label="Share link: Example 2: Expensive Recommendation Query"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Original query (very slow) PROFILE MATCH (u:User)-[:PURCHASED]->(p:Product)<-[:PURCHASED]-(other:User) MATCH (other)-[:PURCHASED]->(rec:Product) WHERE u.id = 123 AND NOT (u)-[:PURCHASED]->(rec) RETURN rec.name, COUNT(other) AS similar_users ORDER BY similar_users DESC LIMIT 10 // Plan shows Cartesian product and late filtering // Optimized query PROFILE MATCH (u:User {id: 123})-[:PURCHASED]->(p:Product) WITH u, COLLECT(p) AS purchased MATCH (p IN purchased)<-[:PURCHASED]-(other:User)-[:PURCHASED]->(rec:Product) WHERE NOT rec IN purchased AND rec <> p RETURN rec.name, COUNT(DISTINCT other) AS similar_users ORDER BY similar_users DESC LIMIT 10 // New plan shows early anchoring and reduced intermediate rows </code></pre></div> <h4 id="example-3-inefficient-sorting" class="position-relative d-flex align-items-center group"> Example 3: Inefficient Sorting <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-inefficient-sorting" aria-haspopup="dialog" aria-label="Share link: Example 3: Inefficient Sorting"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">// Original query PROFILE MATCH (p:Post) WHERE p.category = 'tech' RETURN p.title, p.created_at ORDER BY p.created_at DESC LIMIT 20 // Plan shows: // Limit [...] [actual_rows=20] // Sort [...] [actual_rows=500000, time=2000ms, memory=1GB] // Filter (p.category = 'tech') [actual_rows=500000] // NodeLabelScan (p:Post) [actual_rows=5000000] // Solution: Composite index CREATE INDEX FOR (p:Post) ON (p.category, p.created_at) // Optimized plan: // Limit [...] [actual_rows=20, time=0.5ms] // NodeIndexScan (p:Post, category='tech', order=created_at DESC) // [actual_rows=20] // Only reads 20 rows! </code></pre></div> <h3 id="client-library-integration" class="position-relative d-flex align-items-center group"> Client Library Integration <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="client-library-integration" aria-haspopup="dialog" aria-label="Share link: Client Library Integration"> Share link </button> </h3> <h4 id="python" class="position-relative d-flex align-items-center group"> Python <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="python" aria-haspopup="dialog" aria-label="Share link: Python"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python">from geode_client import Client client = Client(host="localhost", port=3141) async with client.connection() as conn: # Get EXPLAIN output result, _ = await conn.query(""" EXPLAIN MATCH (u:User)-[:FOLLOWS]->(f:User) WHERE u.id = $user_id RETURN f.name """, {"user_id": 123}) # Plan is in result metadata plan = result.execution_plan print(f"Plan: {plan}") # Get PROFILE statistics result, _ = await conn.query(""" PROFILE MATCH (u:User)-[:FOLLOWS]->(f:User) WHERE u.id = $user_id RETURN f.name """, {"user_id": 123}) stats = result.profile_stats print(f"Total time: {stats['total_time_ms']}ms") print(f"Rows examined: {stats['rows_examined']}") </code></pre></div> <h4 id="go" class="position-relative d-flex align-items-center group"> Go <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="go" aria-haspopup="dialog" aria-label="Share link: Go"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-go" data-lang="go">import "database/sql" import _ "geodedb.com/geode" db, _ := sql.Open("geode", "quic://localhost:3141") // Execute EXPLAIN rows, _ := db.Query(` EXPLAIN MATCH (u:User)-[:FOLLOWS]->(f:User) WHERE u.id = $1 RETURN f.name `, 123) // Read plan from result for rows.Next() { var planStep string rows.Scan(&planStep) fmt.Println(planStep) } // Execute PROFILE rows, _ = db.Query(` PROFILE MATCH (u:User)-[:FOLLOWS]->(f:User) WHERE u.id = $1 RETURN f.name `, 123) // Profile results include stats </code></pre></div> <h4 id="rust" class="position-relative d-flex align-items-center group"> Rust <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="rust" aria-haspopup="dialog" aria-label="Share link: Rust"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-rust" data-lang="rust">use geode_client::Client; let client = Client::connect("localhost:3141").await?; // Get EXPLAIN plan let result = client.query( r#" EXPLAIN MATCH (u:User)-[:FOLLOWS]->(f:User) WHERE u.id = $user_id RETURN f.name "#, &[("user_id", &123)], ).await?; println!("Execution plan: {}", result.explain_plan().unwrap()); // Get PROFILE statistics let result = client.query( r#" PROFILE MATCH (u:User)-[:FOLLOWS]->(f:User) WHERE u.id = $user_id RETURN f.name "#, &[("user_id", &123)], ).await?; let stats = result.profile_stats().unwrap(); println!("Total time: {}ms", stats.total_time_ms); println!("Rows examined: {}", stats.rows_examined); </code></pre></div> <h3 id="best-practices" class="position-relative d-flex align-items-center group"> Best Practices <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" aria-haspopup="dialog" aria-label="Share link: Best Practices"> Share link </button> </h3><ol> <li>Always EXPLAIN First: Before optimizing, understand the current plan</li> <li>Create Indexes Strategically: Add indexes for frequently filtered properties</li> <li>Use PROFILE for Real Data: Estimated costs can differ from actual performance</li> <li>Compare Alternatives: Use EXPLAIN to compare different query formulations</li> <li>Monitor Production: Track query performance over time as data grows</li> <li>Index Selectivity: Prioritize indexes on high-selectivity properties</li> <li>Avoid Over-Indexing: Too many indexes slow down writes</li> <li>Composite Indexes: Use for common multi-property filters and ordering</li> </ol> <h3 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> </h3> <h4 id="good-plans" class="position-relative d-flex align-items-center group"> Good 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="good-plans" aria-haspopup="dialog" aria-label="Share link: Good Plans"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">// Index seek with minimal rows NodeIndexSeek (u:User, email='...') [actual_rows=1] Expand (u)-[:POSTED]->(p) [actual_rows=10] // Early filtering Filter (u.verified = true) [actual_rows=1000] NodeLabelScan (u:User) [actual_rows=1500] // Index-backed ordering NodeIndexScan (p:Post, order=created_at DESC) [actual_rows=10] </code></pre></div> <h4 id="problematic-plans" class="position-relative d-flex align-items-center group"> Problematic 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="problematic-plans" aria-haspopup="dialog" aria-label="Share link: Problematic Plans"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">// Full table scan NodeLabelScan (u:User) [actual_rows=10000000] Filter (u.email = '...') [actual_rows=1] // Cartesian product CartesianProduct [actual_rows=100000000] // Large sort Sort [...] [actual_rows=5000000, memory=10GB] // Unbounded expansion VarLengthExpand (...)-[:KNOWS*]->(...) [actual_rows=50000000] </code></pre></div> <h3 id="troubleshooting-guide" class="position-relative d-flex align-items-center group"> Troubleshooting 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="troubleshooting-guide" aria-haspopup="dialog" aria-label="Share link: Troubleshooting Guide"> Share link </button> </h3> <h4 id="query-taking-too-long" class="position-relative d-flex align-items-center group"> Query Taking Too Long <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-taking-too-long" aria-haspopup="dialog" aria-label="Share link: Query Taking Too Long"> Share link </button> </h4><ol> <li>Run PROFILE to identify bottleneck</li> <li>Look for high-cost operations (Sort, Filter with many rows)</li> <li>Check if indexes are being used</li> <li>Consider query restructuring</li> </ol> <h4 id="high-memory-usage" class="position-relative d-flex align-items-center group"> High 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="high-memory-usage" aria-haspopup="dialog" aria-label="Share link: High Memory Usage"> Share link </button> </h4><ol> <li>Look for Sort operators on large result sets</li> <li>Check Aggregate operations</li> <li>Consider pagination (LIMIT/OFFSET)</li> <li>Use streaming in client libraries</li> </ol> <h4 id="unexpected-results" class="position-relative d-flex align-items-center group"> Unexpected 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="unexpected-results" aria-haspopup="dialog" aria-label="Share link: Unexpected Results"> Share link </button> </h4><ol> <li>Check actual_rows vs. expected</li> <li>Verify filter conditions are applied</li> <li>Look for Cartesian products</li> <li>Ensure correct relationship directions</li> </ol> <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>Query Language: GQL query syntax and patterns</li> <li>Indexing: Creating and managing indexes</li> <li>Performance: Query optimization strategies</li> <li>MATCH Clause: Pattern matching fundamentals</li> <li>Monitoring: Production query monitoring</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><a href="/tags/query-language/" >Query Language</a> - Complete GQL guide</li> <li><a href="/tags/performance/" >Performance</a> - Performance tuning</li> <li><a href="/tags/indexing/" >Indexing</a> - Index strategies</li> <li><a href="/tags/match/" >MATCH Clause</a> - Pattern matching</li> <li><a href="/tags/monitoring/" >Monitoring</a> - Production monitoring</li> </ul> EXPLAIN and PROFILE are indispensable tools for developing high-performance graph queries in Geode, enabling you to understand, optimize, and maintain query performance as your data grows.

Popular

Related Articles

Querying, Indexing, and Query Optimization

Query Performance Tuning Guide

Query Optimization

Query Profiling Guide

EXPLAIN Command