Skip to content

Instantly share code, notes, and snippets.

@msakrejda
Created October 18, 2012 19:13
Show Gist options
  • Save msakrejda/3914174 to your computer and use it in GitHub Desktop.
Save msakrejda/3914174 to your computer and use it in GitHub Desktop.
Warn when actual rows are significantly different than estimated rows in an EXPLAIN ANALYZE in postgres
postgres=# create table t(a integer); create index t_idx on t(a);
CREATE TABLE
CREATE INDEX
postgres=# insert into t select g from generate_series(1,10) g;
INSERT 0 10
postgres=# explain analyze select * from t where a > 8;
QUERY PLAN
-------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..1.12 rows=2 width=4) (actual time=0.017..0.019 rows=2 loops=1)
Filter: (a > 8)
Rows Removed by Filter: 8
Total runtime: 0.055 ms
(4 rows)
postgres=# insert into t select g from generate_series(8, 80000) g; explain analyze select * from t where a > 8;
INSERT 0 79993
WARNING: Actual row counts differ significantly from estimated row counts
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Only Scan using t_idx on t (cost=0.00..296.11 rows=787 width=4) (actual time=0.020..17.971 rows=79994 loops=1)
Index Cond: (a > 8)
Heap Fetches: 79994
Total runtime: 20.878 ms
(4 rows)
postgres=# analyze t;
ANALYZE
postgres=# explain analyze select * from t where a > 8;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..1354.04 rows=79996 width=4) (actual time=0.038..18.047 rows=79994 loops=1)
Filter: (a > 8)
Rows Removed by Filter: 9
Total runtime: 23.147 ms
(4 rows)
postgres=#
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment