That example also reports that it uses the index. Only the "is true" variation insists on seq. scan.
On Thu, Jun 26, 2008 at 4:08 PM, Lennin Caro <[EMAIL PROTECTED]> wrote: > use this > > explain analyze select * from result where active = 't'; > > --- On *Thu, 6/26/08, Phillip Mills <[EMAIL PROTECTED]>* wrote: > > From: Phillip Mills <[EMAIL PROTECTED]> > Subject: [GENERAL] Partial Index Too Literal? > To: pgsql-general@postgresql.org > Date: Thursday, June 26, 2008, 7:24 PM > > > Under somewhat unusual circumstances, rows in one of our tables have an > 'active' flag with a true value. We check for these relatively often since > they represent cases that need special handling. We've found through > testing that having a partial index on that field works well. What seems > odd to me, however, is that the index gets used only if the query is a > textual match for how the index was specified. > > That is, using an index defined as 'where active = true': > dev=# explain analyze select * from result where active = true; > QUERY PLAN > > ------------------------------------------------------------------------------------------------------------------------------------- > Bitmap Heap Scan on result (cost=5.31..472.34 rows=4206 width=1126) > (actual time=7.868..7.868 rows=0 loops=1) > Filter: active > -> Bitmap Index Scan on result_active_idx (cost=0.00..4.26 rows=2103 > width=0) (actual time=4.138..4.138 rows=16625 loops=1) > Index Cond: (active = true) > Total runtime: 7.918 ms > (5 rows) > > dev=# explain analyze select * from result where active is true; > QUERY PLAN > > -------------------------------------------------------------------------------------------------------------- > Seq Scan on result (cost=0.00..537.26 rows=4263 width=1126) (actual > time=55.631..55.631 rows=0 loops=1) > Filter: (active IS TRUE) > Total runtime: 55.668 ms > (3 rows) > > This is version 8.2.6. Is there something I'm missing that could make > these queries ever produce different results? > > >
