Search Postgresql Archives

Re: Partial indexes instead of partitions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 11 June 2010 17:15, Leonardo F <m_lists@xxxxxxxx> 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.ru@xxxxxxxxx / Skype: gray-hemp / ICQ: 29353802

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux