Tom Lane wrote:
Madison Kelly <[EMAIL PROTECTED]> 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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
