Re: SQL Query Performance - what gives? - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: SQL Query Performance - what gives?
Date
Msg-id 4A8ADDEA0200002500029CA3@gw.wicourts.gov
Whole thread Raw
In response to Re: SQL Query Performance - what gives?  (Karl Denninger <karl@denninger.net>)
Responses Re: SQL Query Performance - what gives?  (Karl Denninger <karl@denninger.net>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Scott Marlowe
Date:
Subject: Re: number of rows estimation for bit-AND operation
Next
From: Scott Marlowe
Date:
Subject: Re: number of rows estimation for bit-AND operation