Re: patch - per-tablespace random_page_cost/seq_page_cost

On Thu, Dec 3, 2009 at 11:00 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Sat, Nov 28, 2009 at 9:54 PM, David Rowley <dgrowley@gmail.com> wrote:
>> Robert Haas Wrote:
>>> Hmm.  I'm not able to reliably detect a performance difference between
>>> unpatched CVS HEAD (er... git master branch) and same with spcoptions-
>>> v2.patch applied.  I figured that if there were going to be an impact,
>>> it would be most likely to manifest itself in a query that touches lots
>>> and lots of tables but does very little actual work.  So I used the
>>> attached script to create 200 empty tables, 100 in the default
>>> tablespace and 100 in tablespace "dork" (also known as, why I am
>>> working on this at 11 PM on Thanksgiving).  Then I did:
>>>
>>> SELECT * FROM a1, a2, a3, ..., a100;
>>
>> (I've not read the patch, but I've just read the thread)
>> If you're just benchmarking the planner times to see if the extra lookups
>> are affecting the planning times, would it not be better to benchmark
>> EXPLAIN SELECT * FROM a1, a2, a3, ..., a100; ?
>> Otherwise any small changes might be drowned out in the execution time.
>> Scanning 100 relations even if they are empty could account for quite a bit
>> of that time, right?
>
> Possibly, but even if I can measure a difference doing it that way,
> it's not clear that it matters.  It's fairly certain that there will
> be a performance degradation if we measure carefully enough, but if
> that difference is imperceptible in real-world scanerios, then it's
> not worth worrying about.  Still, I probably will test it just to see.

I did some fairly careful benchmarking of EXPLAIN SELECT * FROM a1,
a2, a3, ..., a100.  I explained this query 100 times via DBD::Pg and
used time to measure how long the script took to run.  I ran the
script three times.  And the result is... the unpatched version came
out 1.7% SLOWER than the patched version.  This seems difficult to
take seriously, since it can't possibly be faster to do a syscache
lookup and parse an array than it is to fetch a constant from a known
memory address, but that's what I got.  At any rate, it seems pretty
clear that it's not hurting much.

...Robert