|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
|
#1
|
|||
|
|||
|
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.31472.34 rows=4206 width=1126) (actual time=7.8687.868 rows=0 loops=1) Filter: active -Bitmap Index Scan on result_active_idx (cost=0.004.26 rows=2103 width=0) (actual time=4.1384.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.00537.26 rows=4263 width=1126) (actual time=55.63155.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? |
![]() |
| Viewing: Web Development Archives > Mailing Lists > Databases > Partial Index Too Literal? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|