On 12/7/15 7:26 PM, David Rowley wrote:
> I was talking to Thomas Munro yesterday about this, and he mentioned
> that it would be quite nice to have some stats on how much time is spent
> in the planner, vs executor. He came up with the idea of adding a column
> to pg_stat_statements to record the planning time.
I think that would be useful. Maybe something in pg_stat_database too.
> If we could get real statistics on real world cases and we discovered
> that, for example on average that the total CPU time of planning was
> only 1% of execution time, then it would certainly make adding 2%
> overhead onto the planner a bit less of a worry, as this would just be
> %2 of 1% (0.02%). Such information, if fed back into the community might
> be able to drive us in the right direction when it comes to deciding
> what needs to be done to resolve this constant issue with accepting
> planner improvement patches.
Might be nice, but I think it's also pretty unnecessary.
I've dealt with dozens of queries that took minutes to hours to run. Yet
I can't recall ever having an EXPLAIN on one of these take more than a
few seconds. I tend to do more OLTP stuff so maybe others have
experienced long-running EXPLAIN, in which case it'd be great to know that.
Actually, I have seen long EXPLAIN times, but only as part of trying to
aggressively increase *_collapse_limit. IIRC I was able to increase one
of those to 14 and one to 18 before plan time became unpredictably bad
(it wasn't always bad though, just sometimes).
> I believe that with parallel query on the horizon for 9.6 that we're now
> aiming to support bigger OLAP type database than ever before. So if we
> ignore patches like this one then it appears that we have some
> conflicting goals in the community as it seems that we're willing to add
> the brawn, but we're not willing to add the brain. If this is the case
> then it's a shame, as I think we can have both. So I very much agree on
> the fact that we must find a way to maintain support and high
> performance of small OLTP databases too.
+1
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com