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

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

From

"DANG Trieu"

Date:

18 November 2008, 13:57:39

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)
);

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

From

tv@fuzzy.cz

Date:

18 November 2008, 14:05:46

A lot of important information is missing in your post, for example:

a) Was the table analyzed recently? Is the table vacuumed regularly?
b) How large are the tables? (Number of tuples and pages. SELECT
reltuples, relpages FROM pg_class WHERE relname LIKE 'event%')
c) What values are used for the important config options (work_mem is
important here)
d) What is the basic hw config (disk drives, etc.)
e) Are there any indexes on the tables? Try to create index on columns
used in the "order by" clause.
f) What is the exact query you're optimizing? Run it with EXPLAIN ANALYZE
and post the output here.

regards
Tomas


> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

From

Martin Gainty

Date:

18 November 2008, 16:09:07

Index all the columns used in the join conditions e.g.
a.user_id=b.user_id
need unique indexes on both
a.user_id
b.user_id

(default is to FTS which will slow your query to a crawl)

HTH
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.

> Date: Tue, 18 Nov 2008 16:06:16 +0100
> Subject: Re: [GENERAL] Slow queries when ORDER BY ... DESC with table inheritance (no index scan backwards)
> From: tv@fuzzy.cz
> To: tdang@miranda.com
> CC: pgsql-general@postgresql.org
>
> A lot of important information is missing in your post, for example:
>
> a) Was the table analyzed recently? Is the table vacuumed regularly?
> b) How large are the tables? (Number of tuples and pages. SELECT
> reltuples, relpages FROM pg_class WHERE relname LIKE 'event%')
> c) What values are used for the important config options (work_mem is
> important here)
> d) What is the basic hw config (disk drives, etc.)
> e) Are there any indexes on the tables? Try to create index on columns
> used in the "order by" clause.
> f) What is the exact query you're optimizing? Run it with EXPLAIN ANALYZE
> and post the output here.
>
> regards
> Tomas
>
>
> > 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@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Windows Live Hotmail now works up to 70% faster. Sign up today.