On 10/09/12 16:24, bill_mar...@freenet.de<mailto:bill_mar...@freenet.de> wrote:
Hi All
I´ve ft_simple_core_content_content_idx
ON core_content
USING gin
(to_tsvector('simple'::regconfig, content) );
If I´m seaching for a word which is NOT in the column content the query plan
and the execution time differs with the given limit.
If I choose 3927 or any higher number the query execution took only few
milliseconds.
core_content content where
to_tsvector('simple', content.content) @@ tsquery(plainto_tsquery('simple',
'asdasdadas') :: varchar || ':*')=true
Limit 3927
"Limit (cost=0.00..19302.23 rows=3926 width=621) (actual
time=52147.149..52147.149 rows=0 loops=1)"
" -> Seq Scan on core_content content (cost=0.00..98384.34 rows=20011
width=621) (actual time=52147.147..52147.147 rows=0 loops=1)"
" Filter: (to_tsvector('simple'::regconfig, content) @@
'''asdasdadas'':*'::tsquery)"
"Total runtime: 52147.173 ms"
Is there any posibility to improve the performance even if the limit is only
10? Is it possible to determine that the query optimizer takes only the fast
bitmap heap scan instead of the slow seq scan?
The big hammer is: "set enable_seqscan = off", but if you tell which PG version
you're on there may be something to do. I suggest you'd start by bumping the
statistics target for the column to 10000 and run analyze to see what that
changes.
--
Jesper
Hi,
my email client delete a lot of the content of the original thread message.
Here is the full content:
Hi All
I´ve created following table which contains one million records.
CREATE TABLE core_content
(
id bigint NOT NULL,
content text NOT NULL,
short_content text,
CONSTRAINT core_content_pkey PRIMARY KEY (id )
)
CREATE INDEX ft_simple_core_content_content_idx
ON core_content
USING gin
(to_tsvector('simple'::regconfig, content) );
If I´m seaching for a word which is not in the column content the query plan
and the execution time differs with the given limit.
If I choose 3927 or any higher number the query execution took only few
milliseconds.
select * from core_content content where
to_tsvector('simple', content.content) @@ tsquery(plainto_tsquery('simple',
'asdasdadas') :: varchar || ':*')=true
Limit 3927
"Limit (cost=10091.09..19305.68 rows=3927 width=621) (actual time=0.255..0.255
rows=0 loops=1)"
" -> Bitmap Heap Scan on core_content content (cost=10091.09..57046.32
rows=20011 width=621) (actual time=0.254..0.254 rows=0 loops=1)"
" Recheck Cond: (to_tsvector('simple'::regconfig, content) @@
'''asdasdadas'':*'::tsquery)"
" -> Bitmap Index Scan on ft_simple_core_content_content_idx
(cost=0.00..10086.09 rows=20011 width=0) (actual time=0.251..0.251 rows=0
loops=1)"
" Index Cond: (to_tsvector('simple'::regconfig, content) @@
'''asdasdadas'':*'::tsquery)"
"Total runtime: 0.277 ms"
If I choose 3926 or any lower number (e.g. 10) the query execution took more
than fifty seconds.
select * from core_content content where
to_tsvector('simple', content.content) @@ tsquery(plainto_tsquery('simple',
'asdasdadas') :: varchar || ':*')=true
Limit 3927
"Limit (cost=0.00..19302.23 rows=3926 width=621) (actual
time=52147.149..52147.149 rows=0 loops=1)"
" -> Seq Scan on core_content content (cost=0.00..98384.34 rows=20011
width=621) (actual time=52147.147..52147.147 rows=0 loops=1)"
" Filter: (to_tsvector('simple'::regconfig, content) @@
'''asdasdadas'':*'::tsquery)"
"Total runtime: 52147.173 ms"
Is there any posibility to tune up the performance even if the limit is only
10? Is it possible to determine that the query optimizer takes
only the fast bitmap heap scan instead of the slow seq scan?
I use PostgreSQL 9.1.5.; Intel i5-2400 @ 3.1 GHz, 16GB; Windows 7 64 Bit
Regards,
Bill Martin
