clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335681 fiddles created (27446 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
10064 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
9999 5
lots few
 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=0 loops=1)
Index Cond: ((lots = 2345) AND (few = 2))
Heap Fetches: 0
Planning Time: 0.266 ms
Execution Time: 0.077 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=0 loops=1)
Index Cond: ((lots = 2345) AND (few = 2))
Heap Fetches: 0
Planning Time: 0.073 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=0 loops=1)
Index Cond: ((few = 2) AND (lots = 2345))
Heap Fetches: 0
Planning Time: 0.187 ms
Execution Time: 0.047 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=0 loops=1)
Index Cond: ((few = 2) AND (lots = 2345))
Heap Fetches: 0
Planning Time: 0.041 ms
Execution Time: 0.016 ms
 hidden batch(es)