SQLite Query Optimization: Using Uncorrelated Subqueries to Skip Correlated Subqueries for Performance Gains
By
emschwartz
Slow-proofed and worth the wait. Worth its weight in flour.
Summary
The article describes a SQLite query optimization technique where an uncorrelated scalar subquery is used to skip expensive correlated subqueries, improving query performance by approximately 17%. The author shares this optimization discovered while working on domain exclusion lists and paywalled content filtering for their content aggregation tool called Scour, which processes 1-3 million pieces of content. The post explains technical SQL concepts and provides practical implementation details for database performance optimization.
Key quotes
· 5 pulledusing an uncorrelated scalar subquery to skip a correlated subquery
Skipping expensive per-row subqueries to speed up my average query ~17%
Scour searches noisy sources for content related to users' interests
I recently added domain exclusion lists and paywalled content filtering to Scour
This blog post describes a small but useful SQL(ite) query optimization
You might also wanna read
PostgreSQL Double Buffering: Why OS RAM and Buffer Cache Compete and How the 25% Rule Helps
This article explains the problem of double buffering in PostgreSQL, where the OS filesystem cache and PostgreSQL's shared_buffers both cach
Optimizing Top K Query Performance in PostgreSQL: Challenges and Solutions
This technical article examines the challenges of optimizing Top K queries in PostgreSQL databases, where 'Top K' refers to retrieving the K
paradedb.com·2mo ago
Optimizing Geospatial SQL Queries with H3 Indexes for 400× Performance Gains
The article explains how to dramatically speed up geospatial SQL queries (geo joins) using H3 indexes. It describes the performance problems
floedb.ai·3mo ago
Performance Optimization: Replacing Protobuf with Direct C-to-Rust Bindings in PgDog PostgreSQL Proxy
The article details how PgDog, a PostgreSQL proxy written in Rust, replaced Protobuf serialization with direct C-to-Rust bindings to achieve
pgdog.dev·4mo ago
Advanced PostgreSQL Optimization Techniques Beyond Conventional Methods
This article presents unconventional optimization techniques for PostgreSQL databases, moving beyond standard approaches like query rewritin
hakibenita.com·4mo ago
SlopCollector: Database Optimization Tool for Supabase Performance
SlopCollector is a database optimization tool for Supabase that helps developers identify and fix database performance issues. The tool anal
Product Hunt·6mo ago