I have a table:
debts (
name text,
inv_no integer,
inv_type text,
status text,
);
I have a following select:
SELECT COUNT(*) FROM debts WHERE name = '...' AND inv_no = 100 AND inv_type = '...';
To optimize something else, I added:
CREATE INDEX ON debt (status);
Nowhere in SELECT do I refer to the status, yet running...
EXPLAIN SELECT COUNT(*)... (as above)
...before and after creating index gives me cost 16.65..16.66 changing into 1.25..1.26. Why?
Full explain (analyze, verbose)
before/after:
Before:
QUERY PLAN
----------
Aggregate (cost=16.65..16.66 rows=1 width=0) (actual time=0.126..0.128 rows=1 loops=1)
Output: count(*)
-> Seq Scan on ab123456.debts (cost=0.00..16.65 rows=1 width=0) (actual time=0.106..0.106 rows=0 loops=1)
Output: name, inv_no, inv_type, status
Filter: ((debts.name = '...'::text) AND (debts.inv_type = '...'::text) AND (debts.inv_no = 100))
Total runtime: 0.387 ms
After:
QUERY PLAN
----------
Aggregate (cost=1.25..1.26 rows=1 width=0) (actual time=0.031..0.033 rows=1 loops=1)
Output: count(*)
-> Seq Scan on ab123456.debts (cost=0.00..1.25 rows=1 width=0) (actual time=0.024..0.024 rows=0 loops=1)
Output: name, inv_no, inv_type, status
Filter: ((debts.name = '...'::text) AND (debts.inv_type = '...'::text) AND (debts.inv_no = 100))
Total runtime: 0.118 ms
Some utility statements (including CREATE INDEX
!) update table statistics while being at it. The manual:
For efficiency reasons,
reltuples
andrelpages
are not updated on-the-fly, and so they usually contain somewhat out-of-date values. They are updated byVACUUM
,ANALYZE
, and a few DDL commands such asCREATE INDEX
.
Bold emphasis mine. So even if your index seems completely unrelated, the updated table statistics can have an impact - especially on count()
which primarily depends on the two statistics mentioned.
Related: