Re: Partial Index Too Literal?

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 <lennin(dot)caro(at)yahoo(dot)com> wrote:

> use this
>
> explain analyze select * from result where active = 't';
>
> --- On *Thu, 6/26/08, Phillip Mills <pmills(at)systemcore(dot)ca>* wrote:
>
> From: Phillip Mills <pmills(at)systemcore(dot)ca>
> Subject: [GENERAL] Partial Index Too Literal?
> To: pgsql-general(at)postgresql(dot)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?
>
>
>