Query Optimization | Geode Database

<h2 id="query-optimization" class="position-relative d-flex align-items-center group"> Query Optimization <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="query-optimization" aria-haspopup="dialog" aria-label="Share link: Query Optimization"> 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>This guide covers techniques for optimizing GQL queries in Geode, including query analysis, indexing strategies, and performance tuning. <h3 id="overview" class="position-relative d-flex align-items-center group"> Overview <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="overview" aria-haspopup="dialog" aria-label="Share link: Overview"> Share link </button> </h3>Query optimization improves performance through: <table> <thead> <tr> <th>Technique</th> <th>Impact</th> <th>Effort</th> </tr> </thead> <tbody> <tr> <td>Proper indexes</td> <td>High</td> <td>Low</td> </tr> <tr> <td>Query rewriting</td> <td>High</td> <td>Medium</td> </tr> <tr> <td>Schema optimization</td> <td>High</td> <td>High</td> </tr> <tr> <td>Configuration tuning</td> <td>Medium</td> <td>Low</td> </tr> <tr> <td>Hardware upgrades</td> <td>Medium</td> <td>High</td> </tr> </tbody> </table> <h3 id="query-analysis" class="position-relative d-flex align-items-center group"> Query Analysis <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="query-analysis" aria-haspopup="dialog" aria-label="Share link: Query Analysis"> Share link </button> </h3> <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>Analyze query execution plans: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Basic EXPLAIN EXPLAIN MATCH (p:Person)-[:KNOWS]->(f:Person) WHERE p.age > 30 RETURN f.name; -- Output: -- Query Plan -- ============================================= -- NodeByLabelScan (Person, alias: p) -- └── Filter (p.age > 30) -- └── Expand (p)-[:KNOWS]->(f) -- └── Return (f.name) -- -- Estimated rows: 1500 -- Indexes used: none </code></pre></div> <h4 id="using-explain-analyze" class="position-relative d-flex align-items-center group"> Using EXPLAIN ANALYZE <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-analyze" aria-haspopup="dialog" aria-label="Share link: Using EXPLAIN ANALYZE"> Share link </button> </h4>Execute and measure actual performance: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- EXPLAIN with actual execution EXPLAIN ANALYZE MATCH (p:Person)-[:KNOWS]->(f:Person) WHERE p.age > 30 RETURN f.name; -- Output: -- Query Plan (Actual) -- ============================================= -- NodeByLabelScan (Person, alias: p) -- Rows: 10000, Time: 45ms -- └── Filter (p.age > 30) -- Rows: 3500, Selectivity: 35% -- └── Expand (p)-[:KNOWS]->(f) -- Rows: 15000, Time: 120ms -- └── Return (f.name) -- Rows: 15000, Time: 5ms -- -- Total Time: 170ms -- Rows Returned: 15000 -- Peak Memory: 12MB </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>Get detailed execution statistics: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">PROFILE MATCH (p:Person)-[:KNOWS]->(f:Person) WHERE p.age > 30 RETURN f.name LIMIT 100; -- Output: -- Profile Results -- ============================================= -- Operator | Rows | Time | Memory | Hits -- NodeByLabelScan | 10000 | 45ms | 2MB | 10000 -- Filter | 3500 | 12ms | 0.1MB | 10000 -- Expand | 15000 | 120ms | 8MB | 15000 -- Limit | 100 | 0.1ms | 0.01MB | 100 -- Return | 100 | 0.5ms | 0.1MB | 100 -- ============================================= -- Total: 177.6ms -- Index Lookups: 0 -- Cache Hits: 8500 -- Cache Misses: 1500 </code></pre></div> <h3 id="index-optimization" class="position-relative d-flex align-items-center group"> Index Optimization <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="index-optimization" aria-haspopup="dialog" aria-label="Share link: Index Optimization"> Share link </button> </h3> <h4 id="index-types" class="position-relative d-flex align-items-center group"> 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="index-types" aria-haspopup="dialog" aria-label="Share link: Index Types"> Share link </button> </h4><table> <thead> <tr> <th>Index Type</th> <th>Use Case</th> <th>Query Pattern</th> </tr> </thead> <tbody> <tr> <td>B-tree</td> <td>Range queries, sorting</td> <td><code>WHERE x > 10</code>, <code>ORDER BY x</code></td> </tr> <tr> <td>Hash</td> <td>Exact match</td> <td><code>WHERE x = 'value'</code></td> </tr> <tr> <td>Spatial (R-tree)</td> <td>Geographic queries</td> <td><code>WHERE distance(a, b) < 10</code></td> </tr> <tr> <td>Vector (HNSW)</td> <td>Similarity search</td> <td><code>WHERE similarity(v1, v2) > 0.9</code></td> </tr> <tr> <td>Full-text</td> <td>Text search</td> <td><code>WHERE content CONTAINS 'word'</code></td> </tr> <tr> <td>Patricia Trie</td> <td>Prefix matching, IP ranges</td> <td><code>WHERE ip LIKE '192.168.%'</code></td> </tr> </tbody> </table> <h4 id="creating-effective-indexes" class="position-relative d-flex align-items-center group"> Creating Effective Indexes <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="creating-effective-indexes" aria-haspopup="dialog" aria-label="Share link: Creating Effective Indexes"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- B-tree for range queries CREATE INDEX person_age_idx ON Person(age) USING btree; -- Hash for exact matches (faster than B-tree for equality) CREATE INDEX person_email_idx ON Person(email) USING hash; -- Composite index for multiple columns CREATE INDEX person_name_age_idx ON Person(name, age); -- Spatial index for geographic data CREATE INDEX location_coords_idx ON Location(coordinates) USING spatial; -- Vector index for embeddings CREATE INDEX doc_embedding_idx ON Document(embedding) USING vector WITH (M = 16, ef_construction = 200); -- Full-text index for text search CREATE INDEX article_content_idx ON Article(content) USING fulltext WITH (language = 'english', stemmer = 'porter'); </code></pre></div> <h4 id="index-selection-guidelines" class="position-relative d-flex align-items-center group"> Index Selection Guidelines <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-selection-guidelines" aria-haspopup="dialog" aria-label="Share link: Index Selection Guidelines"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GOOD: Indexed column in WHERE CREATE INDEX person_email_idx ON Person(email); MATCH (p:Person) WHERE p.email = 'alice@example.com' RETURN p; -- Uses index scan: O(log n) -- BAD: Function on indexed column prevents index use MATCH (p:Person) WHERE lower(p.email) = 'alice@example.com' RETURN p; -- Full table scan: O(n) -- BETTER: Create functional index CREATE INDEX person_email_lower_idx ON Person(lower(email)); </code></pre></div> <h4 id="verifying-index-usage" class="position-relative d-flex align-items-center group"> Verifying Index 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="verifying-index-usage" aria-haspopup="dialog" aria-label="Share link: Verifying Index Usage"> 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 (p:Person) WHERE p.email = 'alice@example.com' RETURN p; -- Good output: "IndexSeek (person_email_idx)" -- Bad output: "NodeByLabelScan (Person)" -- List all indexes SHOW INDEXES; -- Check index statistics SHOW INDEX STATISTICS FOR person_email_idx; </code></pre></div> <h3 id="query-patterns" class="position-relative d-flex align-items-center group"> Query 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="query-patterns" aria-haspopup="dialog" aria-label="Share link: Query Patterns"> Share link </button> </h3> <h4 id="pattern-anchor-early" class="position-relative d-flex align-items-center group"> Pattern: Anchor 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="pattern-anchor-early" aria-haspopup="dialog" aria-label="Share link: Pattern: Anchor Early"> Share link </button> </h4>Start patterns with indexed lookups: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GOOD: Anchor with indexed property first MATCH (p:Person {email: 'alice@example.com'})-[:KNOWS]->(f) RETURN f; -- Index lookup -> Expand (fast) -- BAD: No anchor, scans all relationships MATCH (p:Person)-[:KNOWS]->(f) WHERE p.email = 'alice@example.com' RETURN f; -- May scan all Person nodes </code></pre></div> <h4 id="pattern-limit-path-depth" class="position-relative d-flex align-items-center group"> Pattern: Limit Path Depth <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-limit-path-depth" aria-haspopup="dialog" aria-label="Share link: Pattern: Limit Path Depth"> Share link </button> </h4>Bound variable-length paths: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GOOD: Bounded path length MATCH (a)-[:KNOWS*1..3]->(b) RETURN b; -- Predictable expansion -- BAD: Unbounded path (can explode) MATCH (a)-[:KNOWS*]->(b) RETURN b; -- May traverse entire graph -- BETTER: Use shortest path for specific queries MATCH path = shortestPath((a)-[:KNOWS*1..10]->(b)) WHERE a.email = 'alice@example.com' AND b.email = 'bob@example.com' RETURN path; </code></pre></div> <h4 id="pattern-filter-early" class="position-relative d-flex align-items-center group"> Pattern: Filter 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="pattern-filter-early" aria-haspopup="dialog" aria-label="Share link: Pattern: Filter Early"> Share link </button> </h4>Push filters as early as possible: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GOOD: Filter in MATCH MATCH (p:Person {status: 'active'})-[:WORKS_AT]->(c:Company) WHERE c.industry = 'Tech' RETURN p, c; -- Filters applied during traversal -- BAD: Filter after collecting all data MATCH (p:Person)-[:WORKS_AT]->(c:Company) WHERE p.status = 'active' AND c.industry = 'Tech' RETURN p, c; -- May collect more data than needed </code></pre></div> <h4 id="pattern-use-limit-with-order-by" class="position-relative d-flex align-items-center group"> Pattern: Use LIMIT with ORDER BY <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-use-limit-with-order-by" aria-haspopup="dialog" aria-label="Share link: Pattern: Use LIMIT with ORDER BY"> Share link </button> </h4>Always use ORDER BY with LIMIT for deterministic results: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GOOD: Deterministic results MATCH (p:Person) RETURN p.name, p.age ORDER BY p.age DESC LIMIT 10; -- BAD: Non-deterministic (different results each run) MATCH (p:Person) RETURN p.name, p.age LIMIT 10; </code></pre></div> <h4 id="pattern-avoid-cartesian-products" class="position-relative d-flex align-items-center group"> Pattern: Avoid 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="pattern-avoid-cartesian-products" aria-haspopup="dialog" aria-label="Share link: Pattern: Avoid Cartesian Products"> Share link </button> </h4>Ensure patterns are connected: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- BAD: Cartesian product (n * m combinations) MATCH (p:Person), (c:Company) WHERE p.company_id = c.id RETURN p, c; -- Computes all Person x Company pairs first -- GOOD: Connected pattern MATCH (p:Person)-[:WORKS_AT]->(c:Company) RETURN p, c; -- Direct traversal </code></pre></div> <h3 id="aggregation-optimization" class="position-relative d-flex align-items-center group"> Aggregation Optimization <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="aggregation-optimization" aria-haspopup="dialog" aria-label="Share link: Aggregation Optimization"> Share link </button> </h3> <h4 id="push-filters-before-aggregation" class="position-relative d-flex align-items-center group"> Push Filters Before 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="push-filters-before-aggregation" aria-haspopup="dialog" aria-label="Share link: Push Filters Before Aggregation"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GOOD: Filter before aggregation MATCH (p:Person) WHERE p.status = 'active' RETURN p.city, count(*) AS population GROUP BY p.city; -- Filters first, then aggregates -- BAD: Aggregate then filter (if possible to rewrite) MATCH (p:Person) RETURN p.city, count(*) AS population, p.status GROUP BY p.city, p.status HAVING p.status = 'active'; </code></pre></div> <h4 id="use-covering-indexes" class="position-relative d-flex align-items-center group"> Use Covering Indexes <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="use-covering-indexes" aria-haspopup="dialog" aria-label="Share link: Use Covering Indexes"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Create composite index CREATE INDEX person_city_status_idx ON Person(city, status); -- Query uses index only (no table access) MATCH (p:Person) WHERE p.status = 'active' RETURN p.city, count(*) AS count GROUP BY p.city; </code></pre></div> <h4 id="pre-aggregate-with-materialized-views" class="position-relative d-flex align-items-center group"> Pre-aggregate with Materialized Views <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="pre-aggregate-with-materialized-views" aria-haspopup="dialog" aria-label="Share link: Pre-aggregate with Materialized Views"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Create materialized view for common aggregation CREATE MATERIALIZED VIEW city_population AS SELECT city, count(*) AS population FROM (MATCH (p:Person) WHERE p.status = 'active' RETURN p.city AS city) GROUP BY city; -- Query the materialized view SELECT * FROM city_population WHERE city = 'New York'; </code></pre></div> <h3 id="join-optimization" class="position-relative d-flex align-items-center group"> Join Optimization <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="join-optimization" aria-haspopup="dialog" aria-label="Share link: Join Optimization"> Share link </button> </h3> <h4 id="choose-optimal-join-order" class="position-relative d-flex align-items-center group"> Choose Optimal Join 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="choose-optimal-join-order" aria-haspopup="dialog" aria-label="Share link: Choose Optimal Join Order"> Share link </button> </h4>The optimizer chooses join order, but hints can help: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Smaller table first (better for nested loop join) MATCH (small:Rare)-[:RELATES_TO]->(large:Common) RETURN small, large; -- Use EXPLAIN to verify join order EXPLAIN MATCH (a:TypeA)-[:REL]->(b:TypeB) RETURN a, b; </code></pre></div> <h4 id="use-relationship-direction" class="position-relative d-flex align-items-center group"> Use Relationship Direction <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="use-relationship-direction" aria-haspopup="dialog" aria-label="Share link: Use Relationship Direction"> Share link </button> </h4>Specify direction when known: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GOOD: Explicit direction MATCH (a)-[:KNOWS]->(b) RETURN b; -- One direction traversal -- SLOWER: Both directions MATCH (a)-[:KNOWS]-(b) RETURN b; -- Checks both directions </code></pre></div> <h3 id="subquery-optimization" class="position-relative d-flex align-items-center group"> Subquery Optimization <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="subquery-optimization" aria-haspopup="dialog" aria-label="Share link: Subquery Optimization"> Share link </button> </h3> <h4 id="exists-vs-count" class="position-relative d-flex align-items-center group"> EXISTS vs COUNT <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="exists-vs-count" aria-haspopup="dialog" aria-label="Share link: EXISTS vs COUNT"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- GOOD: EXISTS for existence check MATCH (p:Person) WHERE EXISTS { MATCH (p)-[:OWNS]->(c:Car) } RETURN p; -- Stops at first match -- BAD: COUNT for existence check MATCH (p:Person) WHERE (MATCH (p)-[:OWNS]->(c:Car) RETURN count(c)) > 0 RETURN p; -- Counts all cars </code></pre></div> <h4 id="correlated-vs-non-correlated" class="position-relative d-flex align-items-center group"> Correlated vs Non-Correlated <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="correlated-vs-non-correlated" aria-haspopup="dialog" aria-label="Share link: Correlated vs Non-Correlated"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Non-correlated subquery (computed once) MATCH (p:Person) WHERE p.salary > ( SELECT avg(salary) FROM (MATCH (x:Person) RETURN x.salary AS salary) ) RETURN p; -- Correlated subquery (computed per row - slower) MATCH (p:Person) WHERE p.salary > ( MATCH (p)-[:WORKS_AT]->(c:Company) RETURN avg(c.avg_salary) ) RETURN p; </code></pre></div> <h3 id="configuration-tuning" class="position-relative d-flex align-items-center group"> Configuration Tuning <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="configuration-tuning" aria-haspopup="dialog" aria-label="Share link: Configuration Tuning"> Share link </button> </h3> <h4 id="query-optimizer-settings" class="position-relative d-flex align-items-center group"> Query Optimizer Settings <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-optimizer-settings" aria-haspopup="dialog" aria-label="Share link: Query Optimizer Settings"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-yaml" data-lang="yaml"># geode.yaml optimizer: # Cost model parameters cost_model: seq_scan_cost: 1.0 # Sequential scan cost per row index_scan_cost: 0.1 # Index scan cost per row join_cost: 2.0 # Join cost multiplier # Optimization limits max_optimization_time_ms: 1000 # Max time for optimization max_join_permutations: 10000 # Max join orders to consider # Statistics statistics_sample_size: 10000 # Rows to sample for stats auto_analyze: true # Auto-update statistics </code></pre></div> <h4 id="memory-settings" class="position-relative d-flex align-items-center group"> Memory Settings <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-settings" aria-haspopup="dialog" aria-label="Share link: Memory Settings"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-yaml" data-lang="yaml"># geode.yaml memory: # Query execution memory query_memory_limit: '1GB' # Per-query memory limit sort_memory: '256MB' # Memory for sorting hash_join_memory: '512MB' # Memory for hash joins # Page cache page_cache_size: '4GB' # Page cache for data </code></pre></div> <h4 id="parallelism" class="position-relative d-flex align-items-center group"> Parallelism <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="parallelism" aria-haspopup="dialog" aria-label="Share link: Parallelism"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-yaml" data-lang="yaml"># geode.yaml parallelism: max_parallel_queries: 100 # Concurrent queries parallel_workers: 8 # Workers per parallel query parallel_threshold: 10000 # Min rows for parallel scan </code></pre></div> <h3 id="slow-query-analysis" class="position-relative d-flex align-items-center group"> Slow Query Analysis <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="slow-query-analysis" aria-haspopup="dialog" aria-label="Share link: Slow Query Analysis"> Share link </button> </h3> <h4 id="enable-slow-query-logging" class="position-relative d-flex align-items-center group"> Enable Slow Query Logging <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="enable-slow-query-logging" aria-haspopup="dialog" aria-label="Share link: Enable Slow Query Logging"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-yaml" data-lang="yaml"># geode.yaml logging: slow_query: enabled: true threshold_ms: 1000 # Log queries > 1 second log_plan: true # Include query plan log_file: /var/log/geode/slow-queries.log </code></pre></div> <h4 id="analyzing-slow-queries" class="position-relative d-flex align-items-center group"> Analyzing 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="analyzing-slow-queries" aria-haspopup="dialog" aria-label="Share link: Analyzing Slow Queries"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"># Find slowest queries grep "duration_ms" /var/log/geode/slow-queries.log | \ jq -s 'sort_by(.duration_ms) | reverse | .[0:10]' # Find most frequent slow queries grep "query_hash" /var/log/geode/slow-queries.log | \ jq -s 'group_by(.query_hash) | map({hash: .[0].query_hash, count: length}) | sort_by(.count) | reverse' </code></pre></div> <h4 id="query-performance-metrics" class="position-relative d-flex align-items-center group"> Query Performance 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="query-performance-metrics" aria-haspopup="dialog" aria-label="Share link: Query Performance Metrics"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-promql" data-lang="promql"># Slow query rate rate(geode_query_duration_seconds_bucket{le="1"}[5m]) / rate(geode_query_duration_seconds_count[5m]) # Query latency percentiles histogram_quantile(0.99, rate(geode_query_duration_seconds_bucket[5m])) # Queries by type rate(geode_query_total[5m]) by (query_type) </code></pre></div> <h3 id="common-anti-patterns" class="position-relative d-flex align-items-center group"> Common Anti-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-anti-patterns" aria-haspopup="dialog" aria-label="Share link: Common Anti-Patterns"> Share link </button> </h3> <h4 id="anti-pattern-select-" class="position-relative d-flex align-items-center group"> Anti-Pattern: SELECT * <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="anti-pattern-select-" aria-haspopup="dialog" aria-label="Share link: Anti-Pattern: SELECT *"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- BAD: Returns all properties MATCH (p:Person) RETURN p; -- GOOD: Return only needed properties MATCH (p:Person) RETURN p.name, p.email; </code></pre></div> <h4 id="anti-pattern-n1-queries" class="position-relative d-flex align-items-center group"> Anti-Pattern: N+1 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="anti-pattern-n1-queries" aria-haspopup="dialog" aria-label="Share link: Anti-Pattern: N&#43;1 Queries"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- BAD: One query per person (N+1 problem) // Application code: // for person in get_all_persons(): // friends = query("MATCH (p)-[:KNOWS]->(f) WHERE p.id = $id RETURN f", id=person.id) -- GOOD: Single query with all data MATCH (p:Person)-[:KNOWS]->(f:Person) RETURN p.id, collect(f) AS friends; </code></pre></div> <h4 id="anti-pattern-unbounded-collections" class="position-relative d-flex align-items-center group"> Anti-Pattern: Unbounded Collections <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="anti-pattern-unbounded-collections" aria-haspopup="dialog" aria-label="Share link: Anti-Pattern: Unbounded Collections"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- BAD: Collect all (memory explosion) MATCH (p:Person)-[:KNOWS]->(f) RETURN p.name, collect(f) AS all_friends; -- GOOD: Limit collection size MATCH (p:Person)-[:KNOWS]->(f) RETURN p.name, collect(f)[0..100] AS friends; </code></pre></div> <h3 id="best-practices-summary" class="position-relative d-flex align-items-center group"> Best Practices Summary <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="best-practices-summary" aria-haspopup="dialog" aria-label="Share link: Best Practices Summary"> Share link </button> </h3> <h4 id="do" class="position-relative d-flex align-items-center group"> Do <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="do" aria-haspopup="dialog" aria-label="Share link: Do"> Share link </button> </h4><ol> <li>Create indexes for frequently queried properties</li> <li>Use EXPLAIN to verify query plans</li> <li>Anchor patterns with indexed lookups</li> <li>Bound variable-length paths</li> <li>Filter early in the query</li> <li>Use LIMIT with ORDER BY</li> <li>Return only needed properties</li> <li>Use EXISTS for existence checks</li> </ol> <h4 id="avoid" class="position-relative d-flex align-items-center group"> Avoid <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="avoid" aria-haspopup="dialog" aria-label="Share link: Avoid"> Share link </button> </h4><ol> <li>Functions on indexed columns in WHERE</li> <li>Unbounded path expansion</li> <li>Cartesian products</li> <li>SELECT * when specific columns needed</li> <li>N+1 query patterns</li> <li>Unbounded collections</li> <li>Sorting large result sets without indexes</li> </ol> <h3 id="troubleshooting" class="position-relative d-flex align-items-center group"> Troubleshooting <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" aria-haspopup="dialog" aria-label="Share link: Troubleshooting"> Share link </button> </h3> <h4 id="query-takes-too-long" class="position-relative d-flex align-items-center group"> Query Takes 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-takes-too-long" aria-haspopup="dialog" aria-label="Share link: Query Takes Too Long"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"># Check query plan geode query "EXPLAIN ANALYZE <your-query>" # Common causes: # 1. Missing index -> Create appropriate index # 2. Cartesian product -> Rewrite with connected patterns # 3. Unbounded path -> Add depth limit # 4. Large result set -> Add LIMIT </code></pre></div> <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><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"># Check query memory geode query "PROFILE <your-query>" | grep Memory # Reduce memory: # 1. Add LIMIT to reduce result size # 2. Use pagination # 3. Avoid large collect() operations # 4. Increase query_memory_limit if needed </code></pre></div> <h4 id="query-returns-wrong-results" class="position-relative d-flex align-items-center group"> Query Returns Wrong 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="query-returns-wrong-results" aria-haspopup="dialog" aria-label="Share link: Query Returns Wrong Results"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"># Verify query logic geode query "EXPLAIN <your-query>" # Check for: # 1. Missing relationship direction # 2. Incorrect property names # 3. Type mismatches in comparisons </code></pre></div> <h3 id="related-documentation" class="position-relative d-flex align-items-center group"> Related Documentation <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-documentation" aria-haspopup="dialog" aria-label="Share link: Related Documentation"> Share link </button> </h3><ul> <li><a href="/docs/query/indexing-and-optimization/" >Indexing and Optimization</a> - Index reference</li> <li><a href="/docs/query/explain-profile/" >EXPLAIN and PROFILE</a> - Query analysis</li> <li><a href="/docs/performance/" >Performance Tuning</a> - System tuning</li> <li><a href="/docs/performance/benchmarking/" >Benchmarking</a> - Performance testing</li> </ul>