From: Jean-Max Reymond <jmreymond@free.fr>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: not using my GIN index in JOIN expression
Date: Thu, 27 Feb 2014 15:06:56 +0100
Message-ID: <530F4680.7000409@free.fr> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jmreymond@free.fr
Subject: Re: not using my GIN index in JOIN expression
In-Reply-To: <530F4680.7000409@free.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox