I have a very simple table:
CREATE TABLE content
(
id serial NOT NULL,
text text,
fullfilename text,
CONSTRAINT "PK_ID" PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE content
OWNER TO postgres;
CREATE INDEX content_idx
ON content
USING gin
(to_tsvector('danish'::regconfig, text));
Where text
is a rather long aggregated text to perform full text searching.
When I run the following query it resorts to a seq scan:
EXPLAIN ANALYZE SELECT id
FROM content
WHERE to_tsvector('danish', text) @@ to_tsquery('danish','felter')
"Seq Scan on content (cost=0.00..164.57 rows=249 width=4) (actual time=41.147..7235.823 rows=289 loops=1)"
" Filter: (to_tsvector('danish'::regconfig, text) @@ '''felt'''::tsquery)"
" Rows Removed by Filter: 1149"
"Planning time: 0.366 ms"
"Execution time: 7235.914 ms"
That is absolutely not okay. But when disabling seq scans with SET enable_seqscan TO 'off'
I get the following result:
"Bitmap Heap Scan on content (cost=17.94..168.53 rows=249 width=4) (actual time=0.145..0.323 rows=289 loops=1)"
" Recheck Cond: (to_tsvector('danish'::regconfig, text) @@ '''felt'''::tsquery)"
" Heap Blocks: exact=70"
" -> Bitmap Index Scan on content_idx (cost=0.00..17.87 rows=249 width=0) (actual time=0.121..0.121 rows=289 loops=1)"
" Index Cond: (to_tsvector('danish'::regconfig, text) @@ '''felt'''::tsquery)"
"Planning time: 0.373 ms"
"Execution time: 0.383 ms"
What exactly is going on, and what parameters needs tuning for it to run better. I don't like the idea of removing a tool from the query planners toolbox. As it stands, the setup is running a stock PostgreSQL 9.4.4
analyze content
? – a_horse_with_no_name Sep 11 '15 at 13:40ANALYZE
is not going to help much, since the selectivity estimate is pretty much on target (rows=249 ... rows=289
). Cost settings are obviously not configured. As to the question: how many rows? Size of table or mean size of row? "rather long" is a bit fuzzy. And what do you really need as result from the query? Just theid
or the whole row? – Erwin Brandstetter Sep 12 '15 at 20:30id
as it identifies the document. I realize that the above table have the id as being the primary key. That is not how the final table will look. – hvidgaard Sep 14 '15 at 7:11