From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Karl Denninger <karl@denninger.net>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Query Performance - what gives?
Date: Tue, 18 Aug 2009 16:59:22 -0500
Message-ID: <4A8ADDEA0200002500029CA3@gw.wicourts.gov> (raw)
In-Reply-To: <4A8B1606.2040209@denninger.net>
References: <4A8B04C7.6060702@denninger.net>
<4A8AC75C0200002500029C8A@gw.wicourts.gov>
<4A8B1606.2040209@denninger.net>
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
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-sql@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, karl@denninger.net, pgsql-performance@postgresql.org
Subject: Re: SQL Query Performance - what gives?
In-Reply-To: <4A8ADDEA0200002500029CA3@gw.wicourts.gov>
* 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