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)
Anyone has an idea ?
Thanks !
--
Jean-Christophe Boggio
cat@thefreecat.org
Independant Consultant and Developer
Delphi, Linux, Perl, PostgreSQL