Re: Forcing the right queryplan

From: Henk van Lingen <H(dot)G(dot)K(dot)vanLingen(at)uu(dot)nl>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Forcing the right queryplan
Date: 2010-09-07 14:48:50
Message-ID: 20100907144850.GA9665@uu.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-novice

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/

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Craig Ringer 2010-09-07 15:47:50 Re: Configure default for sorting of null-values?
Previous Message Tom Lane 2010-09-07 14:32:55 Re: Configure default for sorting of null-values?

Browse pgsql-novice by date

  From Date Subject
Next Message Turner, John J 2010-09-07 15:43:04 Re: Fwd: StackBuilder can't fetch application list?
Previous Message Sachin Srivastava 2010-09-07 12:27:31 Re: Fwd: StackBuilder can't fetch application list?