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
Re: Questions on 7.2.1 query plan choices |
Список | 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 по дате отправления: