Thread: Partial Index Too Literal?
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?
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?
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
| 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> |
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 PMUnder 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?