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

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
imagei, 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.079rows=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@bdb.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
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_______ _____ ___ _ _  _   _    _      _                  _
>       ""
>
> ---------------------------(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
>