SkyArtShop/backend/query-optimization-analysis.sql

-- =====================================================
-- QUERY OPTIMIZATION ANALYSIS FOR SKYARTSHOP
-- Date: January 3, 2026
-- Purpose: Analyze and optimize slow queries
-- =====================================================

-- =====================================================
-- PART 1: QUERY PERFORMANCE ANALYSIS
-- =====================================================

-- Show slow queries (if pg_stat_statements is enabled)
-- SELECT 
--     substring(query, 1, 100) AS short_query,
--     round(total_exec_time::numeric, 2) AS total_time,
--     calls,
--     round(mean_exec_time::numeric, 2) AS avg_time,
--     round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percentage
-- FROM pg_stat_statements
-- WHERE query NOT LIKE '%pg_stat%'
-- ORDER BY total_exec_time DESC
-- LIMIT 20;

-- =====================================================
-- PART 2: TABLE SIZE AND BLOAT ANALYSIS
-- =====================================================

-- Show table sizes
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS indexes_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- =====================================================
-- PART 3: INDEX USAGE STATISTICS
-- =====================================================

-- Show unused indexes (candidates for removal)
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan as times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
    AND idx_scan = 0
    AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Show most used indexes
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan as times_used,
    idx_tup_read as rows_read,
    idx_tup_fetch as rows_fetched,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC
LIMIT 20;

-- =====================================================
-- PART 4: SEQUENTIAL SCAN ANALYSIS
-- =====================================================

-- Tables with high sequential scan rates (may need indexes)
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / NULLIF(seq_scan, 0) AS avg_seq_rows,
    n_live_tup as live_rows,
    CASE 
        WHEN seq_scan > 0 THEN 
            round((100.0 * seq_scan / NULLIF(seq_scan + idx_scan, 0))::numeric, 2)
        ELSE 0
    END AS seq_scan_percentage
FROM pg_stat_user_tables
WHERE schemaname = 'public'
    AND seq_scan > 0
ORDER BY seq_scan DESC;

-- =====================================================
-- PART 5: MISSING INDEX SUGGESTIONS
-- =====================================================

-- Queries that might benefit from indexes
-- Based on common query patterns in the application

-- Suggestion 1: Composite index for product listing with filters
COMMENT ON INDEX idx_products_composite IS 'Optimizes: SELECT * FROM products WHERE isactive = true AND isfeatured = true ORDER BY createdat DESC';

-- Suggestion 2: Index for product images by color
COMMENT ON INDEX idx_product_images_color IS 'Optimizes: SELECT * FROM product_images WHERE color_variant = ?';

-- Suggestion 3: Index for blog post slug lookup
COMMENT ON INDEX idx_blogposts_slug IS 'Optimizes: SELECT * FROM blogposts WHERE slug = ? AND ispublished = true';

-- =====================================================
-- PART 6: QUERY REWRITE SUGGESTIONS
-- =====================================================

-- ORIGINAL: Get products with images (inefficient)
-- SELECT p.*, pi.* FROM products p 
-- LEFT JOIN product_images pi ON pi.product_id = p.id 
-- WHERE p.isactive = true;

-- OPTIMIZED: Use JSON aggregation to reduce rows
-- SELECT p.*, 
--   COALESCE(json_agg(pi.*) FILTER (WHERE pi.id IS NOT NULL), '[]') as images
-- FROM products p
-- LEFT JOIN product_images pi ON pi.product_id = p.id
-- WHERE p.isactive = true
-- GROUP BY p.id;

-- =====================================================
-- PART 7: MATERIALIZED VIEW FOR EXPENSIVE QUERIES
-- =====================================================

-- Create materialized view for product catalog (if needed for very high traffic)
-- DROP MATERIALIZED VIEW IF EXISTS mv_product_catalog;
-- CREATE MATERIALIZED VIEW mv_product_catalog AS
-- SELECT 
--     p.id, p.name, p.slug, p.shortdescription, p.price, 
--     p.category, p.stockquantity, p.isfeatured, p.isbestseller,
--     json_agg(
--         json_build_object(
--             'id', pi.id,
--             'image_url', pi.image_url,
--             'color_variant', pi.color_variant,
--             'is_primary', pi.is_primary
--         ) ORDER BY pi.display_order
--     ) FILTER (WHERE pi.id IS NOT NULL) as images
-- FROM products p
-- LEFT JOIN product_images pi ON pi.product_id = p.id
-- WHERE p.isactive = true
-- GROUP BY p.id;
-- 
-- CREATE INDEX ON mv_product_catalog(id);
-- CREATE INDEX ON mv_product_catalog(slug);
-- CREATE INDEX ON mv_product_catalog(category);
-- CREATE INDEX ON mv_product_catalog(isfeatured) WHERE isfeatured = true;

-- Refresh command (run after product updates):
-- REFRESH MATERIALIZED VIEW CONCURRENTLY mv_product_catalog;

-- =====================================================
-- PART 8: VACUUM AND ANALYZE
-- =====================================================

-- Full vacuum to reclaim space and update stats
VACUUM ANALYZE products;
VACUUM ANALYZE product_images;
VACUUM ANALYZE blogposts;
VACUUM ANALYZE portfolioprojects;
VACUUM ANALYZE pages;
VACUUM ANALYZE uploads;
VACUUM ANALYZE media_folders;

-- =====================================================
-- PART 9: CONNECTION POOL OPTIMIZATION
-- =====================================================

-- Show current database connections
SELECT 
    datname,
    count(*) as connections,
    max(backend_start) as latest_connection
FROM pg_stat_activity
WHERE datname = 'skyartshop'
GROUP BY datname;

-- Show connection limits
SELECT 
    name, 
    setting, 
    unit 
FROM pg_settings 
WHERE name IN ('max_connections', 'superuser_reserved_connections');

-- =====================================================
-- PART 10: CACHE HIT RATIO
-- =====================================================

-- Check cache hit ratio (should be > 99%)
SELECT 
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    CASE 
        WHEN sum(heap_blks_hit) + sum(heap_blks_read) > 0 THEN
            round(100.0 * sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)), 2)
        ELSE 0
    END as cache_hit_ratio
FROM pg_statio_user_tables;

-- =====================================================
-- PART 11: SPECIFIC QUERY OPTIMIZATIONS
-- =====================================================

-- Optimized query for product listing page
EXPLAIN ANALYZE
SELECT p.id, p.name, p.slug, p.price, p.stockquantity, p.category,
    COALESCE(
        json_agg(
            json_build_object(
                'id', pi.id,
                'image_url', pi.image_url,
                'is_primary', pi.is_primary
            ) ORDER BY pi.display_order
        ) FILTER (WHERE pi.id IS NOT NULL),
        '[]'::json
    ) as images
FROM products p
LEFT JOIN product_images pi ON pi.product_id = p.id AND pi.is_primary = true
WHERE p.isactive = true
GROUP BY p.id
ORDER BY p.createdat DESC
LIMIT 50;

-- Optimized query for single product detail
EXPLAIN ANALYZE
SELECT p.*,
    json_agg(
        json_build_object(
            'id', pi.id,
            'image_url', pi.image_url,
            'color_variant', pi.color_variant,
            'color_code', pi.color_code,
            'alt_text', pi.alt_text,
            'display_order', pi.display_order,
            'is_primary', pi.is_primary,
            'variant_price', pi.variant_price,
            'variant_stock', pi.variant_stock
        ) ORDER BY pi.display_order
    ) FILTER (WHERE pi.id IS NOT NULL) as images
FROM products p
LEFT JOIN product_images pi ON pi.product_id = p.id
WHERE p.slug = 'example-product' AND p.isactive = true
GROUP BY p.id;

-- =====================================================
-- PART 12: PARTITIONING RECOMMENDATIONS (for scale)
-- =====================================================

-- If you have millions of products or images, consider partitioning
-- Example: Partition products by category or date

-- CREATE TABLE products_paintings PARTITION OF products 
-- FOR VALUES IN ('Paintings', 'Oil Paintings', 'Watercolor');
-- 
-- CREATE TABLE products_sculptures PARTITION OF products 
-- FOR VALUES IN ('Sculptures', '3D Art');

-- =====================================================
-- RECOMMENDATIONS SUMMARY
-- =====================================================

-- 1. Ensure all indexes from Part 2 of database-analysis-fixes.sql are created
-- 2. Monitor slow queries using pg_stat_statements
-- 3. Set up regular VACUUM ANALYZE jobs (daily or weekly)
-- 4. Keep cache hit ratio above 99%
-- 5. Limit connection pool size to 20-50 connections
-- 6. Use prepared statements for frequently executed queries
-- 7. Implement application-level caching (Redis) for hot data
-- 8. Consider read replicas for scaling reads
-- 9. Use JSONB for flexible schema parts (settings, metadata)
-- 10. Monitor table bloat and run VACUUM FULL if needed

-- =====================================================
-- END OF QUERY OPTIMIZATION ANALYSIS
-- =====================================================