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

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.