Re: Index ot being used - Mailing list pgsql-performance

Tom Lane wrote:
> Madison Kelly <linux@alteeve.com> writes:
>
>>   So the index obiously provides a major performance boost! I just need
>>to figure out how to tell the planner how to use it...
>
>
> Simple division shows that the planner's cost estimate ratio between the
> seqscan and the indexscan (11956.84 vs 83171.78) is off by a factor of
> more than 8 compared to reality (2018.996 vs 1700.459).  Also the cost of
> the sort seems to be drastically underestimated.
>
> I suspect this may be a combination of random_page_cost being too high
> (since your test case, at least, is no doubt fully cached in RAM) and
> cpu_operator_cost being too low.  I'm wondering if text comparisons
> are really slow on your machine --- possibly due to strcoll being
> inefficient in the locale you are using, which you didn't say.  That
> would account for both the seqscan being slower than expected and the
> sort taking a long time.
>
> It'd be interesting to look at the actual runtimes of this seqscan vs
> one that is doing a simple integer comparison over the same number of
> rows (and, preferably, returning about the same number of rows as this).
>
>             regards, tom lane

   This is where I should mention that though 'n00b' might be a little
harsh, I am still somewhat of a beginner (only been using postgres or
programming at all for a little over a year).

   What is, and how do I check, 'strcoll'? Is there a way that I can
clear the psql cache to make the tests more accurate to real-world
situations? For what it's worth, the program is working (I am doing
stress-testing and optimizing now) and the data in this table is actual
data, not a construct.

   As I mentioned to Bruno in my reply to him, I am trying to keep as
many tweaks as I can inside my program. The reason for this is that this
is a backup program that I am trying to aim to more mainstream users or
where a techy would set it up and then it would be used by mainstream
users. At this point I want to avoid, as best I can, any changes from
default to the 'postgres.conf' file or other external files. Later
though, once I finish this testing phase, I plan to write a section of
external tweaking where I will test these changes out and note my
success for mre advanced users who feel more comfortable playing with
postgres (and web server, rsync, etc) configs.

   If there is any way that I can make changes like this similar from
inside my (perl) program I would prefer that. For example, I implemented
the 'enable_seqscan' via:

$DB->do("SET ENABLE_SEQSCAN TO OFF") || die...
...
$DB->do("SET ENABLE_SEQSCAN TO ON") || die...

   Thank you very kindly! You and Bruno are wonderfully helpful! (as are
the other's who have replied ^_^;)

Madison

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-