SQLSQLite: full scan, index search, covering index and rowid lookup on one table
PostgreSQL: the plan switches as more rows match, and correlation moves the line
-- 100000 generated orders, created in date order
CREATE TABLE big_orders WITH (autovacuum_enabled = false) AS
SELECT g AS id,
g % 5000 AS customer_id,
(g * 7919) % 100000 AS score, -- scattered over the table
DATE '2025-01-01' + (g / 1000) AS created_at, -- follows physical order
repeat('x', 80) AS note
FROM generate_series(1, 100000) AS g;
ALTER TABLE big_orders ADD PRIMARY KEY (id);
CREATE INDEX big_orders_score_idx ON big_orders (score);
CREATE INDEX big_orders_created_idx ON big_orders (created_at);
-- sample every row so the statistics (and so the plans) are the same on every run
ALTER TABLE big_orders ALTER COLUMN score SET STATISTICS 1000;
VACUUM ANALYZE big_orders;
-- returns the first scan node of a plan
CREATE FUNCTION scan_of(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
FOR r IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP
IF r."QUERY PLAN" LIKE '%Scan%' THEN
RETURN regexp_replace(ltrim(r."QUERY PLAN", ' ->'), ' on big_orders$', '');
END IF;
END LOOP;
END $$;
SELECT column_filtered, rows_matched, plan FROM (
SELECT 'score (scattered)' AS column_filtered, pct, pct || '%' AS rows_matched,
scan_of('SELECT * FROM big_orders WHERE score < ' || (pct * 1000)::int) AS plan
FROM unnest(ARRAY[0.001, 0.01, 1, 10, 30, 40]) AS pct
UNION ALL
SELECT 'created_at (in order)', pct, pct || '%',
scan_of('SELECT * FROM big_orders WHERE created_at < DATE ''2025-01-01'' + ' || pct)
FROM unnest(ARRAY[1, 10, 50, 70, 80]) AS pct
) AS s
ORDER BY column_filtered DESC, pct;
PostgreSQL: with random_page_cost = 1.1 (SSD-style) the index wins for more rows
-- 100000 generated orders, created in date order
CREATE TABLE big_orders WITH (autovacuum_enabled = false) AS
SELECT g AS id,
g % 5000 AS customer_id,
(g * 7919) % 100000 AS score, -- scattered over the table
DATE '2025-01-01' + (g / 1000) AS created_at, -- follows physical order
repeat('x', 80) AS note
FROM generate_series(1, 100000) AS g;
ALTER TABLE big_orders ADD PRIMARY KEY (id);
CREATE INDEX big_orders_score_idx ON big_orders (score);
CREATE INDEX big_orders_created_idx ON big_orders (created_at);
-- sample every row so the statistics (and so the plans) are the same on every run
ALTER TABLE big_orders ALTER COLUMN score SET STATISTICS 1000;
VACUUM ANALYZE big_orders;
-- returns the first scan node of a plan
CREATE FUNCTION scan_of(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
FOR r IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP
IF r."QUERY PLAN" LIKE '%Scan%' THEN
RETURN regexp_replace(ltrim(r."QUERY PLAN", ' ->'), ' on big_orders$', '');
END IF;
END LOOP;
END $$;
SET random_page_cost = 1.1;
SELECT pct || '%' AS rows_matched,
scan_of('SELECT * FROM big_orders WHERE score < ' || (pct * 1000)::int) AS plan_at_1_1
FROM unnest(ARRAY[0.001, 0.01, 0.1, 1, 10, 30, 40, 60]) AS pct
ORDER BY pct;
PostgreSQL: Index Only Scans, ORDER BY LIMIT, and queries that cannot use the index
-- 100000 generated orders, created in date order
CREATE TABLE big_orders WITH (autovacuum_enabled = false) AS
SELECT g AS id,
g % 5000 AS customer_id,
(g * 7919) % 100000 AS score, -- scattered over the table
DATE '2025-01-01' + (g / 1000) AS created_at, -- follows physical order
repeat('x', 80) AS note
FROM generate_series(1, 100000) AS g;
ALTER TABLE big_orders ADD PRIMARY KEY (id);
CREATE INDEX big_orders_score_idx ON big_orders (score);
CREATE INDEX big_orders_created_idx ON big_orders (created_at);
-- sample every row so the statistics (and so the plans) are the same on every run
ALTER TABLE big_orders ALTER COLUMN score SET STATISTICS 1000;
VACUUM ANALYZE big_orders;
-- returns the first scan node of a plan
CREATE FUNCTION scan_of(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
FOR r IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP
IF r."QUERY PLAN" LIKE '%Scan%' THEN
RETURN regexp_replace(ltrim(r."QUERY PLAN", ' ->'), ' on big_orders$', '');
END IF;
END LOOP;
END $$;
SELECT label, scan_of(q) AS plan FROM (VALUES
(1, 'SELECT score ... score < 90000 (90%)', 'SELECT score FROM big_orders WHERE score < 90000'),
(2, 'SELECT * ... ORDER BY score LIMIT 10', 'SELECT * FROM big_orders ORDER BY score LIMIT 10'),
(3, 'SELECT * ... WHERE score + 0 = 7', 'SELECT * FROM big_orders WHERE score + 0 = 7'),
(4, 'SELECT * ... WHERE id = 42', 'SELECT * FROM big_orders WHERE id = 42'),
(5, 'SELECT count(*) FROM big_orders', 'SELECT count(*) FROM big_orders'),
(6, 'SELECT * ... WHERE note = ''y''', 'SELECT * FROM big_orders WHERE note = ''y''')
) AS v(n, label, q)
ORDER BY n;