Skip to content

Instantly share code, notes, and snippets.

@svartalf
Created June 6, 2012 08:26
Show Gist options
  • Save svartalf/2880645 to your computer and use it in GitHub Desktop.
Save svartalf/2880645 to your computer and use it in GitHub Desktop.
PostgreSQL benchmark for database index on the Django' `auth_user.is_staff` field
database=> SELECT COUNT(*) FROM auth_user;
count
-------
1076
(1 row)
database=> EXPLAIN SELECT COUNT(*) FROM auth_user WHERE is_staff = False;
QUERY PLAN
-------------------------------------------------------------------
Aggregate (cost=26.80..26.81 rows=1 width=0)
-> Seq Scan on auth_user (cost=0.00..24.04 rows=1103 width=0)
Filter: (NOT is_staff)
(3 rows)
database=> EXPLAIN SELECT COUNT(*) FROM auth_user WHERE is_staff = True;
QUERY PLAN
----------------------------------------------------------------
Aggregate (cost=24.04..24.05 rows=1 width=0)
-> Seq Scan on auth_user (cost=0.00..24.04 rows=1 width=0)
Filter: is_staff
(3 rows)
database=> CREATE INDEX auth_user_is_staff ON auth_user (is_staff);
CREATE INDEX
database=> EXPLAIN SELECT COUNT(*) FROM auth_user WHERE is_staff = False;
QUERY PLAN
-------------------------------------------------------------------
Aggregate (cost=26.45..26.46 rows=1 width=0)
-> Seq Scan on auth_user (cost=0.00..23.76 rows=1075 width=0)
Filter: (NOT is_staff)
(3 rows)
database=> EXPLAIN SELECT COUNT(*) FROM auth_user WHERE is_staff = True;
QUERY PLAN
------------------------------------------------------------------------------------------
Aggregate (cost=8.27..8.28 rows=1 width=0)
-> Index Scan using auth_user_is_staff on auth_user (cost=0.00..8.27 rows=1 width=0)
Index Cond: (is_staff = true)
Filter: is_staff
(4 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment