63273 loke success=# \d indexedsearchword Table "public.indexedsearchword" Column | Type | Modifiers -----------------+-----------------------------+----------- id | bigint | not null objectid | bigint | not null name | character varying(255) | not null modified | timestamp without time zone | type | integer | not null customerunit_id | bigint | not null Indexes: "indexedsearchword_pkey" PRIMARY KEY, btree (id) Foreign-key constraints: "fk_indexedsearchword_customerunit_id" FOREIGN KEY (customerunit_id) REFERENCES customerunit(id) success=# \d indexedsearchword_searchword Table "public.indexedsearchword_searchword" Column | Type | Modifiers ----------------------+--------+----------- indexedsearchword_id | bigint | not null words_id | bigint | not null Indexes: "indexedsearchword_searchword_pkey" PRIMARY KEY, btree (indexedsearchword_id, words_id) "indexedsearchword_searchword_id" btree (indexedsearchword_id) "indexedsearchword_words" btree (words_id) Foreign-key constraints: "fk_indexedsearchword_searchword_indexedsearchword_id" FOREIGN KEY (indexedsearchword_id) REFERENCES indexedsearchword(id) "fk_indexedsearchword_searchword_words_id" FOREIGN KEY (words_id) REFERENCES searchword(id) success=# select count(*) from indexedsearchword; count ------- 88091 (1 row) success=# select count(*) from indexedsearchword_searchword; count --------- 2034893 (1 row) success=# explain analyse SELECT DISTINCT t0.WORD FROM SEARCHWORD t0, INDEXEDSEARCHWORD_SEARCHWORD t2, INDEXEDSEARCHWORD t1 WHERE (((t1.CUSTOMERUNIT_ID = 102) AND (t0.WORD LIKE 'n%')) AND ((t2.IndexedSearchWord_ID = t1.ID) AND (t0.ID = t2.words_ID))) ORDER BY t0.WORD ASC; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- Unique (cost=359090.17..366354.66 rows=268050 width=18) (actual time=38196.071..39500.317 rows=196134 loops=1) -> Sort (cost=359090.17..362722.42 rows=1452898 width=18) (actual time=38196.068..39157.070 rows=798087 loops=1) Sort Key: t0.word -> Hash Join (cost=16700.47..150784.42 rows=1452898 width=18) (actual time=1249.268..32601.095 rows=798087 loops=1) Hash Cond: (t2.indexedsearchword_id = t1.id) -> Hash Join (cost=12526.41..98581.37 rows=1477029 width=26) (actual time=859.638..27266.243 rows=808035 loops=1) Hash Cond: (t2.words_id = t0.id) -> Seq Scan on indexedsearchword_searchword t2 (cost=0.00..32323.81 rows=2035181 width=16) (actual time=15.732..1610.128 rows=2034893 loops=1) -> Hash (cost=7342.79..7342.79 rows=268050 width=26) (actual time=843.813..843.813 rows=268991 loops=1) -> Seq Scan on searchword t0 (cost=0.00..7342.79 rows=268050 width=26) (actual time=0.053..638.011 rows=268991 loops=1) Filter: ((word)::text ~~ 'n%'::text) -> Hash (cost=2707.54..2707.54 rows=86762 width=8) (actual time=164.758..164.758 rows=86406 loops=1) -> Seq Scan on indexedsearchword t1 (cost=0.00..2707.54 rows=86762 width=8) (actual time=17.834..111.943 rows=86406 loops=1) Filter: (customerunit_id = 102) Total runtime: 42203.839 ms (15 rows) 3424185952 None None TRUE 2 loke success=# create index indexedsearchword_cu_index on indexedsearchword (customerunit_id); CREATE INDEX success=# explain analyse SELECT DISTINCT t0.WORD FROM SEARCHWORD t0, INDEXEDSEARCHWORD_SEARCHWORD t2, INDEXEDSEARCHWORD t1 WHERE (((t1.CUSTOMERUNIT_ID = 102) AND (t0.WORD LIKE 'n%')) AND ((t2.IndexedSearchWord_ID = t1.ID) AND (t0.ID = t2.words_ID))) ORDER BY t0.WORD ASC; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------- Unique (cost=359020.19..366283.69 rows=267894 width=18) (actual time=11784.289..13004.879 rows=196134 loops=1) -> Sort (cost=359020.19..362651.94 rows=1452700 width=18) (actual time=11784.287..12685.915 rows=798087 loops=1) Sort Key: t0.word -> Hash Join (cost=16687.05..150750.13 rows=1452700 width=18) (actual time=986.173..7296.785 rows=798087 loops=1) Hash Cond: (t2.indexedsearchword_id = t1.id) -> Hash Join (cost=12519.76..98560.63 rows=1476824 width=26) (actual time=470.223..5374.936 rows=808035 loops=1) Hash Cond: (t2.words_id = t0.id) -> Seq Scan on indexedsearchword_searchword t2 (cost=0.00..32318.93 rows=2034893 width=16) (actual time=25.893..835.154 rows=2034893 loops=1) -> Hash (cost=7339.09..7339.09 rows=267894 width=26) (actual time=444.216..444.216 rows=268991 loops=1) -> Seq Scan on searchword t0 (cost=0.00..7339.09 rows=267894 width=26) (actual time=0.080..237.640 rows=268991 loops=1) Filter: ((word)::text ~~ 'n%'::text) -> Hash (cost=2703.14..2703.14 rows=86652 width=8) (actual time=291.660..291.660 rows=86406 loops=1) -> Seq Scan on indexedsearchword t1 (cost=0.00..2703.14 rows=86652 width=8) (actual time=0.790..73.862 rows=86406 loops=1) Filter: (customerunit_id = 102) Total runtime: 13692.048 ms (15 rows) success=# show lc_ctype; lc_ctype ---------- C (1 row) 3424186801 None TRUE 1 loke success=# \d searchword Table "public.searchword" Column | Type | Modifiers --------+------------------------+----------- id | bigint | not null word | character varying(255) | not null Indexes: "searchword_pkey" PRIMARY KEY, btree (id) "searchword_word_key" UNIQUE, btree (word) 3424186540 None TRUE