fraiseql_cli/schema/
optimizer.rs

1//! Schema Optimizer
2//!
3//! Analyzes compiled schemas and adds SQL generation hints for runtime optimization.
4//! This runs during compilation to precompute optimization strategies.
5
6use anyhow::Result;
7use fraiseql_core::{
8    db::types::DatabaseType,
9    schema::{CompiledSchema, QueryDefinition, SqlProjectionHint, TypeDefinition},
10};
11use tracing::{debug, info};
12
13/// Schema optimizer that analyzes queries and adds SQL hints
14pub struct SchemaOptimizer;
15
16impl SchemaOptimizer {
17    /// Optimize a compiled schema
18    ///
19    /// This analyzes queries and adds optimization hints like:
20    /// - Index suggestions
21    /// - Join order hints
22    /// - Projection optimization
23    /// - Predicate pushdown opportunities
24    ///
25    /// # Errors
26    ///
27    /// Currently infallible; returns `Ok` in all cases. The `Result` return
28    /// type is reserved for future optimizations that may produce errors.
29    pub fn optimize(schema: &mut CompiledSchema) -> Result<OptimizationReport> {
30        info!("Optimizing compiled schema");
31
32        let mut report = OptimizationReport::default();
33
34        // Analyze queries for optimization opportunities
35        for query in &schema.queries {
36            Self::analyze_query(query, &mut report);
37        }
38
39        // Analyze types for field access patterns and SQL projection opportunities
40        Self::analyze_types(schema, &mut report);
41
42        // Detect and apply SQL projection hints to types that would benefit
43        Self::apply_sql_projection_hints(schema, &mut report);
44
45        info!("Schema optimization complete: {} hints generated", report.total_hints());
46
47        Ok(report)
48    }
49
50    /// Analyze a single query for optimization opportunities
51    fn analyze_query(query: &QueryDefinition, report: &mut OptimizationReport) {
52        debug!("Analyzing query: {}", query.name);
53
54        // Check if query would benefit from indexes
55        if query.returns_list && !query.arguments.is_empty() {
56            report.index_hints.push(IndexHint {
57                query_name:        query.name.clone(),
58                reason:            "List query with arguments benefits from index".to_string(),
59                suggested_columns: query.arguments.iter().map(|arg| arg.name.clone()).collect(),
60            });
61        }
62
63        // Check for auto-params that suggest filtering
64        if query.auto_params.has_where {
65            report.optimization_notes.push(format!(
66                "Query '{}' supports WHERE filtering - ensure denormalized filter columns exist",
67                query.name
68            ));
69        }
70
71        // Check for pagination
72        if query.auto_params.has_limit || query.auto_params.has_offset {
73            report.optimization_notes.push(format!(
74                "Query '{}' supports pagination - consider adding ORDER BY for deterministic results",
75                query.name
76            ));
77        }
78    }
79
80    /// Analyze types for field access patterns
81    fn analyze_types(schema: &CompiledSchema, report: &mut OptimizationReport) {
82        for type_def in &schema.types {
83            // Check for large number of fields (potential over-fetching)
84            if type_def.fields.len() > 20 {
85                report.optimization_notes.push(format!(
86                    "Type '{}' has {} fields - consider field selection optimization",
87                    type_def.name,
88                    type_def.fields.len()
89                ));
90            }
91
92            // Check for JSONB columns
93            if !type_def.jsonb_column.is_empty() {
94                report.optimization_notes.push(format!(
95                    "Type '{}' uses JSONB column '{}' - ensure GIN index exists for performance",
96                    type_def.name, type_def.jsonb_column
97                ));
98            }
99        }
100    }
101
102    /// Detect and apply SQL projection hints to types that would benefit from SQL-level field
103    /// projection.
104    ///
105    /// SQL projection optimization works by filtering JSONB fields at the database level,
106    /// reducing network payload and JSON deserialization overhead.
107    ///
108    /// Detection heuristics:
109    /// - Type must have a JSONB column
110    /// - Type should have sufficient fields (>10) or estimated large payload (>1KB)
111    /// - PostgreSQL benefit: reduced payload and latency (proportional to fields omitted; run
112    ///   `cargo bench --bench sql_projection_benchmark` for hardware-specific numbers)
113    fn apply_sql_projection_hints(schema: &mut CompiledSchema, report: &mut OptimizationReport) {
114        for type_def in &mut schema.types {
115            if Self::should_use_projection(type_def) {
116                let hint = Self::create_projection_hint(type_def);
117
118                debug!(
119                    "Type '{}' qualifies for SQL projection: {} bytes saved ({:.0}%)",
120                    type_def.name,
121                    Self::estimate_payload_savings(type_def),
122                    hint.estimated_reduction_percent
123                );
124
125                type_def.sql_projection_hint = Some(hint);
126                report.projection_hints.push(ProjectionHint {
127                    type_name:                   type_def.name.to_string(),
128                    field_count:                 type_def.fields.len(),
129                    estimated_reduction_percent: type_def
130                        .sql_projection_hint
131                        .as_ref()
132                        .map_or(0, |h| h.estimated_reduction_percent),
133                });
134            }
135        }
136    }
137
138    /// Determine if a type should use SQL projection optimization.
139    ///
140    /// A type qualifies for SQL projection if:
141    /// 1. It has a JSONB column (`store_format` == "jsonb")
142    /// 2. It has sufficient fields (>10) OR estimated large payload (>1KB)
143    ///
144    /// Rationale: SQL projection's benefit (reducing JSONB payload) is most valuable
145    /// for types with many fields or large payloads. Small types don't benefit enough
146    /// to justify the SQL generation overhead.
147    fn should_use_projection(type_def: &TypeDefinition) -> bool {
148        // Condition 1: Must have JSONB column
149        if type_def.jsonb_column.is_empty() {
150            return false;
151        }
152
153        // Condition 2a: Sufficient field count (>10 fields = likely significant overhead)
154        if type_def.fields.len() > 10 {
155            return true;
156        }
157
158        // Condition 2b: Likely large payload (estimate ~150 bytes per field)
159        // Average field: id (50B) + name (100B) + value (100B) = 250B overhead
160        // 1KB threshold = ~4+ fields of average size
161        let estimated_size = type_def.fields.len() * 250;
162        if estimated_size > 1024 {
163            return true;
164        }
165
166        false
167    }
168
169    /// Create a SQL projection hint for PostgreSQL.
170    ///
171    /// The hint contains:
172    /// - Database type: "postgresql"
173    /// - Projection template: `jsonb_build_object('field1', data->>'field1', ...)`
174    /// - Estimated reduction: Based on field count and typical JSONB overhead
175    fn create_projection_hint(type_def: &TypeDefinition) -> SqlProjectionHint {
176        // Rough estimate: each unselected field ≈ 250 bytes saved (conservative).
177        // Actual reduction varies by schema and query pattern; not a guaranteed figure.
178        let estimated_reduction = Self::estimate_reduction_percent(type_def.fields.len());
179
180        SqlProjectionHint {
181            database:                    DatabaseType::PostgreSQL,
182            projection_template:         Self::generate_postgresql_projection_template(type_def),
183            estimated_reduction_percent: estimated_reduction,
184        }
185    }
186
187    /// Estimate the percentage of payload that can be reduced through SQL projection.
188    ///
189    /// Based on benchmarks:
190    /// - Baseline payload: ~9.8 KB for typical large type
191    /// - Projected payload: ~450 B (select 5 key fields)
192    /// - Reduction: 95.4%
193    ///
194    /// Conservative scaling formula:
195    /// - Few fields (5-10): 40% reduction (mostly JSONB overhead, few wasted fields)
196    /// - Many fields (11-20): 70% reduction (more unselected fields)
197    /// - Very many fields (20+): 85% reduction (mostly unnecessary data)
198    const fn estimate_reduction_percent(field_count: usize) -> u32 {
199        match field_count {
200            0..=10 => 40,
201            11..=20 => 70,
202            _ => 85,
203        }
204    }
205
206    /// Estimate total payload savings in bytes for a type.
207    fn estimate_payload_savings(type_def: &TypeDefinition) -> usize {
208        let estimated_reduction = Self::estimate_reduction_percent(type_def.fields.len());
209        // Assume baseline JSONB payload ~250 bytes per field
210        let total_payload = type_def.fields.len() * 250;
211        (total_payload * estimated_reduction as usize) / 100
212    }
213
214    /// Generate a PostgreSQL `jsonb_build_object` template for SQL projection.
215    ///
216    /// Example output:
217    /// `jsonb_build_object`('id', data->>'id', 'name', data->>'name', 'email', data->>'email')
218    ///
219    /// Note: This is a template. At runtime, the adapter will:
220    /// 1. Receive the requested GraphQL fields
221    /// 2. Filter to only include requested fields
222    /// 3. Generate the actual SQL with selected fields only
223    fn generate_postgresql_projection_template(type_def: &TypeDefinition) -> String {
224        if type_def.fields.is_empty() {
225            // Edge case: type with no fields, use pass-through
226            "data".to_string()
227        } else {
228            // Create template with first N fields (up to 20 as representative)
229            let field_list: Vec<String> = type_def
230                .fields
231                .iter()
232                .take(20)
233                .map(|f| format!("'{}', data->>'{}' ", f.name, f.name))
234                .collect();
235
236            format!("jsonb_build_object({})", field_list.join(","))
237        }
238    }
239}
240
241/// Optimization report generated during compilation
242#[derive(Debug, Default)]
243pub struct OptimizationReport {
244    /// Index suggestions for query performance
245    pub index_hints:        Vec<IndexHint>,
246    /// SQL projection hints for types that would benefit from JSONB field filtering
247    pub projection_hints:   Vec<ProjectionHint>,
248    /// General optimization notes
249    pub optimization_notes: Vec<String>,
250}
251
252impl OptimizationReport {
253    /// Get total number of optimization hints
254    pub fn total_hints(&self) -> usize {
255        self.index_hints.len() + self.projection_hints.len() + self.optimization_notes.len()
256    }
257
258    /// Check if there are any optimization suggestions
259    pub fn has_suggestions(&self) -> bool {
260        !self.index_hints.is_empty()
261            || !self.projection_hints.is_empty()
262            || !self.optimization_notes.is_empty()
263    }
264
265    /// Print report to stdout
266    pub fn print(&self) {
267        if !self.has_suggestions() {
268            return;
269        }
270
271        println!("\nOptimization Suggestions:");
272
273        if !self.index_hints.is_empty() {
274            println!("\n  Indexes:");
275            for hint in &self.index_hints {
276                println!("  • Query '{}': {}", hint.query_name, hint.reason);
277                println!("    Columns: {}", hint.suggested_columns.join(", "));
278            }
279        }
280
281        if !self.projection_hints.is_empty() {
282            println!("\n  SQL Projection Optimization:");
283            for hint in &self.projection_hints {
284                println!(
285                    "  • Type '{}' ({} fields): ~{}% payload reduction",
286                    hint.type_name, hint.field_count, hint.estimated_reduction_percent
287                );
288            }
289        }
290
291        if !self.optimization_notes.is_empty() {
292            println!("\n  Notes:");
293            for note in &self.optimization_notes {
294                println!("  • {note}");
295            }
296        }
297
298        println!();
299    }
300}
301
302/// Index hint for query optimization
303#[derive(Debug, Clone)]
304pub struct IndexHint {
305    /// Query name that would benefit from index
306    pub query_name:        String,
307    /// Reason for the suggestion
308    pub reason:            String,
309    /// Suggested columns to index
310    pub suggested_columns: Vec<String>,
311}
312
313/// SQL projection hint for type optimization
314#[derive(Debug, Clone)]
315pub struct ProjectionHint {
316    /// Type name that would benefit from SQL projection
317    pub type_name:                   String,
318    /// Number of fields in the type
319    pub field_count:                 usize,
320    /// Estimated payload reduction percentage (0-100)
321    pub estimated_reduction_percent: u32,
322}
323
324#[allow(clippy::unwrap_used)] // Reason: test code, panics are acceptable
325#[cfg(test)]
326mod tests {
327    use std::collections::HashMap;
328
329    use fraiseql_core::{
330        schema::{
331            ArgumentDefinition, AutoParams, CursorType, FieldDefinition, FieldDenyPolicy,
332            FieldType, TypeDefinition,
333        },
334        validation::CustomTypeRegistry,
335    };
336    use indexmap::IndexMap;
337
338    use super::*;
339
340    #[test]
341    fn test_optimize_empty_schema() {
342        let mut schema = CompiledSchema {
343            types: vec![],
344            enums: vec![],
345            input_types: vec![],
346            interfaces: vec![],
347            unions: vec![],
348            queries: vec![],
349            mutations: vec![],
350            subscriptions: vec![],
351            directives: vec![],
352            observers: Vec::new(),
353            fact_tables: HashMap::default(),
354            federation: None,
355            security: None,
356            observers_config: None,
357            subscriptions_config: None,
358            validation_config: None,
359            debug_config: None,
360            mcp_config: None,
361            schema_sdl: None,
362            schema_format_version: None,
363            custom_scalars: CustomTypeRegistry::default(),
364            ..Default::default()
365        };
366
367        let report = SchemaOptimizer::optimize(&mut schema).unwrap();
368        assert_eq!(report.total_hints(), 0);
369    }
370
371    #[test]
372    fn test_index_hint_for_list_query() {
373        let mut schema = CompiledSchema {
374            types: vec![],
375            enums: vec![],
376            input_types: vec![],
377            interfaces: vec![],
378            unions: vec![],
379            queries: vec![QueryDefinition {
380                name:                "users".to_string(),
381                return_type:         "User".to_string(),
382                returns_list:        true,
383                nullable:            false,
384                arguments:           vec![ArgumentDefinition {
385                    name:          "status".to_string(),
386                    arg_type:      FieldType::String,
387                    nullable:      false,
388                    default_value: None,
389                    description:   None,
390                    deprecation:   None,
391                }],
392                sql_source:          Some("users".to_string()),
393                description:         None,
394                auto_params:         AutoParams::default(),
395                deprecation:         None,
396                jsonb_column:        "data".to_string(),
397                relay:               false,
398                relay_cursor_column: None,
399                relay_cursor_type:   CursorType::default(),
400                inject_params:       IndexMap::default(),
401                cache_ttl_seconds:   None,
402                additional_views:    vec![],
403                requires_role:       None,
404                rest_path:           None,
405                rest_method:         None,
406                native_columns:      HashMap::new(),
407            }],
408            mutations: vec![],
409            subscriptions: vec![],
410            directives: vec![],
411            observers: Vec::new(),
412            fact_tables: HashMap::default(),
413            federation: None,
414            security: None,
415            observers_config: None,
416            subscriptions_config: None,
417            validation_config: None,
418            debug_config: None,
419            mcp_config: None,
420            schema_sdl: None,
421            schema_format_version: None,
422            custom_scalars: CustomTypeRegistry::default(),
423            ..Default::default()
424        };
425
426        let report = SchemaOptimizer::optimize(&mut schema).unwrap();
427        assert!(report.total_hints() > 0);
428        assert!(!report.index_hints.is_empty());
429        assert_eq!(report.index_hints[0].query_name, "users");
430    }
431
432    #[test]
433    fn test_pagination_note() {
434        let mut schema = CompiledSchema {
435            types: vec![],
436            enums: vec![],
437            input_types: vec![],
438            interfaces: vec![],
439            unions: vec![],
440            queries: vec![QueryDefinition {
441                name:                "products".to_string(),
442                return_type:         "Product".to_string(),
443                returns_list:        true,
444                nullable:            false,
445                arguments:           vec![],
446                sql_source:          Some("products".to_string()),
447                description:         None,
448                auto_params:         AutoParams {
449                    has_where:    false,
450                    has_order_by: false,
451                    has_limit:    true,
452                    has_offset:   true,
453                },
454                deprecation:         None,
455                jsonb_column:        "data".to_string(),
456                relay:               false,
457                relay_cursor_column: None,
458                relay_cursor_type:   CursorType::default(),
459                inject_params:       IndexMap::default(),
460                cache_ttl_seconds:   None,
461                additional_views:    vec![],
462                requires_role:       None,
463                rest_path:           None,
464                rest_method:         None,
465                native_columns:      HashMap::new(),
466            }],
467            mutations: vec![],
468            subscriptions: vec![],
469            directives: vec![],
470            observers: Vec::new(),
471            fact_tables: HashMap::default(),
472            federation: None,
473            security: None,
474            observers_config: None,
475            subscriptions_config: None,
476            validation_config: None,
477            debug_config: None,
478            mcp_config: None,
479            schema_sdl: None,
480            schema_format_version: None,
481            custom_scalars: CustomTypeRegistry::default(),
482            ..Default::default()
483        };
484
485        let report = SchemaOptimizer::optimize(&mut schema).unwrap();
486        assert!(report.optimization_notes.iter().any(|note| note.contains("pagination")));
487    }
488
489    #[test]
490    fn test_large_type_warning() {
491        let mut schema = CompiledSchema {
492            types: vec![TypeDefinition {
493                name:                "BigType".into(),
494                sql_source:          String::new().into(),
495                jsonb_column:        String::new(),
496                fields:              (0..25)
497                    .map(|i| FieldDefinition {
498                        name:           format!("field{i}").into(),
499                        field_type:     FieldType::String,
500                        nullable:       false,
501                        default_value:  None,
502                        description:    None,
503                        vector_config:  None,
504                        alias:          None,
505                        deprecation:    None,
506                        requires_scope: None,
507                        on_deny:        FieldDenyPolicy::default(),
508                        encryption:     None,
509                    })
510                    .collect(),
511                description:         None,
512                sql_projection_hint: None,
513                implements:          vec![],
514                requires_role:       None,
515                is_error:            false,
516                relay:               false,
517                relationships:       Vec::new(),
518            }],
519            enums: vec![],
520            input_types: vec![],
521            interfaces: vec![],
522            unions: vec![],
523            queries: vec![],
524            mutations: vec![],
525            subscriptions: vec![],
526            directives: vec![],
527            observers: Vec::new(),
528            fact_tables: HashMap::default(),
529            federation: None,
530            security: None,
531            observers_config: None,
532            subscriptions_config: None,
533            validation_config: None,
534            debug_config: None,
535            mcp_config: None,
536            schema_sdl: None,
537            schema_format_version: None,
538            custom_scalars: CustomTypeRegistry::default(),
539            ..Default::default()
540        };
541
542        let report = SchemaOptimizer::optimize(&mut schema).unwrap();
543        assert!(report.optimization_notes.iter().any(|note| note.contains("25 fields")));
544    }
545
546    #[test]
547    fn test_projection_hint_for_large_type() {
548        let mut schema = CompiledSchema {
549            types: vec![TypeDefinition {
550                name:                "User".into(),
551                sql_source:          "users".into(),
552                jsonb_column:        "data".to_string(),
553                fields:              (0..15)
554                    .map(|i| FieldDefinition {
555                        name:           format!("field{i}").into(),
556                        field_type:     FieldType::String,
557                        nullable:       false,
558                        default_value:  None,
559                        description:    None,
560                        vector_config:  None,
561                        alias:          None,
562                        deprecation:    None,
563                        requires_scope: None,
564                        on_deny:        FieldDenyPolicy::default(),
565                        encryption:     None,
566                    })
567                    .collect(),
568                description:         None,
569                sql_projection_hint: None,
570                implements:          vec![],
571                requires_role:       None,
572                is_error:            false,
573                relay:               false,
574                relationships:       Vec::new(),
575            }],
576            enums: vec![],
577            input_types: vec![],
578            interfaces: vec![],
579            unions: vec![],
580            queries: vec![],
581            mutations: vec![],
582            subscriptions: vec![],
583            directives: vec![],
584            observers: Vec::new(),
585            fact_tables: HashMap::default(),
586            federation: None,
587            security: None,
588            observers_config: None,
589            subscriptions_config: None,
590            validation_config: None,
591            debug_config: None,
592            mcp_config: None,
593            schema_sdl: None,
594            schema_format_version: None,
595            custom_scalars: CustomTypeRegistry::default(),
596            ..Default::default()
597        };
598
599        let report = SchemaOptimizer::optimize(&mut schema).unwrap();
600
601        // Type with 15 fields and JSONB column should get projection hint
602        assert!(!report.projection_hints.is_empty());
603        assert_eq!(report.projection_hints[0].type_name, "User");
604        assert_eq!(report.projection_hints[0].field_count, 15);
605
606        // Type should have sql_projection_hint set
607        assert!(schema.types[0].has_sql_projection());
608        let hint = schema.types[0].sql_projection_hint.as_ref().unwrap();
609        assert_eq!(hint.database, DatabaseType::PostgreSQL);
610        assert!(hint.estimated_reduction_percent > 0);
611    }
612
613    #[test]
614    fn test_projection_not_applied_without_jsonb() {
615        let mut schema = CompiledSchema {
616            types: vec![TypeDefinition {
617                name:                "SmallType".into(),
618                sql_source:          "small_table".into(),
619                jsonb_column:        String::new(), // No JSONB column
620                fields:              (0..15)
621                    .map(|i| FieldDefinition {
622                        name:           format!("field{i}").into(),
623                        field_type:     FieldType::String,
624                        nullable:       false,
625                        default_value:  None,
626                        description:    None,
627                        vector_config:  None,
628                        alias:          None,
629                        deprecation:    None,
630                        requires_scope: None,
631                        on_deny:        FieldDenyPolicy::default(),
632                        encryption:     None,
633                    })
634                    .collect(),
635                description:         None,
636                sql_projection_hint: None,
637                implements:          vec![],
638                requires_role:       None,
639                is_error:            false,
640                relay:               false,
641                relationships:       Vec::new(),
642            }],
643            enums: vec![],
644            input_types: vec![],
645            interfaces: vec![],
646            unions: vec![],
647            queries: vec![],
648            mutations: vec![],
649            subscriptions: vec![],
650            directives: vec![],
651            observers: Vec::new(),
652            fact_tables: HashMap::default(),
653            federation: None,
654            security: None,
655            observers_config: None,
656            subscriptions_config: None,
657            validation_config: None,
658            debug_config: None,
659            mcp_config: None,
660            schema_sdl: None,
661            schema_format_version: None,
662            custom_scalars: CustomTypeRegistry::default(),
663            ..Default::default()
664        };
665
666        let report = SchemaOptimizer::optimize(&mut schema).unwrap();
667
668        // Type without JSONB column should not get projection hint
669        assert!(report.projection_hints.is_empty());
670        assert!(!schema.types[0].has_sql_projection());
671    }
672}
fraiseql_cli/schema/optimizer.rs

fraiseql_cli/schema/
optimizer.rs
