Re: [GENERAL] Partial Index Too Literal?
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 [EMAIL PROTECTED] wrote: use this explain analyze select * from result where active = 't'; --- On *Thu, 6/26/08, Phillip Mills [EMAIL PROTECTED]* wrote: From: Phillip Mills [EMAIL PROTECTED] 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?
[GENERAL] 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?
Re: [GENERAL] Partial Index Too Literal?
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 [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Partial Index Too Literal?
use this explain analyze select * from result where active = 't'; --- On Thu, 6/26/08, Phillip Mills [EMAIL PROTECTED] wrote: From: Phillip Mills [EMAIL PROTECTED] 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?
