From: Jesper Krogh <jesper@krogh.cc>
To: pgsql-performance@postgresql.org
Subject: Re: Planner selects different execution plans depending on limit
Date: Mon, 10 Sep 2012 20:18:38 +0200
Message-ID: <504E2EFE.7060000@krogh.cc> (raw)
In-Reply-To: <ccf45defbe1241e741631192a29f1c09@email.freenet.de>
References: <ccf45defbe1241e741631192a29f1c09@email.freenet.de>
On 10/09/12 16:24, bill_martin@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
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: jesper@krogh.cc
Subject: Re: Planner selects different execution plans depending on limit
In-Reply-To: <504E2EFE.7060000@krogh.cc>
* 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