DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:user-agent:mime-version:to:subject:content-type:content-transfer-encoding;
	b=eiaLcXDfyD2RQ3FPkSvuh14fh9RYDKm3kYd6wpMJ4CSOOglL8Et/3uWaUFcNtR92IBXoWDKNKyhBTRD0MlsvFXKqw28fgMWzTr2by30lE+VN+rKe6KSpzKGNe51kY9jwWRozydsjbpG9CKb9sIbJ+PuSdBQylD4iwDBTQPQKqZs=
Message-ID: <43A998D4.4070300@gmail.com>
Date: Wed, 21 Dec 2005 19:03:00 +0100
From: =?ISO-8859-2?Q?Sz=FBcs_G=E1bor?= <surrano@gmail.com>
User-Agent: Thunderbird 1.5 (Windows/20051025)
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Wrong index used when ORDER BY LIMIT 1
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit

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)