Created
October 18, 2012 19:13
-
-
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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