Thread: Partial Index Too Literal?

Partial Index Too Literal?

From
"Phillip Mills"
Date:
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?

Re: Partial Index Too Literal?

From
Martijn van Oosterhout
Date:
On Thu, Jun 26, 2008 at 03:24:41PM -0400, Phillip Mills wrote:
> dev=# explain analyze select * from result where active = true;
> dev=# explain analyze select * from result where active is true;

> This is version 8.2.6.  Is there something I'm missing that could make these
> queries ever produce different results?

As usual the counter example is when there a NULL where the two
expressions arn't equal. That this doesn't affect the result of the
query is I suppose a deficiency of the expression comparitor...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Attachment

Re: Partial Index Too Literal?

From
Lennin Caro
Date:
use this

explain analyze select * from result where active = 't';

--- On Thu, 6/26/08, Phillip Mills <pmills@systemcore.ca> wrote:
From: Phillip Mills <pmills@systemcore.ca>
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?


Re: Partial Index Too Literal?

From
"Phillip Mills"
Date:
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.caro@yahoo.com> wrote:
use this

explain analyze select * from result where active = 't';

--- On Thu, 6/26/08, Phillip Mills <pmills@systemcore.ca> wrote:
From: Phillip Mills <pmills@systemcore.ca>
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?