not using my GIN index in JOIN expression
От | Jean-Max Reymond |
---|---|
Тема | not using my GIN index in JOIN expression |
Дата | |
Msg-id | 530F4680.7000409@free.fr обсуждение исходный текст |
Ответы |
Re: not using my GIN index in JOIN expression
|
Список | pgsql-performance |
I am running the last version of PostgreSQL 9.3.3 I have two tables detm and corm and a lot of datas in the column cormdata of corm table (1.4 GB). I have a GIN index on cormdata: CREATE INDEX ix_corm_fulltext_cormdata ON corm USING gin (to_tsvector('french'::regconfig, cormdata)) WHERE cormishtml IS FALSE AND length(cormdata) < 20000; select distinct b.detmmailid from corm b where (to_tsvector('french',b.cormdata) @@ to_tsquery('mauritanie') and b.cormishtml is false and length(b.cormdata) < 20000) is very fast and use the GIN index. "HashAggregate (cost=2027.72..2031.00 rows=328 width=52)" " -> Bitmap Heap Scan on corm b (cost=24.25..2026.35 rows=548 width=52)" " Recheck Cond: ((to_tsvector('french'::regconfig, cormdata) @@ to_tsquery('mauritanie'::text)) AND (cormishtml IS FALSE) AND (length(cormdata) < 20000))" " -> Bitmap Index Scan on ix_corm_fulltext_cormdata (cost=0.00..24.11 rows=548 width=0)" " Index Cond: (to_tsvector('french'::regconfig, cormdata) @@ to_tsquery('mauritanie'::text))" With a join an another table detm, GIN index is not used explain select distinct a.detmmailid from detm a JOIN corm b on a.detmmailid = b.detmmailid where ((to_tsvector('french',b.cormdata) @@ to_tsquery('mauritanie') and b.cormishtml is false and length(b.cormdata) < 20000) OR ( detmobjet ~* 'mauritanie' )) "HashAggregate (cost=172418.27..172423.98 rows=571 width=52)" " -> Hash Join (cost=28514.92..172416.85 rows=571 width=52)" " Hash Cond: (b.detmmailid = a.detmmailid)" " Join Filter: (((to_tsvector('french'::regconfig, b.cormdata) @@ to_tsquery('mauritanie'::text)) AND (b.cormishtml IS FALSE) AND (length(b.cormdata) < 20000)) OR (a.detmobjet ~* 'mauritanie'::text))" " -> Seq Scan on corm b (cost=0.00..44755.07 rows=449507 width=689)" " -> Hash (cost=19322.74..19322.74 rows=338574 width=94)" " -> Seq Scan on detm a (cost=0.00..19322.74 rows=338574 width=94)" If I remove OR ( detmobjet ~* 'mauritanie' ) in the select, the GIN index is used explain select distinct a.detmmailid from detm a JOIN corm b on a.detmmailid = b.detmmailid where ((to_tsvector('french',b.cormdata) @@ to_tsquery('mauritanie') and b.cormishtml is false and length(b.cormdata) < 20000)) "HashAggregate (cost=4295.69..4301.17 rows=548 width=52)" " -> Nested Loop (cost=24.67..4294.32 rows=548 width=52)" " -> Bitmap Heap Scan on corm b (cost=24.25..2026.35 rows=548 width=52)" " Recheck Cond: ((to_tsvector('french'::regconfig, cormdata) @@ to_tsquery('mauritanie'::text)) AND (cormishtml IS FALSE) AND (length(cormdata) < 20000))" " -> Bitmap Index Scan on ix_corm_fulltext_cormdata (cost=0.00..24.11 rows=548 width=0)" " Index Cond: (to_tsvector('french'::regconfig, cormdata) @@ to_tsquery('mauritanie'::text))" " -> Index Only Scan using pkey_detm on detm a (cost=0.42..4.13 rows=1 width=52)" " Index Cond: (detmmailid = b.detmmailid)" How can i force the use of the GIN index ? thanks for your tips, -- Jean-Max Reymond CKR Solutions Open Source http://www.ckr-solutions.com
В списке pgsql-performance по дате отправления: