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
