On 07/05/13 19:33, Simon Riggs wrote:
On 7 May 2013 07:32, Mark Kirkwood <mark.kirkwood@xxxxxxxxxxxxxxx> wrote:
On 07/05/13 18:10, Simon Riggs wrote:
On 7 May 2013 01:23, <mark.kirkwood@xxxxxxxxxxxxxxx> wrote:
I'm thinking that a variant of (2) might be simpler to inplement:
(I think Matt C essentially beat me to this suggestion - he originally
discovered this issue). It is probably good enough for only *new* plans
to
react to the increased/increasing number of in progress rows. So this
would require backends doing significant numbers of row changes to either
directly update pg_statistic or report their in progress numbers to the
stats collector. The key change here is the partial execution numbers
would need to be sent. Clearly one would need to avoid doing this too
often (!) - possibly only when number of changed rows >
autovacuum_analyze_scale_factor proportion of the relation concerned or
similar.
Are you loading using COPY? Why not break down the load into chunks?
INSERT - but we could maybe workaround by chunking the INSERT. However that
*really* breaks the idea that in SQL you just say what you want, not how the
database engine should do it! And more practically means that the most
obvious and clear way to add your new data has nasty side effects, and you
have to tip toe around muttering secret incantations to make things work
well :-)
Yes, we'd need to break up SQL statements into pieces and use external
transaction snapshots to do that.
I'm still thinking that making postgres smarter about having current stats
for getting the actual optimal plan is the best solution.
I agree.
The challenge now is to come up with something that actually works;
most of the ideas have been very vague and ignore the many downsides.
The hard bit is the analysis and balanced thinking, not the
developing.
Yeah - seeing likely downsides can be a bit tricky too. I'll have a play
with some prototyping ideas, since this is actually an area of postgres
(analyze/stats collector) that I've fiddled with before :-)
Cheers
Mark
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
