Re: Partial indexes instead of partitions

On 11 June 2010 17:15, Leonardo F <m_lists(at)yahoo(dot)it> wrote:
> Basically what I'm trying to do is to partition the index in the table
> where the data is going to be inserted into smaller indexes, but
> without using partitions: I would use partial indexes.
> "Historic" data will have just the big index...

Well, you can estimate if it's worth bothering with index
partitioning. For "selects" you should compare

logM(N)
N - number of records
M - (base) number of records in b-tree node (in one 8k page)

for whole table partition and index partition but I do not think the
difference would be great. For "inserts" I do not see the reason why
it would be better to use index partitioning because AFAIK b-tree
would behave exactly the same in both cases.

> That is, the table where data will be inserted (ts will always be
> ascending, so I will always insert data in the latest table)
> will have multiple small indexes.
> Then, at night, the small indexes would be dropped after one big
> index has been created (since no more rows will be inserted in that
> table, I don't care if the index is big).
>
> So, a query like:
> select * from master where key1=938479
> and ts between now() and "now()-10 minutes"

You should explicitly state the index conditions and the partition
conditions here otherwise they would not be used

SELECT * FROM master
WHERE
-- For table partition
ts >= '2006-03-10' AND
ts < '2006-04-10' AND
-- For index partition
ts >= '2006-03-10 01:00' AND
ts < '2006-03-10 02:00' AND
-- Target conditions
key1 = 938479 AND
ts BETWEEN now() AND now() - interval '10 minutes';

Furthermore I would suggest you to use this index

CREATE INDEX master_10_2_ix1
ON master_10 (key1, ts)
WHERE
ts >= '2006-03-10 01:00' and
ts < '2006-03-10 02:00';

if you want "Target conditions" to work optimal way.

> a query like:
> select * from master where key1=938479
> and ts between "3 days ago" and "2 days ago"

You can not use BETWEEN here because it is equal to "ts >= ... AND ts
<= ..." not "ts >= ... AND ts < ..." as specified in the table
definition. See above.

--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray(dot)ru(at)gmail(dot)com / Skype: gray-hemp / ICQ: 29353802