Re: Slow queries when ORDER BY ... DESC with table inheritance (no index scan backwards) - Mailing list pgsql-general

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
>