Really long - running query when using order by

I have a major issue with one of my queries:

SELECT tpostime, gispoint 
FROM mytable 
WHERE idterminal = 233463 
ORDER BY idpos DESC

When idterminal does not exist in 'mytable' then this query is being processed forever, and then I'm presented with timeout (well 'canceling statement due to user request' message to be specific), but when I remove the order by clause, everything seems fine. Now I'm wondering - idpos is primary key for 'mytable', therefore it's indexed so ordering by it should be fast, I guess. And what's important - 'mytable' weights 3gb.

Table and index definitions:

CREATE TABLE mytable ( 
  idpos serial NOT NULL, 
  开发者_JAVA百科tpostime timestamp(0) without time zone, 
  idterminal integer DEFAULT 0, 
  gispoint geometry, 
  idtracks integer, 
  CONSTRAINT mytable_pkey PRIMARY KEY (idpos), 
  CONSTRAINT qwe FOREIGN KEY (idtracks) REFERENCES qwe (idtracks) 
    MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE, 
  CONSTRAINT abc FOREIGN KEY (idterminal) REFERENCES abc (idterminal)
    MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE, 
  CONSTRAINT enforce_geotype_gispoint 
    CHECK (geometrytype(gispoint)= 'POINT'::text OR gispoint IS NULL), 
  CONSTRAINT enforce_srid_gispoint CHECK (srid(gispoint) = 4326)
) WITH OIDS; 

CREATE INDEX idx_idterminal ON mytable USING btree (idterminal); 
CREATE INDEX idx_idtracks ON mytable USING btree (idtracks); 
CREATE INDEX idx_idtracks_idterminal ON mytable USING btree (idtracks, idterminal);

---

It looks to me like the selectivity of idterminal is low enough for postgres to choose a full scan of mytable_pkey rather than the cost of ordering all the rows with idterminal = 233463

I suggest:

CREATE INDEX idx_idterminal2 ON mytable USING btree (idterminal, idpos);

and perhaps:

DROP INDEX idx_idterminal;

You don't mention if this is a production database or not - if it is of course you will need to test the impact of the change first elsewhere.

If you prefer not to change the schema you might like to try and trick the optimizer into the path you know is best with something like (not tested) for 8.4 and above:

SELECT * 
FROM ( SELECT tpostime, gispoint, idpos, row_number() over (order by 1)
       FROM mytable 
       WHERE idterminal = 233463 )
ORDER BY idpos DESC;

or perhaps just:

SELECT * 
FROM ( SELECT tpostime, gispoint, idpos
       FROM mytable 
       WHERE idterminal = 233463
       GROUP BY tpostime, gispoint, idpos )
ORDER BY idpos DESC;

or even:

SELECT tpostime, gispoint 
FROM mytable 
WHERE idterminal = 233463 
ORDER BY idpos*2 DESC

---

Do you have an index on idterminal? Try adding a composite index with both (idpos, idterminal). What is probably happening if you do the explain plan, is it is ordering by idpos first, then scanning to find idterminal.