Databases
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
Go Back   Web Development Archives Mailing Lists Databases

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Web Development Archives Sponsor:
  #1  
Old June 26th, 2008, 02:51 PM
Phillip Mills
Guest
Dev Archives Newbie (0 - 499 posts)
 
Posts: n/a  
Time spent in forums:
Reputation Power:
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?

Reply With Quote
Reply

Viewing: Web Development Archives Mailing Lists Databases > Partial Index Too Literal?


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are Off
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 5 hosted by Hostway
Stay green...Green IT