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

Reply via email to