Re: Simple postgresql.conf wizard
Re: default_stats_target WAS: Simple postgresql.conf wizard
От:
Aidan Van Dyk <aidan@highrise.ca>
Дата:
Re: Simple postgresql.conf wizard
От:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Дата:
>>> Gregory Stark wrote: > That sounds like it would be an interesting query to analyze in more detail. > Is there any chance to could run the complete graph and get a chart of > analyze > times for all statistics values from 1..1000 ? And log the explain plans to > a > file so we can look for at what statistics targets the plan changed? > > Or if the data is public I would be interested in looking at doing it if you > want to send it to me. There are some very big tables in that query which contain some confidential data. It would be hard do a lot of runs at high default_statistics_target values because the database analyze time goes so high. If I pick out which tables are used by the query, I might be able to put a script together which loops through analyze of those tables with different targets and capturing run time. There are two problems -- finding the time to set this up, and finding server time windows where other things wouldn't be distorting the results. If you could help with setting up the test script, that would go a long way toward solving the first problem. I think I could reserve a smaller multi-CPU machine with identical data but slower CPUs to run the test. I'll attach the query and plan. You'll note that the query looks a little odd, especially all the (1=1) tests. This is because the application allows users to plug in a variety of selection criteria, and any that aren't used are stubbed out that way. I picked one that was not too atypical for the 300,000 runs per day. I intentionally didn't "clean up" the white space, but left it just as it was emitted by our framework, in case any of that affected parse/plan time. If you need schema info not obvious from the plan, let me know. -Kevin
Re: Simple postgresql.conf wizard
От:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Дата:
>>> Gregory Stark wrote: > And log the explain plans to a > file so we can look for at what statistics targets the plan changed? Well, I can give you explain analyze output for default_statistics_target 10 and 50, for whatever that's worth. Unfortunately I blew my save from the first run with target 50, but it ran much faster than the first run of target 10 (attached), I think it was about 2.8 seconds. That may or may not be due to the right pages being cached by coincidence. The machine with the target 50 run was serving the web app at the time, so there was significant other load, while the other was idle except as a replicaton target at the time of the run. -Kevin
Re: Simple postgresql.conf wizard
От:
"Robert Haas" <robertmhaas@gmail.com>
Дата:
I profiled this on CVS HEAD. First, I set this up: > postgres=# create table tk as select random()::text||random()::text||random()::text||random()::text||random()::text||random()::text as r from generate_series(1,1000); > postgres=# insert into tk (select * from tk); > postgres=# insert into tk (select * from tk); > postgres=# insert into tk (select random()::text||random()::text||random()::text||random()::text||random()::text||random()::text as r from generate_series(1,2000)); > postgres=# alter table tk alter r set statistics 1000; > postgres=# analyze tk; Then I profiled a backend that executed the following query 10x (uparrow-enter 10 times from psql): > postgres=# explain select count(*) from (select * from tk as k, tk as l,tk as m,tk as n,tk as o,tk as p where k.r=l.r and k.r=m.r and k.r=n.r and k.r=o.r and k.r=p.r) as x; Results: % cumulative self self total time seconds seconds calls s/call s/call name 77.73 1.92 1.92 5500 0.00 0.00 pglz_decompress 6.07 2.07 0.15 250 0.00 0.00 eqjoinsel 1.62 2.11 0.04 2009500 0.00 0.00 varstr_cmp 1.21 2.14 0.03 3502500 0.00 0.00 datumCopy 1.21 2.17 0.03 3500 0.00 0.00 deconstruct_array 1.21 2.20 0.03 MemoryContextContains Full results attached. ...Robert
