index on partitioned table

From: "Wojtek" <foo(at)twine(dot)pl>
To: pgsql-performance(at)postgresql(dot)org
Subject: index on partitioned table
Date: 2010-02-05 12:32:37
Message-ID: 15213497474b6c0fe595df07.79488231.Active.mail@starapoczta.nazwa.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Dear Postgres Community,

I'm running postgres 8.3

I have a table, partitioned by month

-- Table: datadump

-- DROP TABLE datadump;

CREATE TABLE datadump
(
sys_timestamp timestamp without time zone,
sys_device_id integer,
usefields integer,
timedate timestamp without time zone,
digitalthermometer1 integer,
digitalthermometer2 integer,
digitalthermometer3 integer,
digitalthermometer4 integer,
digitalthermometer5 integer,
digitalthermometer6 integer,
tco0 integer,
tco1 integer,
tco2 integer,
tco3 integer
)
WITH (
OIDS=FALSE
)
TABLESPACE disk_d;
ALTER TABLE datadump OWNER TO postgres;
GRANT ALL ON TABLE datadump TO postgres;

partitioned by timedate, example:

CREATE TABLE data_dmp_part_201036
(
{inherits from master table}
CONSTRAINT data_dmp_part_201036_timedate_check CHECK (timedate >= '2010-09-06 00:00:00'::timestamp without time zone AND timedate < '2010-09-13 00:00:00'::timestamp without time zone)
)
INHERITS (datadump)
WITH (
OIDS=FALSE
);
ALTER TABLE data_dmp_part_201036 OWNER TO postgres;

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.

my first, pretty obvious choice, was to create index on partitions on timedate:
CREATE INDEX data_dmp_part_201036_idx
ON data_dmp_part_201036
USING btree
(timedate);

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?

Any advice, please?

Regards,
foo

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Nikolas Everett 2010-02-05 15:08:28 Re: index on partitioned table
Previous Message Yeb Havinga 2010-02-05 11:17:07 Re: Slow query: table iteration (8.3)