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: Slow queries when ORDER BY ... DESC with table inheritance (no index scan backwards) > From: tv@xxxxxxxx > To: tdang@xxxxxxxxxxx > CC: pgsql-general@xxxxxxxxxxxxxx > > 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@xxxxxxxxxxxxxx) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > 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. |