index on partitioned table

From: Wojtek
Subject: index on partitioned table
Date: ,
Msg-id: 15213497474b6c0fe595df07.79488231.Active.mail@starapoczta.nazwa.pl
(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;

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
ANDtimedate < '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
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
  (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/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?

Regards,
foo




pgsql-performance by date:

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