partial index regarded more expensive

From: Tobias Brox
Subject: partial index regarded more expensive
Date: ,
Msg-id: 20050810175208.GD6141@tobias.lan
(view: Whole thread, Raw)
Responses: Re: partial index regarded more expensive  (PFC)
Re: partial index regarded more expensive  (Tom Lane)
List: pgsql-performance

Tree view

partial index regarded more expensive  (Tobias Brox, )
 Re: partial index regarded more expensive  (PFC, )
  Re: partial index regarded more expensive  (Tobias Brox, )
 Re: partial index regarded more expensive  (Tom Lane, )

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=3852loops=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.771rows=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.


pgsql-performance by date:

From: Dan Harris
Date:
Subject: Speedier count(*)
From: "Joshua D. Drake"
Date:
Subject: Re: Speedier count(*)