2010/2/5 Wojtek
<foo@twine.pl> partitions are will typically have from 200k to 300k rows, i have 52 partitions per year and I'm keeping around 4-5 years of history. However, they will query last 3-4 months most often.
Do you mean 12 partitions a year or weekly partitions?
Most of my queries will have where conditions on timedate and sys_device_id, but a lot of them will have additional clause: where usefields is not null. Some of the queries will be limited on timedate only.
I'm trying to figure out the best indexing strategy for this table. If a query will have condition on sys_device_id and/or usefields is not null, postgres won't use my index.
I've experimented turning on and off enable_seqscan and creating different indexes and so far btree index on (usefields, sys_device_id, timedate) turn out to be the best.
If I create btree index only on (usefields, timedate) or (sys_device_id, timedate), planner will go for seqscan. If I turn off seqscan, postgres will use index but performance will be worse than seqscan.
My question finally: is btree index on (usefields, sys_device_id, timedate) really the best choice? I'm yet to examine options of creating separate indexes for timedate, usefields and sys_device_id. Possibly I should try using GiST or GIN?
I'd start with no indexes and then add indexes as your queries start to take too long. I'd start with single column indexes. PostgreSQL is perfectly capable of bitmap anding the indexes if it has to. Multicolumn indexes are the last place I'd go.
I'm not sure you'll need an index on timedate. It depends on the length of the timedate segments you'll be querying. If they are typically a month long then you shouldn't have an index on it at all. Even if they are a week long its probably not worth it.
My guess is that an index sys_device_id will be selective enough for most of what you need. What does PostgreSQL tell you about the statistics of that column?
Regards,
foo
Regards,
bar