Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 69DDF6322F1;
	Tue, 18 Aug 2009 18:59:37 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 86702-03; Tue, 18 Aug 2009 21:59:21 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by mail.postgresql.org (Postfix) with ESMTP id D63E163370C;
	Tue, 18 Aug 2009 18:59:25 -0300 (ADT)
Received: from Courts-MTA by gw.wicourts.gov
	with Novell_GroupWise; Tue, 18 Aug 2009 16:59:24 -0500
Message-Id: <4A8ADDEA0200002500029CA3@gw.wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 8.0.0 
Date: Tue, 18 Aug 2009 16:59:22 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Karl Denninger" <karl@denninger.net>
Cc: <pgsql-performance@postgresql.org>,<pgsql-sql@postgresql.org>
Subject: Re: SQL Query Performance - what gives?
References: <4A8B04C7.6060702@denninger.net>
	<4A8AC75C0200002500029C8A@gw.wicourts.gov>
	<4A8B1606.2040209@denninger.net>
In-Reply-To: <4A8B1606.2040209@denninger.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=AWL=0.000,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200908/197
X-Sequence-Number: 35229

Karl Denninger <karl@denninger.net> wrote:
>                ->  Index Scan using forum_name on forum 
> (cost=0.00..250.63 rows=1 width=271) (actual time=0.013..0.408
> rows=63 loops=1)
>                      Filter: (((contrib IS NULL) OR (contrib = '
> '::text) OR (contrib ~~ '%b%'::text)) AND ((permission & 127) =
> permission))
 
The biggest issue, as far as I can see, is that it thinks that the
selection criteria on forum will limit to one row, while it really
matches 63 rows.
 
You might be able to coerce it into a faster plan with something like
this (untested):
 
select *
  from (select * from post
          where invisible <> 1
            and to_tsvector('english', message)
             @@ to_tsquery('violence')
       ) p,
  forum
  where forum.name = p.forum
    and (permission & '127') = permission
    and (contrib is null or contrib = ' ' or contrib like '%b%')
  order by modified desc
  limit 100
;
 
-Kevin