Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id C795C1E16587 for ; Tue, 10 Jan 2012 17:43:02 -0400 (AST) Received: from 2605ds1-ynoe.0.fullrate.dk ([90.184.12.24] helo=shrek.krogh.cc) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RkjT8-0001Pg-RI for pgsql-performance@postgresql.org; Tue, 10 Jan 2012 21:43:02 +0000 Received: from localhost (localhost [127.0.0.1]) by shrek.krogh.cc (Postfix) with ESMTP id E48A5C4403B; Tue, 10 Jan 2012 22:42:45 +0100 (CET) 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 zI-DdSQZZrwY; Tue, 10 Jan 2012 22:42:41 +0100 (CET) Received: from [90.184.13.151] (2605ds1-ynoe.2.fullrate.dk [90.184.13.151]) (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 C25DB1CDFD8; Tue, 10 Jan 2012 22:42:40 +0100 (CET) Message-ID: <4F0CB0CB.7090101@krogh.cc> Date: Tue, 10 Jan 2012 22:42:35 +0100 From: Jesper Krogh User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.17) Gecko/20110424 Thunderbird/3.1.10 MIME-Version: 1.0 To: Tom Lane CC: darklow , pgsql-performance@postgresql.org Subject: Re: Query planner doesn't use index scan on tsvector GIN index if LIMIT is specifiedQuery planner doesn't use index scan on tsvector GIN index if LIMIT is specified References: <25378.1326215074@sss.pgh.pa.us> In-Reply-To: <25378.1326215074@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201201/34 X-Sequence-Number: 46047 On 2012-01-10 18:04, Tom Lane wrote: > darklow writes: >> But the performance problems starts when i do the same query specifying >> LIMIT. >> *SELECT id FROM cache WHERE tsv @@ to_tsquery('test:*') limit 20;* >> By some reason index is not used. > It apparently thinks there are enough matches that it might as well just > seqscan the table and expect to find some matches at random, in less > time than using the index would take. > > The estimate seems to be off quite a bit, so maybe raising the stats > target for this column would help. The cost of matching ts_match_vq against a toasted column is not calculated correctly. This is completely parallel with http://archives.postgresql.org/pgsql-hackers/2011-11/msg01754.php Try raising the cost for ts_match_vq(tsvector,tsquery) that help a bit, but its hard to get the cost high enough. Raising statistics target helps too.. -- Jesper