patch - per-tablespace random_page_cost/seq_page_cost
in [PgSql]
|
From: Robert Haas on 14 Nov 2009 20:22 On Sat, Nov 14, 2009 at 6:36 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > I'm not really convinced of that, but even if we do, so what? It's not > that much code to have an extra cache watching the syscache traffic. > There's an example in parse_oper.c of a specialized cache that's about > as complicated as this would be. It's about 150 lines including copious > comments. We didn't even bother to split it out into its own source > file. Well, if it's that simple maybe it's not too bad. I'll take a look at that one. >> With >> hardwired columns, a regular catcache is all we need. But the >> reloptions stuff is designed to populate a struct, and once we >> populate that struct we have to have someplace to hang it - or I guess >> maybe we could reparse it on every call to cost_seqscan(), >> cost_index(), genericcostestimate(), etc, but that doesn't seem like a >> great idea. > > Well, no, we would not do it that way. I would imagine instead that > plancat.c would be responsible for attaching appropriate cost values to > each RelOptInfo struct, so it'd be more like one lookup per referenced > table per query. It's possible that a cache would be useful even at > that load level, but I'm not convinced. I'm not sure exactly what you mean by the last sentence, but my current design attaches the tablespace OID to RelOptInfo (for baserels only, of course) and IndexOptInfo, and the costing functions trigger the actual lookup of the page costs. I guess that might be slightly inferior to actually attaching the actualized values to the RelOptInfo, since each possible index-path needs the values for both the index and the underlying table. I will take another crack at it. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Bernd Helmle on 16 Nov 2009 04:37 --On 14. November 2009 20:22:42 -0500 Robert Haas <robertmhaas(a)gmail.com> wrote: > I will take another crack at it. > > ...Robert I take this that you are going to provide a new patch version? -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 16 Nov 2009 09:26 On Mon, Nov 16, 2009 at 4:37 AM, Bernd Helmle <mailings(a)oopsware.de> wrote: > --On 14. November 2009 20:22:42 -0500 Robert Haas <robertmhaas(a)gmail.com> > wrote: > >> I will take another crack at it. >> >> ...Robert > > I take this that you are going to provide a new patch version? Yes. I'm not sure whether or not it will be in time for this CF, however. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 26 Nov 2009 23:16 On Thu, Nov 26, 2009 at 4:25 PM, Robert Haas <robertmhaas(a)gmail.com> wrote: > Current version of patch is attached. I've revised it to use the > reloptions stuff, but I don't think it's committable as-is because it > currently thinks that extracting options from a pg_tablespace tuple is > a cheap operation, which was true in the non-reloptions-based > implementation but is less true now. At least, some benchmarking > needs to be done to figure out whether and to what extent this is an > issue. 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; ....and likewise for the bn. I tried this on an unpatched install and also with the patch applied, with and without options set on tablespace dork. I tried it a couple of times and the times were pretty consistent on any given run, but bounced around enough between runs that I can't say with any confidence that this patch makes any difference one way or the other. So it seems as if there is little reason to worry about caching, as Tom suspected, unless someone sees a flaw in my testing methodology. It might matter more in the future, if we have a larger number of tablespace options, but we could always add a cache then if need be. ....Robert
From: "David Rowley" on 28 Nov 2009 21:54
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? David -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |