From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Jean-Christophe Boggio <cat(at)thefreecat(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Weird indices |
Date: | 2001-02-17 00:50:32 |
Message-ID: | Pine.BSF.4.21.0102161649250.14460-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-02-17 02:07:24 | Re: order of clauses |
Previous Message | Jean-Christophe Boggio | 2001-02-17 00:35:54 | Weird indices |