Re: Unique index and estimated rows.

Uh oh,

function indexes seem to be a bit crippled. I created a unique index
without the upper() function and number of estimated rows is now just
right.

"users_nick" unique, btree (nick)

And the plan:

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 nick = 'FireGirl-' AND i.status IN ('d', 'v') AND u.status = 'a' ORDER BY status, stamp DESC;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=154.10..154.12 rows=7 width=63) (actual time=0.227..0.237 rows=19 loops=1)
Sort Key: i.status, i.stamp
-> Nested Loop (cost=0.00..154.00 rows=7 width=63) (actual time=0.075..0.176 rows=19 loops=1)
-> Index Scan using users_nick on users u (cost=0.00..6.01 rows=1 width=14) (actual time=0.041..0.043 rows=1 loops=1)
Index Cond: ((nick)::text = 'FireGirl-'::text)
Filter: (status = 'a'::bpchar)
-> Index Scan using image_uid_status on image i (cost=0.00..147.73 rows=21 width=53) (actual time=0.026..0.079 rows=19 loops=1)
Index Cond: (i.uid = "outer".uid)
Filter: ((status = 'd'::bpchar) OR (status = 'v'::bpchar))
Total runtime: 0.303 ms
(10 rows)

I think that creating an uppercase column for name and unique index for
could be a workaround for this.

Another problem is that function indexes don't seem to care about
statistics target settings.

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

On Fri, 30 Jan 2004, Kari Lavikka wrote:

>
> 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_______ _____ ___ _ _ _ _ _ _ _
> ""
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>