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)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)
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)