Re: index on partitioned table

2010/2/5 Wojtek <foo(at)twine(dot)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