Unique index and estimated rows.

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 image i, 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.039 rows=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 image i, 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.038 rows=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.080 rows=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(at)bdb(dot)fi - (050) 380 3808
__ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
""