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 systemevents WHERE ( ( 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/