Message-ID: <530F4680.7000409@free.fr>
Date: Thu, 27 Feb 2014 15:06:56 +0100
From: Jean-Max Reymond <jmreymond@free.fr>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.3.0
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: not using my GIN index in JOIN expression
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance