Re: Weird indices

Stephan Szabo wrote:
>
> 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.
>
Err, why?  There is an index, isn't there?  Shouldn't the index allow
postgres to quickly find the %2 of rows that would match?


> 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)

--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com