DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type;
	b=j5ygXviAJFUjBWAzrLSgBzVXYRoUa5Hu62weZEWk6Om40mNkuNz6jYElHIDBHQwrXF
	BvDYQWpt11tjoohIgNpzizVFdbNAPXIO0bs0nzOrigNemamm3OzpJ4eJjbGxbBpU1kLf
	nG4/bDMy9/7qBsq1wizbpr86vSlg4gePRKxiA=
MIME-Version: 1.0
In-Reply-To: 
 <15213497474b6c0fe595df07.79488231.Active.mail@starapoczta.nazwa.pl>
References: 
 <15213497474b6c0fe595df07.79488231.Active.mail@starapoczta.nazwa.pl>
From: Nikolas Everett <nik9000@gmail.com>
Date: Fri, 5 Feb 2010 10:08:28 -0500
Message-ID: <d4e11e981002050708r6f14daa5o340a27465dd65b82@mail.gmail.com>
Subject: Re: index on partitioned table
To: Wojtek <foo@twine.pl>
Cc: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=0016e6d63fff1f6fcf047edbd430

--0016e6d63fff1f6fcf047edbd430
Content-Type: text/plain; charset=ISO-8859-1

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

--0016e6d63fff1f6fcf047edbd430
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<br><br><div class=3D"gmail_quote">2010/2/5 Wojtek <span dir=3D"ltr">&lt;<a=
 href=3D"mailto:foo@twine.pl">foo@twine.pl</a>&gt;</span><blockquote class=
=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padd=
ing-left:1ex;">


partitions are will typically have from 200k to 300k rows, i have 52 partit=
ions per year and I&#39;m keeping around 4-5 years of history. However, the=
y will query last 3-4 months most often.<br></blockquote><div>Do you mean 1=
2 partitions a year or weekly partitions?</div>

<div>=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;=
border-left:1px #ccc solid;padding-left:1ex;">
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 nul=
l. Some of the queries will be limited on timedate only.<br>
<br>
I&#39;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&#39;t use my index.<br>
I&#39;ve experimented turning on and off enable_seqscan and creating differ=
ent indexes and so far btree index on (usefields, sys_device_id, timedate) =
turn out to be the best.<br>
If I create btree index only on (usefields, timedate) or (sys_device_id, ti=
medate), planner will go for seqscan. If I turn off seqscan, postgres will =
use index but performance will be worse than seqscan.<br>
<br>
<br>
My question finally: is btree index on (usefields, sys_device_id, timedate)=
 really the best choice? I&#39;m yet to examine options of creating separat=
e indexes for timedate, usefields and sys_device_id. Possibly I should try =
using GiST or GIN?<br>

</blockquote><div><br></div><div>I&#39;d start with no indexes and then add=
 indexes as your queries start to take too long. =A0I&#39;d start with sing=
le column indexes. =A0PostgreSQL is perfectly capable of bitmap anding the =
indexes if it has to. =A0Multicolumn indexes are the last place I&#39;d go.=
</div>

<div><br></div><div>I&#39;m not sure you&#39;ll need an index on timedate. =
=A0It depends on the length of the timedate segments you&#39;ll be querying=
. =A0If they are typically a month long then you shouldn&#39;t have an inde=
x on it at all. =A0Even if they are a week long its probably not worth it.<=
/div>

<div><br></div><div>My guess is that an index sys_device_id will be selecti=
ve enough for most of what you need. =A0What does PostgreSQL tell you about=
 the statistics of that column?</div><div>=A0</div><blockquote class=3D"gma=
il_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-lef=
t:1ex;">


Regards,<br>
foo</blockquote><div><br></div><div>Regards,</div><div>bar=A0</div></div>

--0016e6d63fff1f6fcf047edbd430--