Query Profiling with PROFILE Command

<h2 id="query-profiling-with-profile-command" class="position-relative d-flex align-items-center group"> Query Profiling with PROFILE Command <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-with-profile-command" aria-haspopup="dialog" aria-label="Share link: Query Profiling with PROFILE Command"> 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 profiling is essential for understanding and optimizing database performance. Geode’s <code>PROFILE</code> command provides comprehensive execution metrics, timing information, and resource usage statistics to help you identify bottlenecks and optimize query performance. <h3 id="understanding-profile" class="position-relative d-flex align-items-center group"> Understanding 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="understanding-profile" aria-haspopup="dialog" aria-label="Share link: Understanding PROFILE"> Share link </button> </h3> <h4 id="what-is-query-profiling" class="position-relative d-flex align-items-center group"> What is Query Profiling? <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-query-profiling" aria-haspopup="dialog" aria-label="Share link: What is Query Profiling?"> Share link </button> </h4>Query profiling captures detailed runtime information about query execution: <ul> <li>Execution Time: Time spent in each query phase</li> <li>Row Counts: Number of rows processed at each step</li> <li>Index Usage: Which indexes were used and how effectively</li> <li>Memory Usage: Memory allocated during execution</li> <li>Cache Hits: Cache effectiveness and hit rates</li> <li>Lock Statistics: Locking behavior and contention</li> </ul> <h4 id="profile-vs-explain" class="position-relative d-flex align-items-center group"> PROFILE vs 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="profile-vs-explain" aria-haspopup="dialog" aria-label="Share link: PROFILE vs EXPLAIN"> Share link </button> </h4>While both commands analyze queries, they serve different purposes: EXPLAIN: <ul> <li>Shows the query execution plan</li> <li>Runs without executing the query</li> <li>Displays estimated costs and strategies</li> <li>Used for planning and design</li> </ul> PROFILE: <ul> <li>Executes the query and collects actual metrics</li> <li>Shows real execution times and row counts</li> <li>Reveals actual resource usage</li> <li>Used for performance tuning</li> </ul> Use <code>EXPLAIN</code> during development, <code>PROFILE</code> during optimization. <h3 id="basic-profile-usage" class="position-relative d-flex align-items-center group"> Basic PROFILE 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-profile-usage" aria-haspopup="dialog" aria-label="Share link: Basic PROFILE Usage"> Share link </button> </h3> <h4 id="simple-profiling" class="position-relative d-flex align-items-center group"> Simple Profiling <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="simple-profiling" aria-haspopup="dialog" aria-label="Share link: Simple Profiling"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (u:User) WHERE u.age > 25 RETURN u.name, u.email; </code></pre></div>Output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">+-------------------------+----------+----------+----------+ | Operation | Rows | Time(ms) | DB Hits | +-------------------------+----------+----------+----------+ | ProduceResults | 1,523 | 2.3 | 0 | | Projection | 1,523 | 1.8 | 3,046 | | Filter | 1,523 | 4.5 | 5,000 | | NodeLabelScan(User) | 5,000 | 8.1 | 5,000 | +-------------------------+----------+----------+----------+ | Total | - | 16.7 | 13,046 | +-------------------------+----------+----------+----------+ </code></pre></div> <h4 id="understanding-profile-output" class="position-relative d-flex align-items-center group"> Understanding Profile Output <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-profile-output" aria-haspopup="dialog" aria-label="Share link: Understanding Profile Output"> Share link </button> </h4>Each row represents an operation in the query execution: <ul> <li>Operation: The type of operation performed</li> <li>Rows: Actual number of rows produced by this operation</li> <li>Time(ms): Milliseconds spent in this operation</li> <li>DB Hits: Number of database lookups performed</li> </ul> <h4 id="complex-query-profiling" class="position-relative d-flex align-items-center group"> Complex Query Profiling <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="complex-query-profiling" aria-haspopup="dialog" aria-label="Share link: Complex Query Profiling"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (u:User)-[:FOLLOWS]->(f:User) WHERE u.created > '2025-01-01' AND f.verified = true WITH u, COUNT(f) AS followCount WHERE followCount > 10 RETURN u.name, followCount ORDER BY followCount DESC LIMIT 20; </code></pre></div>Output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-fallback" data-lang="fallback">+-------------------------+----------+----------+----------+ | Operation | Rows | Time(ms) | DB Hits | +-------------------------+----------+----------+----------+ | Top(20) | 20 | 0.1 | 0 | | Projection | 45 | 0.2 | 90 | | Filter(followCount>10) | 45 | 0.3 | 0 | | Aggregation | 145 | 12.5 | 0 | | Filter(verified) | 892 | 3.2 | 892 | | Expand(FOLLOWS) | 2,345 | 18.7 | 2,345 | | Filter(created) | 1,234 | 4.1 | 1,234 | | NodeLabelScan(User) | 5,000 | 8.9 | 5,000 | +-------------------------+----------+----------+----------+ | Total | - | 48.0 | 9,561 | +-------------------------+----------+----------+----------+ </code></pre></div> <h3 id="profiling-patterns" class="position-relative d-flex align-items-center group"> 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="profiling-patterns" aria-haspopup="dialog" aria-label="Share link: Profiling Patterns"> Share link </button> </h3> <h4 id="index-usage-analysis" class="position-relative d-flex align-items-center group"> Index Usage 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="index-usage-analysis" aria-haspopup="dialog" aria-label="Share link: Index Usage Analysis"> Share link </button> </h4>Profile queries to verify index usage: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Without index PROFILE MATCH (u:User) WHERE u.email = 'user@example.com' RETURN u; -- Output shows full scan: -- NodeLabelScan(User): 50,000 rows, 125ms -- Create index CREATE INDEX user_email_idx ON :User(email); -- With index PROFILE MATCH (u:User) WHERE u.email = 'user@example.com' RETURN u; -- Output shows index usage: -- NodeIndexSeek(User.email): 1 row, 0.8ms </code></pre></div>The dramatic difference in rows processed and time confirms the index is working. <h4 id="join-performance" class="position-relative d-flex align-items-center group"> Join 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="join-performance" aria-haspopup="dialog" aria-label="Share link: Join Performance"> Share link </button> </h4>Profile relationship traversals: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (p:Product)-[:IN_CATEGORY]->(c:Category) WHERE c.name = 'Electronics' RETURN p.name, p.price; </code></pre></div>Analysis Points: <ul> <li>Check if Category filter happens before or after expansion</li> <li>Verify index usage on <code>Category.name</code></li> <li>Compare row counts before and after filtering</li> <li>Identify if relationship direction affects performance</li> </ul> <h4 id="aggregation-performance" class="position-relative d-flex align-items-center group"> Aggregation 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="aggregation-performance" aria-haspopup="dialog" aria-label="Share link: Aggregation Performance"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (u:User)-[:PURCHASED]->(p:Product) WITH p.category AS category, COUNT(*) AS purchases, SUM(p.price) AS revenue RETURN category, purchases, revenue ORDER BY revenue DESC; </code></pre></div>Watch For: <ul> <li>Memory usage in aggregation step</li> <li>Sorting overhead with large result sets</li> <li>Whether grouping keys are indexed</li> <li>Impact of multiple aggregation functions</li> </ul> <h4 id="subquery-profiling" class="position-relative d-flex align-items-center group"> Subquery Profiling <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-profiling" aria-haspopup="dialog" aria-label="Share link: Subquery Profiling"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (u:User) WHERE EXISTS { MATCH (u)-[:POSTED]->(post:Post) WHERE post.likes > 100 } RETURN u.name; </code></pre></div>Profile shows subquery execution for each user, helping identify expensive existence checks. <h3 id="performance-optimization-workflow" class="position-relative d-flex align-items-center group"> Performance 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="performance-optimization-workflow" aria-haspopup="dialog" aria-label="Share link: Performance Optimization Workflow"> Share link </button> </h3> <h4 id="1-baseline-profiling" class="position-relative d-flex align-items-center group"> 1. Baseline Profiling <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="1-baseline-profiling" aria-haspopup="dialog" aria-label="Share link: 1. Baseline Profiling"> Share link </button> </h4>Establish current performance: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (u:User)-[:FRIEND]->(f:User) WHERE u.city = 'Seattle' RETURN u.name, COUNT(f) AS friendCount; </code></pre></div>Record baseline metrics: <ul> <li>Total execution time: 245ms</li> <li>DB Hits: 125,000</li> <li>Rows scanned: 50,000</li> </ul> <h4 id="2-identify-bottlenecks" class="position-relative d-flex align-items-center group"> 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="2-identify-bottlenecks" aria-haspopup="dialog" aria-label="Share link: 2. Identify Bottlenecks"> Share link </button> </h4>Look for: <ul> <li>High Time: Operations taking disproportionate time</li> <li>High DB Hits: Excessive database lookups</li> <li>Large Row Counts: Inefficient filtering order</li> <li>Full Scans: Missing indexes</li> </ul> In our example, <code>NodeLabelScan(User)</code> processes 50,000 rows when only 500 are in Seattle. <h4 id="3-apply-optimizations" class="position-relative d-flex align-items-center group"> 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="3-apply-optimizations" aria-haspopup="dialog" aria-label="Share link: 3. Apply Optimizations"> Share link </button> </h4>Create index on frequently filtered property: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE INDEX user_city_idx ON :User(city); </code></pre></div> <h4 id="4-re-profile" class="position-relative d-flex align-items-center group"> 4. Re-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="4-re-profile" aria-haspopup="dialog" aria-label="Share link: 4. Re-Profile"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (u:User)-[:FRIEND]->(f:User) WHERE u.city = 'Seattle' RETURN u.name, COUNT(f) AS friendCount; </code></pre></div>New metrics: <ul> <li>Total execution time: 18ms (13.6x faster)</li> <li>DB Hits: 1,200 (104x fewer)</li> <li>Rows scanned: 500 (100x fewer)</li> </ul> <h4 id="5-iterate" class="position-relative d-flex align-items-center group"> 5. Iterate <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="5-iterate" aria-haspopup="dialog" aria-label="Share link: 5. Iterate"> Share link </button> </h4>Continue profiling and optimizing until performance meets requirements. <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="cache-analysis" class="position-relative d-flex align-items-center group"> Cache 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="cache-analysis" aria-haspopup="dialog" aria-label="Share link: Cache Analysis"> Share link </button> </h4>Profile with cache statistics: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (u:User) WHERE u.id = $userId RETURN u; -- Second execution shows cache hits: -- NodeIndexSeek: 1 row, 0.1ms (cached) </code></pre></div> <h4 id="memory-profiling" class="position-relative d-flex align-items-center group"> Memory Profiling <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="memory-profiling" aria-haspopup="dialog" aria-label="Share link: Memory Profiling"> Share link </button> </h4>Monitor memory-intensive operations: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (u:User) WITH u ORDER BY u.name RETURN u; </code></pre></div>Large sorts show memory allocation in profile output. <h4 id="concurrent-query-profiling" class="position-relative d-flex align-items-center group"> Concurrent Query Profiling <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="concurrent-query-profiling" aria-haspopup="dialog" aria-label="Share link: Concurrent Query Profiling"> Share link </button> </h4>Profile queries under load: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"># Run multiple instances for i in {1..10}; do geode shell -e "PROFILE MATCH (u:User) RETURN COUNT(u);" & done </code></pre></div>Compare single-query vs concurrent execution times. <h4 id="pattern-comparison" class="position-relative d-flex align-items-center group"> Pattern Comparison <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-comparison" aria-haspopup="dialog" aria-label="Share link: Pattern Comparison"> Share link </button> </h4>Profile different approaches to the same query: Approach 1: Filter then expand <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (u:User) WHERE u.active = true MATCH (u)-[:FOLLOWS]->(f:User) RETURN u.name, f.name; </code></pre></div>Approach 2: Expand then filter <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (u:User)-[:FOLLOWS]->(f:User) WHERE u.active = true RETURN u.name, f.name; </code></pre></div>Compare total execution times and choose the faster approach. <h3 id="profiling-best-practices" class="position-relative d-flex align-items-center group"> Profiling 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="profiling-best-practices" aria-haspopup="dialog" aria-label="Share link: Profiling Best Practices"> Share link </button> </h3> <h4 id="development-workflow" class="position-relative d-flex align-items-center group"> Development 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="development-workflow" aria-haspopup="dialog" aria-label="Share link: Development Workflow"> Share link </button> </h4><ol> <li>Profile Early: Don’t wait until production</li> <li>Profile Often: Check performance after each change</li> <li>Profile Realistic Data: Use production-scale test data</li> <li>Profile Edge Cases: Test with empty, small, and large datasets</li> <li>Profile Under Load: Test concurrent execution</li> </ol> <h4 id="interpreting-results" class="position-relative d-flex align-items-center group"> Interpreting 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="interpreting-results" aria-haspopup="dialog" aria-label="Share link: Interpreting Results"> Share link </button> </h4>Red Flags: <ul> <li>Any operation > 100ms without data transfer</li> <li>DB Hits » Row count (inefficient access)</li> <li>Large row count early in pipeline (filter late)</li> <li>Multiple full table scans</li> </ul> Green Flags: <ul> <li>Index seeks instead of scans</li> <li>Filtering early in pipeline</li> <li>Minimal DB hits per row</li> <li>Consistent performance across executions</li> </ul> <h4 id="common-patterns" class="position-relative d-flex align-items-center group"> Common 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-patterns" aria-haspopup="dialog" aria-label="Share link: Common Patterns"> Share link </button> </h4>Pattern: Early Filtering <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GOOD: Filter first MATCH (u:User) WHERE u.role = 'admin' -- Reduces rows early MATCH (u)-[:MANAGES]->(t:Team) RETURN u.name, t.name; -- BAD: Filter late MATCH (u:User)-[:MANAGES]->(t:Team) WHERE u.role = 'admin' -- Expands all users first RETURN u.name, t.name; </code></pre></div>Pattern: Index Coverage <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Ensure indexes cover WHERE and JOIN conditions CREATE INDEX user_role_idx ON :User(role); CREATE INDEX team_manager_idx ON ()-[:MANAGES]->(); </code></pre></div>Pattern: Selective Properties <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GOOD: Return only needed properties RETURN u.name, u.email; -- BAD: Return entire node RETURN u; -- Transfers unnecessary data </code></pre></div> <h3 id="profiling-in-production" class="position-relative d-flex align-items-center group"> Profiling in Production <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-in-production" aria-haspopup="dialog" aria-label="Share link: Profiling in Production"> Share link </button> </h3> <h4 id="safe-production-profiling" class="position-relative d-flex align-items-center group"> Safe Production Profiling <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="safe-production-profiling" aria-haspopup="dialog" aria-label="Share link: Safe Production Profiling"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Profile with read-only queries PROFILE MATCH (u:User) WHERE u.lastLogin > '2025-01-01' RETURN COUNT(u); -- NEVER profile write-heavy queries in production -- (they execute and modify data) </code></pre></div> <h4 id="profiling-via-monitoring" class="position-relative d-flex align-items-center group"> Profiling via 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="profiling-via-monitoring" aria-haspopup="dialog" aria-label="Share link: Profiling via Monitoring"> Share link </button> </h4>Integrate profiling into monitoring: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python"># Python example import geode_client client = geode_client.open_database() async with client.connection() as conn: result, _ = await conn.query(""" PROFILE MATCH (u:User)-[:PURCHASED]->(p:Product) WHERE u.id = ? RETURN p.name """, user_id) # Log profile metrics metrics = result.profile_data logger.info(f"Query time: {metrics.total_time}ms") logger.info(f"DB hits: {metrics.db_hits}") </code></pre></div> <h4 id="continuous-profiling" class="position-relative d-flex align-items-center group"> Continuous Profiling <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="continuous-profiling" aria-haspopup="dialog" aria-label="Share link: Continuous Profiling"> Share link </button> </h4>Set up automated profiling: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash">#!/bin/bash # profile-queries.sh while read -r query; do echo "Profiling: $query" geode shell -e "PROFILE $query" >> profile-log.txt sleep 1 done < critical-queries.txt </code></pre></div> <h3 id="troubleshooting-with-profile" class="position-relative d-flex align-items-center group"> Troubleshooting with 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="troubleshooting-with-profile" aria-haspopup="dialog" aria-label="Share link: Troubleshooting with PROFILE"> Share link </button> </h3> <h4 id="slow-queries" class="position-relative d-flex align-items-center group"> Slow 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="slow-queries" aria-haspopup="dialog" aria-label="Share link: Slow Queries"> Share link </button> </h4>Symptom: Query takes > 1 second Diagnosis: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (u:User)-[:FRIEND*1..3]->(f:User) WHERE u.id = 'user123' RETURN f.name; </code></pre></div>Analysis: <ul> <li>Check if variable-length expansion is too deep</li> <li>Verify starting point uses index</li> <li>Consider limiting depth or adding filters</li> </ul> <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>Symptom: Out of memory errors Diagnosis: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (u:User) WITH u ORDER BY u.name -- Materializes all users RETURN u LIMIT 100; </code></pre></div>Solution: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Use index for ordering CREATE INDEX user_name_idx ON :User(name); PROFILE MATCH (u:User) RETURN u ORDER BY u.name LIMIT 100; -- Now uses index scan, no materialization </code></pre></div> <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>Symptom: Wrong number of rows returned Diagnosis: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (u:User)-[:FOLLOWS]->(f:User) WHERE u.city = 'Seattle' RETURN COUNT(DISTINCT f); </code></pre></div>Profile shows row counts at each step, revealing where rows are added/removed unexpectedly. <h3 id="integration-with-development-tools" class="position-relative d-flex align-items-center group"> Integration with Development Tools <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="integration-with-development-tools" aria-haspopup="dialog" aria-label="Share link: Integration with Development Tools"> Share link </button> </h3> <h4 id="ide-integration" class="position-relative d-flex align-items-center group"> IDE 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="ide-integration" aria-haspopup="dialog" aria-label="Share link: IDE Integration"> Share link </button> </h4>Many IDEs support profiling output: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-sql" data-lang="sql">-- Visual Studio Code with Geode extension -- Hover over PROFILE results for visual execution plan </code></pre></div> <h4 id="cicd-profiling" class="position-relative d-flex align-items-center group"> CI/CD Profiling <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="cicd-profiling" aria-haspopup="dialog" aria-label="Share link: CI/CD Profiling"> Share link </button> </h4>Integrate profiling into tests: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-go" data-lang="go">// Go example func TestQueryPerformance(t *testing.T) { result := db.Profile(` MATCH (u:User) WHERE u.active = true RETURN COUNT(u) `) if result.TotalTime > 100 { t.Errorf("Query too slow: %dms", result.TotalTime) } } </code></pre></div> <h4 id="profiling-dashboards" class="position-relative d-flex align-items-center group"> Profiling Dashboards <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-dashboards" aria-haspopup="dialog" aria-label="Share link: Profiling Dashboards"> Share link </button> </h4>Visualize profiling data: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python"># Grafana dashboard example import grafana_api def send_profile_metrics(profile_data): grafana_api.send({ 'query_time': profile_data.total_time, 'db_hits': profile_data.db_hits, 'rows_processed': profile_data.total_rows }) </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/explain/" >EXPLAIN</a> : Query execution plans without execution</li> <li><a href="/tags/performance/" >Performance</a> : General performance optimization</li> <li><a href="/tags/indexing/" >Indexing</a> : Index strategies for better performance</li> <li><a href="/tags/optimization/" >Optimization</a> : Query optimization techniques</li> <li><a href="/tags/benchmarking/" >Benchmarking</a> : Performance measurement and comparison</li> <li><a href="/tags/monitoring/" >Monitoring</a> : Production monitoring and observability</li> <li><a href="/tags/caching/" >Caching</a> : Query result caching strategies</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>PROFILE Command Reference: <code>/docs/gql-reference/profile/</code></li> <li>Performance Tuning Guide: <code>/docs/performance/tuning-guide/</code></li> <li>Index Optimization: <code>/docs/performance/index-optimization/</code></li> <li>Query Best Practices: <code>/docs/best-practices/query-optimization/</code></li> <li>Monitoring Guide: <code>/docs/operations/monitoring/</code></li> </ul> <h3 id="conclusion" class="position-relative d-flex align-items-center group"> Conclusion <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="conclusion" aria-haspopup="dialog" aria-label="Share link: Conclusion"> Share link </button> </h3>The <code>PROFILE</code> command is your most powerful tool for understanding and optimizing query performance in Geode. By systematically profiling queries, identifying bottlenecks, and applying targeted optimizations, you can achieve dramatic performance improvements. Remember: <ul> <li>Profile early and often during development</li> <li>Compare before and after optimization metrics</li> <li>Focus on the biggest bottlenecks first</li> <li>Use realistic data volumes for profiling</li> <li>Integrate profiling into your CI/CD pipeline</li> </ul> Master query profiling to build high-performance graph applications with Geode.

Popular

Related Articles

Querying, Indexing, and Query Optimization

Query Performance Tuning Guide

PROFILE Command