Re: Forcing the right queryplan

On Fri, Sep 03, 2010 at 09:20:39AM +0200, Yeb Havinga wrote:
  >
  > If the index is useless anyway, you might consider dropping it.
  > Otherwise, increasing random_page_cost might help in choosing the
  > otherplan, but on the other hand that plan has index scanning too,
  > so I'm not to sure there.
  >
  > If that doesn't help, it would be interesting to see some output
  > of vmstat 1 (or better: iostat -xk 1) to see what is the
  > bottleneck during execution of the first plan. If it is IO bound,
  > you might want to increase RAM or add spindles for increased
  > random io performance. If it is CPU bound, it is probably because
  > of executing the to_tsvector function. In that case it might be
  > interesting to see if changing ts_vectors cost (see ALTER FUNCTION

Hi Yeb,

Thanks for your answer. Dropping the (pkey) index is not an option.
iostat suggest the thing is CPU bound (%iowait remaining 11% but cpu
rizing from 1 to 13 %)
However, I'm reluctant to changing the to_tsvector costs. (besides
not knowing how the find out the current value). The pkey is also
used for queries like this one, which also results in the wrong
queryplan:

syslog=# explain SELECT id, devicereportedtime, facility, priority, fromhost, syslogtag, infounitid, message FROM
systemeventsWHERE (  ( to_tsvector('english', message) @@ to_tsquery ( 'error')) )  AND id <= 26689837 ; 
                                          QUERY PLAN

--------------------------------------------------------------------------------
---------------
 Index Scan using systemevents_pkey on systemevents  (cost=0.00..27302.74 rows=2
174 width=158)
   Index Cond: (id <= 26689837)
   Filter: (to_tsvector('english'::regconfig, message) @@ to_tsquery('error'::te
xt))
(3 rows)

So I'm afraid that not being able to force a plan is a showstopper for
using postgresql with full text search for this project.

Regards,
--
Henk van Lingen, ICT-SC Netwerk & Telefonie,                  (o-      -+
Universiteit Utrecht, Jenalaan 18a, room 0.12                 /\        |
phone: +31-30-2538453                                         v_/_      |
http://henk.vanlingen.net/             http://www.tuxtown.net/netiquette/