Unregistered <Guest.10y6ky@mail.webservertalk.com> writes:
> So what I did was:
> made an "today_idx", "2_weeks_idx", "1 month_idx" on the attribute
> "sent_date" to speed up the queries using the "sent_date"
> attribute,which 90% of the times is queried on. These indexes are
> rebuild every night.
> When I was testing this setup, the indexes I used where like:
> today_idx: 1/02
> 2_weeks_idx: 18/01 --> 1/02
> month_idx: 2/01 --> 1/02
> When quering something like:
> select * from messages where sent_date = '2004-02-01'
> the optimiser chooses to search in the month_idx index instead of using
> the optimal today_idx. This is because today (1/02) is also included in
> the month_idx (and also the 2weeks_idx)
There's really hardly any difference in practice. The exact same number
of index entries will be scanned in both cases. If you're lucky there
might be one less level of btree to descend through at the start of the
scan, but probably not more than that considering the index fanout will
be in the hundreds. The planner's cost model for this is too crude to
tell the difference, and so it estimates the costs the same and might
choose either index.
The above really isn't a very profitable way to use partial indexes,
anyway. You're triply indexing the most active part of the table,
thus tripling your index-updating costs, for what certainly won't
be a 3x payoff in access time. See the Postgres user manual for some
more plausible use-cases for partial indexes.
regards, tom lane
