Received: from magus.postgresql.org ([87.238.57.229])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <jesper@krogh.cc>) id 1TB8ZO-0003zg-Mn
	for pgsql-performance@postgresql.org; Mon, 10 Sep 2012 18:18:50 +0000
Received: from 2605ds1-ynoe.3.fullrate.dk ([90.185.142.83]
 helo=shrek.krogh.cc)
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <jesper@krogh.cc>) id 1TB8ZM-0007OD-OV
	for pgsql-performance@postgresql.org; Mon, 10 Sep 2012 18:18:50 +0000
Received: from localhost (localhost [127.0.0.1])
	by shrek.krogh.cc (Postfix) with ESMTP id D5145C44002
	for <pgsql-performance@postgresql.org>;
	Mon, 10 Sep 2012 20:18:46 +0200 (CEST)
X-Virus-Scanned: Debian amavisd-new at shrek.krogh.cc
Received: from shrek.krogh.cc ([127.0.0.1])
	by localhost (shrek.krogh.cc [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id oWqg0fofHijh for <pgsql-performance@postgresql.org>;
	Mon, 10 Sep 2012 20:18:38 +0200 (CEST)
Received: from [90.185.142.91] (2605ds1-ynoe.4.fullrate.dk [90.185.142.91])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	(Authenticated sender: jesper@krogh.cc)
	by shrek.krogh.cc (Postfix) with ESMTPSA id 92FDEC44001
	for <pgsql-performance@postgresql.org>;
	Mon, 10 Sep 2012 20:18:38 +0200 (CEST)
Message-ID: <504E2EFE.7060000@krogh.cc>
Date: Mon, 10 Sep 2012 20:18:38 +0200
From: Jesper Krogh <jesper@krogh.cc>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:12.0) Gecko/20120430 Thunderbird/12.0.1
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Planner selects different execution plans depending
 on limit
References: <ccf45defbe1241e741631192a29f1c09@email.freenet.de>
In-Reply-To: <ccf45defbe1241e741631192a29f1c09@email.freenet.de>
Content-Type: multipart/alternative;
	boundary="------------000105060702040102030300"
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201209/34
X-Sequence-Number: 47780

This is a multi-part message in MIME format.
--------------000105060702040102030300
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable

On 10/09/12 16:24, bill_martin@freenet.de wrote:
>
> Hi All
>
> I=C2=B4ve ft_simple_core_content_content_idx
>   ON core_content
>   USING gin
>   (to_tsvector('simple'::regconfig, content) );
>
>
> If I=C2=B4m seaching for a word which is NOT in the column content the=20
> query plan and the execution time differs with the given limit.
> If I choose 3927 or any higher number the query execution took only=20
> few milliseconds.
>
> core_content content where
> to_tsvector('simple', content.content) @@=20
> tsquery(plainto_tsquery('simple', 'asdasdadas') :: varchar || ':*')=3Dt=
rue
> Limit 3927
>
> "Limit  (cost=3D0.00..19302.23 rows=3D3926 width=3D621) (actual=20
> time=3D52147.149..52147.149 rows=3D0 loops=3D1)"
> "  ->  Seq Scan on core_content content  (cost=3D0.00..98384.34=20
> rows=3D20011 width=3D621) (actual time=3D52147.147..52147.147 rows=3D0 =
loops=3D1)"
> "        Filter: (to_tsvector('simple'::regconfig, content) @@=20
> '''asdasdadas'':*'::tsquery)"
> "Total runtime: 52147.173 ms"
>
> Is there any posibility to improve the performance even if the limit=20
> is only 10? Is it possible to determine that the query optimizer takes=20
> only the fast bitmap heap scan instead of the slow seq scan?
>

The big hammer is: "set enable_seqscan =3D off", but if you tell which PG=
=20
version you're on there may be something to do. I suggest you'd start by=20
bumping the statistics target for the column to 10000 and run analyze to=20
see what that changes.

--=20
Jesper

--------------000105060702040102030300
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html>
  <head>
    <meta content=3D"text/html; charset=3DUTF-8" http-equiv=3D"Content-Ty=
pe">
  </head>
  <body bgcolor=3D"#FFFFFF" text=3D"#000000">
    On 10/09/12 16:24, <a class=3D"moz-txt-link-abbreviated" href=3D"mail=
to:bill_martin@freenet.de">bill_martin@freenet.de</a> wrote:
    <blockquote
      cite=3D"mid:ccf45defbe1241e741631192a29f1c09@email.freenet.de"
      type=3D"cite">
      <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DU=
TF-8">
      <title></title>
      <div
        style=3D"font-family:arial,helvetica,sans-serif;font-size:10pt;">
        <p style=3D"margin:0px;padding:0px;">Hi All<br>
          <br>
          I=C2=B4ve ft_simple_core_content_content_idx<br>
          =C2=A0 ON core_content<br>
          =C2=A0 USING gin<br>
          =C2=A0 (to_tsvector('simple'::regconfig, content) );<br>
          <br>
          =C2=A0 <br>
          If I=C2=B4m seaching for a word which is NOT in the column cont=
ent
          the query plan and the execution time differs with the given
          limit.<br>
          If I choose 3927 or any higher number the query execution took
          only few milliseconds. <br>
          =C2=A0 <br>
          core_content content where<br>
          to_tsvector('simple', content.content) @@
          tsquery(plainto_tsquery('simple', 'asdasdadas') :: varchar ||
          ':*')=3Dtrue<br>
          Limit 3927<br>
          <br>
          "Limit=C2=A0 (cost=3D0.00..19302.23 rows=3D3926 width=3D621) (a=
ctual
          time=3D52147.149..52147.149 rows=3D0 loops=3D1)"<br>
          "=C2=A0 -&gt;=C2=A0 Seq Scan on core_content content=C2=A0
          (cost=3D0.00..98384.34 rows=3D20011 width=3D621) (actual
          time=3D52147.147..52147.147 rows=3D0 loops=3D1)"<br>
          "=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Filter: (to_tsvecto=
r('simple'::regconfig, content) @@
          '''asdasdadas'':*'::tsquery)"<br>
          "Total runtime: 52147.173 ms"<br>
          <br>
          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?<br>
        </p>
      </div>
    </blockquote>
    <br>
    The big hammer is: "set enable_seqscan =3D off", but if you tell whic=
h
    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. <br>
    <br>
    -- <br>
    Jesper<br>
  </body>
</html>

--------------000105060702040102030300--