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(at)selectacast(dot)net
Increase signal to noise ratio. http://www.targabot.com