This doesn't look too healthy:
CREATE TABLE page (
page_id SERIAL NOT NULL,
page_namespace INT NOT NULL,
page_title TEXT NOT NULL,
page_restrictions TEXT DEFAULT NULL,
page_is_redirect SMALLINT DEFAULT 0 NOT NULL,
page_is_new SMALLINT DEFAULT 0 NOT NULL,
page_random FLOAT NOT NULL,
page_touched TIMESTAMPTZ NOT NULL,
page_links_updated TIMESTAMPTZ DEFAULT NULL,
page_latest INT NOT NULL,
page_len INT NOT NULL,
page_content_model TEXT DEFAULT NULL,
page_lang TEXT DEFAULT NULL,
PRIMARY KEY(page_id)
);
INSERT INTO page (page_namespace, page_title, page_is_redirect,
page_is_new, page_random, page_touched, page_latest, page_len) select
case when random() > 0.5 then 1 else 0 end, random()::text, 0, 0,
42.0, now(), 0, 42 from generate_series(1, 170000);
CREATE INDEX page_main_title ON page USING btree(page_title text_pattern_ops)
WHERE (page_namespace = 0);
postgres=# select count(*) from page;
count
--------
170000
(1 row)
postgres=# select count(*) from page where page_namespace = 0;
count
-------
84934
(1 row)
postgres=# explain select count(*) from page where page_namespace = 0;
QUERY PLAN
----------------------------------------------------------------------------------------------
Aggregate (cost=3179.75..3179.76 rows=1 width=8)
-> Index Only Scan using page_main_title on page
(cost=0.42..2967.85 rows=84762 width=0)
(2 rows)
