From: Tobias Brox <tobias@nordicbet.com>
To: pgsql-performance@postgresql.org
Subject: partial index regarded more expensive
Date: Wed, 10 Aug 2005 19:52:08 +0200
Message-ID: <20050810175208.GD6141@tobias.lan> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tobias@nordicbet.com
Subject: Re: partial index regarded more expensive
In-Reply-To: <20050810175208.GD6141@tobias.lan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox