Indexes Invisible
Make Indexes Invisible
Postgres allows for transactional DDL, which allows you to compare a query with and without an index very easily:
We see that this SELECT
uses an index:
db= EXPLAIN
db- SELECT *
db- FROM sale_fact
db- WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
QUERY PLAN
--------------------
Index Scan using sale_fact_sold_at_ix on sale_fact (cost=0.42..182.80 rows=4319 width=41)
Index Cond: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
Let’s drop the index inside of a transaction and see the query plan aftewards:
db= BEGIN;
BEGIN
db= DROP INDEX sale_fact_sold_at_ix;
DROP INDEX
db= EXPLAIN
db- SELECT *
db- FROM sale_fact
db- WHERE sold_at BETWEEN '2020-07-01' AND '2020-07-31';
QUERY PLAN
---------
Seq Scan on sale_fact (cost=0.00..2435.00 rows=4319 width=41)
Filter: ((sold_at >= '2020-07-01'::date) AND (sold_at <= '2020-07-31'::date))
db= ROLLBACK;
ROLLBACK
CAUTION: This will lock out concurent selects, inserts, updates, and deletes while the transcation is active:
Be careful using this in a testing environment, and be extremely careful using this in a production database.