From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: order by slowing down a query by 80 times
Date: Mon, 28 Jun 2010 16:18:58 -0400
Message-ID: <3371.1277756338@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, mallah.rajesh@gmail.com, Kevin.Grittner@wicourts.gov
Subject: Re: order by slowing down a query by 80 times
In-Reply-To: <3371.1277756338@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox