Re: Planner selects different execution plans depending on limit

[Bill Martin <bill.martin@xxxxxxxxxxxxx>]

On 10/09/12 16:24, bill_martin@xxxxxxxxxx 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