index on partitioned table

From: Wojtek
Subject: index on partitioned table
Date: ,
(view: Whole thread, Raw)
Responses: Re: index on partitioned table  (Nikolas Everett)
Re: index on partitioned table  (Robert Haas)
List: pgsql-performance

Dear Postgres Community,

I'm running postgres 8.3

I have a table, partitioned by month

-- Table: datadump

-- DROP 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
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
ANDtimedate < '2010-09-13 00:00:00'::timestamp without time zone) 
INHERITS (datadump)
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
yearsof 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

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/orusefields 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
turnoff 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
optionsof creating separate indexes for timedate, usefields and sys_device_id. Possibly I should try using GiST or GIN? 

Any advice, please?


pgsql-performance by date:

From: Robert Haas
Subject: Re: index on partitioned table
From: Glenn Maynard
Subject: Re: Slow query: table iteration (8.3)