gistfile1.txt

de2nq092qkqfxll=> create index ben_test_partial on units (city_name) where is_deleted = true;
CREATE INDEX
de2nq092qkqfxll=> explain select count(*) from units where city_name = 'San Francisco' and is_deleted = true;
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Aggregate  (cost=1276.17..1276.17 rows=1 width=0)
   ->  Bitmap Heap Scan on units  (cost=35.38..1276.00 rows=331 width=0)
         Recheck Cond: (((city_name)::text = 'San Francisco'::text) AND is_deleted)
         ->  Bitmap Index Scan on ben_test_partial  (cost=0.00..35.37 rows=331 width=0)
               Index Cond: ((city_name)::text = 'San Francisco'::text)
(5 rows)


de2nq092qkqfxll=> drop index ben_test_partial;
DROP INDEX
de2nq092qkqfxll=> create index ben_test_partial_2 on units (city_name, is_deleted);
CREATE INDEX
de2nq092qkqfxll=> explain select count(*) from units where city_name = 'San Francisco' and is_deleted = true;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Aggregate  (cost=1249.61..1249.61 rows=1 width=0)
   ->  Bitmap Heap Scan on units  (cost=8.82..1249.44 rows=331 width=0)
         Recheck Cond: ((city_name)::text = 'San Francisco'::text)
         Filter: is_deleted
         ->  Bitmap Index Scan on ben_test_partial_2  (cost=0.00..8.80 rows=331 width=0)
               Index Cond: (((city_name)::text = 'San Francisco'::text) AND (is_deleted = true))



USING EXPLAIN ANALYZE


de2nq092qkqfxll=> explain analyze select count(*) from units where city_name = 'San Francisco' and is_deleted = true;
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1249.61..1249.61 rows=1 width=0) (actual time=1.964..1.965 rows=1 loops=1)
   ->  Bitmap Heap Scan on units  (cost=8.82..1249.44 rows=331 width=0) (actual time=0.321..1.410 rows=847 loops=1)
         Recheck Cond: ((city_name)::text = 'San Francisco'::text)
         Filter: is_deleted
         ->  Bitmap Index Scan on ben_test_partial_2  (cost=0.00..8.80 rows=331 width=0) (actual time=0.291..0.291 rows=847 loops=1)
               Index Cond: (((city_name)::text = 'San Francisco'::text) AND (is_deleted = true))
 Total runtime: 2.037 ms
(7 rows)

de2nq092qkqfxll=> 
de2nq092qkqfxll=> 
de2nq092qkqfxll=> drop index ben_test_partial_2;
DROP INDEX
de2nq092qkqfxll=> create index ben_test_partial on (city_name) where is_deleted = true;
ERROR:  syntax error at or near "("
LINE 1: create index ben_test_partial on (city_name) where is_delete...
                                         ^
de2nq092qkqfxll=> create index ben_test_partial on units (city_name) where is_deleted = true;
CREATE INDEX
de2nq092qkqfxll=> 
de2nq092qkqfxll=> 
de2nq092qkqfxll=> explain analyze select count(*) from units where city_name = 'San Francisco' and is_deleted = true;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1276.17..1276.17 rows=1 width=0) (actual time=1.592..1.593 rows=1 loops=1)
   ->  Bitmap Heap Scan on units  (cost=35.38..1276.00 rows=331 width=0) (actual time=0.215..1.039 rows=847 loops=1)
         Recheck Cond: (((city_name)::text = 'San Francisco'::text) AND is_deleted)
         ->  Bitmap Index Scan on ben_test_partial  (cost=0.00..35.37 rows=331 width=0) (actual time=0.190..0.190 rows=847 loops=1)
               Index Cond: ((city_name)::text = 'San Francisco'::text)
 Total runtime: 1.661 ms
(6 rows)