Date: Wed, 10 Aug 2005 19:52:08 +0200
From: Tobias Brox <tobias@nordicbet.com>
To: pgsql-performance@postgresql.org
Subject: partial index regarded more expensive
Message-ID: <20050810175208.GD6141@tobias.lan>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Organization: Group Nordicbet
User-Agent: Mutt/1.5.8i

So, I have a table game with a timestamp attribute 'game_end', ranging from
jan-2005 to present.  The game table also have an attribute state, with live
games beeing in state 2, and ended games beeing in state 4 (so,
game_end+delta>now() usually means state=4).  There are also an insignificant
number of games in states 1,3.

This query puzzles me:

  select * from game where game_end>'2005-07-30' and state in (3,4);
  
Now, one (at least me) should believe that the best index would be a partial
index,

    "resolved_game_by_date" btree (game_end) WHERE ((state = 3) OR (state = 4))
    
NBET=> explain analyze select * from game where game_end>'2005-07-30' and state in (3,4);
                                                               QUERY PLAN   

----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using resolved_game_by_date on game  (cost=0.00..7002.87 rows=7147 width=555) (actual time=0.220..86.234 rows=3852 loops=1)
   Index Cond: (game_end > '2005-07-30 00:00:00'::timestamp without time zone)
   Filter: ((state = 3) OR (state = 4))
 Total runtime: 90.568 ms
(4 rows)
      
Since state has only two significant states, I wouldn't believe this index
to be any good:

    "game_by_state" btree (state)
    

...and it seems like I'm right:

NBET=> explain analyze select * from game where game_end>'2005-07-30' and
state in (3,4);
                                                                   QUERY
PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using game_by_state, game_by_state on game  (cost=0.00..4413.78 rows=7147 width=555) (actual time=0.074..451.771 rows=3851 loops=1)
   Index Cond: ((state = 3) OR (state = 4))
      Filter: (game_end > '2005-07-30 00:00:00'::timestamp without time zone)
 Total runtime: 457.132 ms
(4 rows)

Now, how can the planner believe the game_by_state-index to be better?

('vacuum analyze game' did not significantly impact the numbers, and I've
tried running the queries some times with and without the
game_by_state-index to rule out cacheing effects)

-- 
Tobias Brox
This signature has been virus scanned, and is probably safe to read.
This mail may contain confidential information, please keep your eyes closed.