Re: Weird indices

Do you have a value that is not null that is very common?
It's estimating that there will be 10113 rows that match
nomsession='xxx' which makes a seq scan a much less bad plan.

On Sat, 17 Feb 2001, Jean-Christophe Boggio wrote:

> 
> Hi,
> 
> I try to optimize our databases and I find a query that's not very
> optimal :
> 
> sitefr=# explain select nomsession from session where nomsession='xxx';
> NOTICE:  QUERY PLAN:
> 
> Seq Scan on session  (cost=0.00..16275.95 rows=10113 width=12)
> 
> EXPLAIN
> 
> 
> Phew! I think there's an index missing but...
> 
> sitefr=# \d session
>                              Table "session"
>  Attribute  |   Type    |                    Modifier
> ------------+-----------+-------------------------------------------------
>  idsession  | integer   | not null default nextval('seq_idsession'::text)
>  nomsession | text      |
>  idmembre   | text      |
>  referer    | text      |
>  ip         | text      |
>  datelog    | timestamp |
> Indices: ix_session_idmembre,
>          ix_session_nomsession,
>          session_idsession_key
> 
> 
> So I look at the index itself :
> 
> sitefr=# \d ix_session_nomsession
> Index "ix_session_nomsession"
>  Attribute  | Type
> ------------+------
>  nomsession | text
> btree
> 
> 
> Did I miss something or 'text' attributes (fields) can't be indexed ?
> That sounds crazy ! (I vacuum analyzed many times)
> 
> Just in case 'nomsession' would not be as dispersed as I would
> think...
> 
> sitefr=# select count(nomsession) from session;
>  count
> --------
>  510069
> (1 row)
> 
> sitefr=# select count(distinct nomsession) from session;
>  count
> --------
>  401094
> (1 row)