From: | Unregistered <Guest(dot)10y6ky(at)mail(dot)webservertalk(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | partial indexes |
Date: | 2004-02-01 12:14:51 |
Message-ID: | Guest.10y6ky@mail.webservertalk.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have following situation:
one "message" table and 3 other "message_{1,2,3}" tables which inherit
from "message".
Every day +-50 000 "messages" are being inserted.
I needed something extra to speed up the queries instead of only using
indexes, so I thought of doing something like you can do in Oracle,
table partitioning.
In postgresql you can simulate this using the partial indexes.
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)
So I had to recreate the indexes excluding the previous ones and
borders. Is this normal?
My question:
Is this a good way to solve the problem? are there any other ways I can
do this?
Is the inheritance a good feature / stable in postgresql (not a lot of
documentation about)
Tnx in advance
Unregistered -
------------------------------------------------------------------------
Posted via http://www.webservertalk.com
------------------------------------------------------------------------
View this thread: http://www.webservertalk.com/message106661.html
From | Date | Subject | |
---|---|---|---|
Next Message | Rajesh Kumar Mallah | 2004-02-01 20:36:09 | Re: Functions in CHECK constraint not getting dumped before |
Previous Message | Oleg Bartunov | 2004-02-01 09:58:02 | Re: Search across multiple sources |