From: | "Phillip Mills" <pmills(at)systemcore(dot)ca> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Partial Index Too Literal? |
Date: | 2008-06-26 19:24:41 |
Message-ID: | dd0408e50806261224q146c676bw7a386fb6bc7bb266@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2008-06-26 19:50:59 | Re: Partial Index Too Literal? |
Previous Message | Brandon Metcalf | 2008-06-26 18:17:36 | Re: new RETURNING clause and Pg.pm |