clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798208 fiddles created (41114 in the last week).

CREATE TEMP TABLE tbl AS SELECT (random() * 10000)::int AS lots , (random() * 4)::int AS few FROM generate_series (1, 100000); -- 100k rows DELETE FROM tbl WHERE random() > 0.9; -- create some dead tuples, more "real-life"
100000 rows affected
9753 rows affected
 hidden batch(es)


VACUUM ANALYZE tbl;
 hidden batch(es)


SELECT count(distinct lots) -- ~ 10000 , count(distinct few) -- 5 FROM tbl; SELECT * FROM tbl WHERE lots = 2345 AND few = 2;
count count
10001 5
lots few
2345 2
2345 2
2345 2
 hidden batch(es)


CREATE INDEX tbl_lf_idx ON tbl(lots, few);
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM tbl WHERE lots = 2345 AND few = 2;
QUERY PLAN
Index Only Scan using tbl_lf_idx on tbl (cost=0.29..4.33 rows=2 width=8) (actual rows=3 loops=1)
Index Cond: ((lots = 2345) AND (few = 2))
Heap Fetches: 0
Planning Time: 0.293 ms
Execution Time: 0.104 ms
 hidden batch(es)


-- repeat EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM tbl WHERE lots = 2345 AND few = 2;
QUERY PLAN
Index Only Scan using tbl_lf_idx on tbl (cost=0.29..4.33 rows=2 width=8) (actual rows=3 loops=1)
Index Cond: ((lots = 2345) AND (few = 2))
Heap Fetches: 0
Planning Time: 0.056 ms
Execution Time: 0.023 ms
 hidden batch(es)


DROP INDEX tbl_lf_idx; CREATE INDEX tbl_fl_idx ON tbl(few, lots);
 hidden batch(es)


EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM tbl WHERE lots = 2345 AND few = 2;
QUERY PLAN
Index Only Scan using tbl_fl_idx on tbl (cost=0.29..4.33 rows=2 width=8) (actual rows=3 loops=1)
Index Cond: ((few = 2) AND (lots = 2345))
Heap Fetches: 0
Planning Time: 0.246 ms
Execution Time: 0.114 ms
 hidden batch(es)


-- repeat EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM tbl WHERE lots = 2345 AND few = 2;
QUERY PLAN
Index Only Scan using tbl_fl_idx on tbl (cost=0.29..4.33 rows=2 width=8) (actual rows=3 loops=1)
Index Cond: ((few = 2) AND (lots = 2345))
Heap Fetches: 0
Planning Time: 0.056 ms
Execution Time: 0.024 ms
 hidden batch(es)