Unique index and estimated rows. - Mailing list pgsql-performance

Hi, more strange plans ...

Planner estimates an indexscan to return 240 rows although it is using a
unique index and chooses to use hash join and seqscan instead of nested
loop and indexscan. It's ... very slow.

Idexes used:
  users:  "users_upper_nick" unique, btree (upper((nick)::text))
  image:  "image_uid_status" btree (uid, status)

galleria=> set enable_hashjoin = true;
SET
galleria=> explain analyze SELECT i.image_id, i.info, i.stamp, i.status, i.t_width, i.t_height, u.nick, u.uid FROM
imagei, users u WHERE i.uid = u.uid AND upper(u.nick) = upper('FireGirl-') AND i.status IN ('d', 'v') AND u.status =
'a'ORDER BY status, stamp DESC; 
                                                                   QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=24731.07..24734.95 rows=1550 width=63) (actual
time=1392.675..1392.686 rows=19 loops=1)
   Sort Key: i.status, i.stamp
   ->  Hash Join  (cost=961.31..24648.94 rows=1550 width=63) (actual time=552.184..1392.617 rows=19 loops=1)
         Hash Cond: ("outer".uid = "inner".uid)
         ->  Seq Scan on image i  (cost=0.00..22025.22 rows=329382 width=53) (actual time=0.009..1088.856 rows=346313
loops=1)
               Filter: ((status = 'd'::bpchar) OR (status = 'v'::bpchar))
         ->  Hash  (cost=960.71..960.71 rows=240 width=14) (actual time=0.043..0.043 rows=0 loops=1)
               ->  Index Scan using users_upper_nick on users u  (cost=0.00..960.71 rows=240 width=14) (actual
time=0.037..0.039rows=1 loops=1) 
                     Index Cond: (upper((nick)::text) = 'FIREGIRL-'::text)
                     Filter: (status = 'a'::bpchar)
 Total runtime: 1392.769 ms
(11 rows)

galleria=> set enable_hashjoin = false;
SET
galleria=> explain analyze SELECT i.image_id, i.info, i.stamp, i.status, i.t_width, i.t_height, u.nick, u.uid FROM
imagei, users u WHERE i.uid = u.uid AND upper(u.nick) = upper('FireGirl-') AND i.status IN ('d', 'v') AND u.status =
'a'ORDER BY status, stamp DESC; 
                                                                QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=35861.87..35865.74 rows=1550 width=63) (actual
time=0.230..0.244 rows=19 loops=1)
   Sort Key: i.status, i.stamp
   ->  Nested Loop  (cost=0.00..35779.73 rows=1550 width=63) (actual time=0.070..0.173 rows=19 loops=1)
         ->  Index Scan using users_upper_nick on users u  (cost=0.00..960.71 rows=240 width=14) (actual
time=0.036..0.038rows=1 loops=1) 
               Index Cond: (upper((nick)::text) = 'FIREGIRL-'::text)
               Filter: (status = 'a'::bpchar)
         ->  Index Scan using image_uid_status on image i  (cost=0.00..144.83 rows=20 width=53) (actual
time=0.026..0.080rows=19 loops=1) 
               Index Cond: (i.uid = "outer".uid)
               Filter: ((status = 'd'::bpchar) OR (status = 'v'::bpchar))
 Total runtime: 0.315 ms
(10 rows)


    |\__/|
    ( oo )    Kari Lavikka - tuner@bdb.fi - (050) 380 3808
__ooO(  )Ooo_______ _____ ___ _ _  _   _    _      _                  _
      ""