From: Nikolas Everett <nik9000@gmail.com>
To: Wojtek <foo@twine.pl>
Cc: pgsql-performance@postgresql.org
Subject: Re: index on partitioned table
Date: Fri, 5 Feb 2010 10:08:28 -0500
Message-ID: <d4e11e981002050708r6f14daa5o340a27465dd65b82@mail.gmail.com> (raw)
In-Reply-To: <15213497474b6c0fe595df07.79488231.Active.mail@starapoczta.nazwa.pl>
References: <15213497474b6c0fe595df07.79488231.Active.mail@starapoczta.nazwa.pl>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nik9000@gmail.com, foo@twine.pl
Subject: Re: index on partitioned table
In-Reply-To: <d4e11e981002050708r6f14daa5o340a27465dd65b82@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox