Wrong index used when ORDER BY LIMIT 1 - Mailing list pgsql-performance

Dear Gurus,

Version: 7.4.6

I use a query on a heavily indexed table which picks a wrong index
unexpectedly. Since this query is used in response to certain user
interactions thousands of times in succession (with different constants),
500ms is not affordable for us. I can easily work around this, but I'd like
to understand the root of the problem.

Basically, there are two relevant indexes:
- muvelet_vonalkod_muvelet btree (muvelet, ..., idopont)
- muvelet_vonalkod_pk3 btree (idopont, ...)

Query is:
SELECT idopont WHERE muvelet = x ORDER BY idopont LIMIT 1.

I expected the planner to choose the index on muvelet, then sort by idopont.
Instead, it took the other index. I think there is heavy correlation since
muvelet references to a sequenced pkey and idopont is a timestamp (both
increase with passing time). May that be a cause?

See full table description and explain analyze results at end of the email.


TIA,
--
G.

---- table :
            Table "public.muvelet_vonalkod"
    Column   |           Type           |  Modifiers
------------+--------------------------+-----------------------------------
  az         | integer                  | not null def. nextval('...')
  olvaso_nev | character varying        | not null
  vonalkod   | character varying        | not null
  mozgasnem  | integer                  | not null
  idopont    | timestamp with time zone | not null
  muvelet    | integer                  |
  minoseg    | integer                  | not null
  cikk       | integer                  |
  muszakhely | integer                  |
  muszakkod  | integer                  |
  muszaknap  | date                     |
  repre      | boolean                  | not null default false
  hiba       | integer                  | not null default 0
Indexes:
     "muvelet_vonalkod_pkey" primary key, btree (az)
     "muvelet_vonalkod_pk2" unique, btree (olvaso_nev, idopont)
     "muvelet_vonalkod_muvelet" btree
         (muvelet, mozgasnem, vonalkod, olvaso_nev, idopont)
     "muvelet_vonalkod_pk3" btree (idopont, olvaso_nev)
     "muvelet_vonalkod_vonalkod" btree
         (vonalkod, mozgasnem, olvaso_nev, idopont)
Foreign-key constraints:
     "$1" FOREIGN KEY (mozgasnem) REFERENCES mozgasnem(az)
     "$2" FOREIGN KEY (muvelet) REFERENCES muvelet(az)
     "$3" FOREIGN KEY (minoseg) REFERENCES minoseg(az)
     "$4" FOREIGN KEY (cikk) REFERENCES cikk(az)
     "$5" FOREIGN KEY (muszakhely) REFERENCES hely(az)
     "$6" FOREIGN KEY (muszakkod) REFERENCES muszakkod(az)
     "muvelet_vonalkod_muszak_fk"
        FOREIGN KEY (muszakhely, muszaknap, muszakkod)
        REFERENCES muszak(hely, nap, muszakkod)
Triggers:
     muvelet_vonalkod_aiud AFTER INSERT OR DELETE OR UPDATE ON
muvelet_vonalkod FOR EACH ROW EXECUTE PROCEDURE muvelet_vonalkod_aiud()
     muvelet_vonalkod_biu BEFORE INSERT OR UPDATE ON muvelet_vonalkod FOR
EACH ROW EXECUTE PROCEDURE muvelet_vonalkod_biu()
     muvelet_vonalkod_noty AFTER INSERT OR DELETE OR UPDATE ON
muvelet_vonalkod FOR EACH ROW EXECUTE PROCEDURE muvelet_vonalkod_noty()


-- original query, limit
# explain analyze
   select idopont from muvelet_vonalkod
   where muvelet=6859 order by idopont
   limit 1;
                                QUERY PLAN

----------------------------------------------------------------------------
  Limit  (cost=0.00..25.71 rows=1 width=8) (actual time=579.528..579.529
rows=1 loops=1)
    ->  Index Scan using muvelet_vonalkod_pk3 on muvelet_vonalkod
(cost=0.00..8304.42 rows=323 width=8) (actual time=579.522..579.522 rows=1
loops=1)
          Filter: (muvelet = 6859)
  Total runtime: 579.606 ms
(4 rows)

-- however, if I omit the limit clause:
# explain analyze
   select idopont from muvelet_vonalkod
   where muvelet=6859 order by idopont;
                                QUERY PLAN

---------------------------------------------------------------------------
  Sort  (cost=405.41..405.73 rows=323 width=8) (actual time=1.295..1.395
rows=360 loops=1)
    Sort Key: idopont
    ->  Index Scan using muvelet_vonalkod_muvelet on muvelet_vonalkod
(cost=0.00..400.03 rows=323 width=8) (actual time=0.049..0.855 rows=360 loops=1)
          Index Cond: (muvelet = 6859)
  Total runtime: 1.566 ms
(5 rows)

-- workaround 1: the planner is hard to trick...
# explain analyze
   select idopont from
   (select idopont from muvelet_vonalkod
    where muvelet=6859) foo
   order by idopont limit 1;
                                QUERY PLAN

---------------------------------------------------------------------------
  Limit  (cost=0.00..25.71 rows=1 width=8) (actual time=584.403..584.404
rows=1 loops=1)
    ->  Index Scan using muvelet_vonalkod_pk3 on muvelet_vonalkod
(cost=0.00..8304.42 rows=323 width=8) (actual time=584.397..584.397 rows=1
loops=1)
          Filter: (muvelet = 6859)
  Total runtime: 584.482 ms
(4 rows)

-- workaround 2: quite ugly but seems to work (at least for this
-- one test case):
# explain analyze
   select idopont from
   (select idopont from muvelet_vonalkod
    where muvelet=6859 order by idopont) foo
   order by idopont limit 1;
                                QUERY PLAN

---------------------------------------------------------------------------
  Limit  (cost=405.41..405.42 rows=1 width=8) (actual time=1.754..1.755
rows=1 loops=1)
    ->  Subquery Scan foo  (cost=405.41..407.35 rows=323 width=8) (actual
time=1.751..1.751 rows=1 loops=1)
          ->  Sort  (cost=405.41..405.73 rows=323 width=8) (actual
time=1.746..1.746 rows=1 loops=1)
                Sort Key: idopont
                ->  Index Scan using muvelet_vonalkod_muvelet on
muvelet_vonalkod  (cost=0.00..400.03 rows=323 width=8) (actual
time=0.377..1.359 rows=360 loops=1)
                      Index Cond: (muvelet = 6859)
  Total runtime: 1.853 ms
(7 rows)