Partial Index Too Literal?
От | Phillip Mills |
---|---|
Тема | Partial Index Too Literal? |
Дата | |
Msg-id | dd0408e50806261224q146c676bw7a386fb6bc7bb266@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Partial Index Too Literal?
Re: Partial Index Too Literal? |
Список | 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?
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?
В списке pgsql-general по дате отправления: