query optimizer questions

Поиск
Список
Период
Сортировка
От Robert Berger
Тема query optimizer questions
Дата
Msg-id 3B409842.DDD62061@vtiscan.com
обсуждение исходный текст
Ответы Re: query optimizer questions  (Peter Eisentraut <peter_e@gmx.net>)
Re: query optimizer questions  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Why do the following three queries use different scan types under 7.1.2?
Under
6.5.3 they all used index (and my system ran MUCH faster, since I often
use the trailing %)

I've done a vacuum analyze.
I installed 7.1.2 on RedHat 6.2 using the RPMs.


anderson=# explain select value from s_n_grantor where value='foobar';
NOTICE:  QUERY PLAN:

Index Scan using s_n_grantor_key on s_n_grantor  (cost=0.00..823.58
rows=247 width=12)

EXPLAIN
anderson=# explain select value from s_n_grantor where value like
'foobar';
NOTICE:  QUERY PLAN:

Seq Scan on s_n_grantor  (cost=0.00..8178.46 rows=247 width=12)

anderson=# explain select value from s_n_grantor where value like
'foobar%';
NOTICE:  QUERY PLAN:

Seq Scan on s_n_grantor  (cost=0.00..8178.46 rows=1 width=12)


EXPLAIN
anderson=# \d s_n_grantor_key
      Index "s_n_grantor_key"
 Attribute |         Type
-----------+-----------------------
 value     | character varying(80)
btree

anderson=# \d s_n_grantor
             Table "s_n_grantor"
 Attribute |         Type          | Modifier
-----------+-----------------------+----------
 doc       | character varying(16) | not null
 entry     | smallint              | not null
 value     | character varying(80) |
Indices: s_n_grantor_key,
         s_n_grantor_pkey

В списке pgsql-general по дате отправления:

Предыдущее
От: "dennis@zserve.com"
Дата:
Сообщение: Multiple Indexes
Следующее
От: Peter Eisentraut
Дата:
Сообщение: Re: Multiple Indexes