Questions on 7.2.1 query plan choices

Поиск
Список
Период
Сортировка
От Ed Loehr
Тема Questions on 7.2.1 query plan choices
Дата
Msg-id 3CBDF3E5.6050808@bluepolka.net
обсуждение исходный текст
Ответы Re: Questions on 7.2.1 query plan choices  (Curt Sampson <cjs@cynic.net>)
Re: Questions on 7.2.1 query plan choices  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
This seems pretty basic...I'd appreciate someone showing me the error of my
ways...Questions below this schema...

$ psql -c "\d freetext"
                            Table "freetext"
       Column      |         Type          |         Modifiers
------------------+-----------------------+----------------------------
  value            | text                  |
  key              | integer               | not null
  isindexed        | boolean               | not null default 'f'::bool
  tobeindexed      | boolean               | default 'f'::bool
Indexes: indexed_idx
Unique keys: freetext_pkey

$ psql -c "\d indexed_idx"
   Index "indexed_idx"
    Column    |  Type
-------------+---------
  tobeindexed | boolean
  isindexed   | boolean
btree

$ psql -c "\d freetext_pkey"
Index "freetext_pkey"
  Column |  Type
--------+---------
  key    | integer
unique btree


1)  There are over 700,000 rows in the table below, but only about 1,300
matching the where clause.  How can I (and should I) get the planner to
choose to traverse indexed_idx instead of a sequential scan?  The following
is immediately after calling 'analyze'...

$ psql -c "explain select key, value from freetext where tobeindexed = 't'
and isindexed = 'f'
NOTICE:  QUERY PLAN:

Seq Scan on freetext  (cost=0.00..102114.21 rows=296161 width=1138)

$ psql -c "select count(key) from freetext"
  count
--------
  728868
(1 row)

$ psql -c "select count(key) from freetext where tobeindexed = 't' and
isindexed = 'f'"
  count
-------
   1319
(1 row)


2)  Why does the planner choose to first scan freetext_pkey when choosing
indexed_idx would narrow the 700K rows down to 1300 in the query below?  As
it is, it is apparently doing the equivalent of a backward seqscan of 700K
rows right of the bat.

$ psql -c "explain select key, value from freetext where tobeindexed = 't'
and isindexed = 'f' order by key desc limit 25;
NOTICE:  QUERY PLAN:

Limit  (cost=0.00..267.87 rows=25 width=1144)
   ->  Index Scan Backward using freetext_pkey on freetext
(cost=0.00..3165306.12 rows=295414 width=1144)


-Ed


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

Предыдущее
От: Bruce Momjian
Дата:
Сообщение: Re: select users in a group?
Следующее
От: Jeff Post
Дата:
Сообщение: PERL DBI and Novell Netware