To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	pgsql-performance@postgresql.org
Subject: Re: order by slowing down a query by 80 times 
In-reply-to: <AANLkTiklzIhL9g-Jek-o1QoSLM429ArQoWzHeg16yNez@mail.gmail.com> 
References: <AANLkTim6WXLK8iJuwZbzJjCuPr9LPau1OX7vxwYDFdkN@mail.gmail.com>
	<4C2862C90200002500032BCD@gw.wicourts.gov>
	<26902.1277734649@sss.pgh.pa.us>
	<AANLkTiklzIhL9g-Jek-o1QoSLM429ArQoWzHeg16yNez@mail.gmail.com>
Comments: In-reply-to Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
	message dated "Tue, 29 Jun 2010 01:26:50 +0530"
Date: Mon, 28 Jun 2010 16:18:58 -0400
Message-ID: <3371.1277756338@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Rajesh Kumar Mallah <mallah.rajesh@gmail.com> writes:
> co_name_vec is actually the auxiliary tsvector column that is mantained via
> a
> an update trigger. and the index that you suggested is there .

Well, in that case it's just a costing/statistics issue.  The planner is
probably estimating there are more tsvector matches than there really
are, which causes it to think the in-order indexscan will terminate
earlier than it really will, so it goes for that instead of a full scan
and sort.  If this is 8.4 then increasing the statistics target for the
co_name_vec column should help that.  In previous versions I'm not sure
how much you can do about it other than raise random_page_cost, which is
likely to be a net loss overall.

			regards, tom lane