Slow queries when ORDER BY ... DESC with table inheritance (no index scan backwards)

["DANG Trieu" <tdang@xxxxxxxxxxx>]

Hi all,

I'm a newbie to Postgres so please bear with me. I have a schema that
uses inherited tables. I need the queries on my 'event' table to always
be in descending order of the primary key, i.e. scan the index backwards
(for obvious performance reasons). Somehow the ORDER BY doesn't seem to
be propagated to the inherited tables (event_a), hence no backward index
scan.

Here's an example query:
select * from event where timestamp < 1234567890 order by timestamp
desc;

I'm using version 8.1.3.

I haven't found any relevant information in the docs or the mailing
lists. Is this a known bug? Is there a workaround?

Thanks in advance.
Luke

------------------------------------

CREATE TABLE event (
  timestamp BIGINT NOT NULL,
  gsmTimestamp BIGINT NOT NULL,  
  alarmURI VARCHAR(255) NOT NULL,
  alarmName VARCHAR(255),
  deviceURI VARCHAR(255),
  deviceClass VARCHAR(255),
  typeId INTEGER NOT NULL,
  userName VARCHAR(255),
  groupPath VARCHAR(255),
  oldState INTEGER NOT NULL,
  newState INTEGER NOT NULL,
  oldLatch INTEGER NOT NULL,
  newLatch INTEGER NOT NULL,
  oldAck INTEGER NOT NULL,
  newAck INTEGER NOT NULL,
  oldMode INTEGER NOT NULL,
  newMode INTEGER NOT NULL,
  timecode bigint NOT NULL,
  text VARCHAR(255),
  extraInfo VARCHAR(255),
  PRIMARY KEY (timestamp, alarmURI)
);

CREATE TABLE event_a (
  PRIMARY KEY (timestamp, alarmURI)
) inherits (event);

CREATE TABLE event_b (
  PRIMARY KEY (timestamp, alarmURI)
) inherits (event);

CREATE TABLE event_1 (
  PRIMARY KEY (timestamp, alarmURI)
) inherits (event);

CREATE or REPLACE RULE insert_to_event AS
ON INSERT TO event DO INSTEAD  
INSERT INTO event_a ("timestamp", gsmtimestamp, alarmuri, alarmname,
deviceuri, deviceclass, typeid, username, grouppath, oldstate, newstate,
oldlatch, newlatch, oldack, newack, oldmode, newmode, timecode, text,
extrainfo)
VALUES (new."timestamp", new.gsmtimestamp, new.alarmuri, new.alarmname,
new.deviceuri, new.deviceclass, new.typeid, new.username, new.grouppath,
new.oldstate, new.newstate, new.oldlatch, new.newlatch, new.oldack,
new.newack, new.oldmode, new.newmode, new.timecode, new.text,
new.extrainfo);


CREATE TABLE eventCause (
  eventTimestamp BIGINT NOT NULL,
  eventURI VARCHAR(255) NOT NULL,
  causeTimestamp BIGINT NOT NULL,
  causeURI VARCHAR(255) NOT NULL,
  PRIMARY KEY (eventTimestamp, eventURI, causeURI, causeTimestamp)
);

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general