Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id CA1FF1E16586 for ; Tue, 10 Jan 2012 13:04:51 -0400 (AST) Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Rkf7x-0003tR-L3 for pgsql-performance@postgresql.org; Tue, 10 Jan 2012 17:04:51 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id q0AH4Zrg025379; Tue, 10 Jan 2012 12:04:35 -0500 (EST) To: darklow cc: 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 In-reply-to: References: Comments: In-reply-to darklow message dated "Tue, 10 Jan 2012 14:30:41 +0200" Date: Tue, 10 Jan 2012 12:04:34 -0500 Message-ID: <25378.1326215074@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201201/31 X-Sequence-Number: 46044 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. regards, tom lane