Re: [HACKERS] Speedups
От | jwieck@debis.com (Jan Wieck) |
---|---|
Тема | Re: [HACKERS] Speedups |
Дата | |
Msg-id | m0yAcxJ-000BFRC@orion.SAPserv.Hamburg.dsh.de обсуждение исходный текст |
Ответ на | Re: [HACKERS] Speedups ("Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>) |
Список | pgsql-hackers |
Vadim wrote: > > ocie@paracel.com wrote: > > > > > > Not sure ofhand, but it would be useful for JDBC's PreparedStatement and > > > > CallableStatement classes > > > > > > We can implement it very easy, and fast. Execution plan may be reused > > > many times. Is this feature in standard ? > > > What is proposed syntax if not ? > > > > I do see a couple sticky points: > > > > We would need some information about which variables are to be > > substituted into this query plan, but this should be fairly > > straightforward. > > Parser, Planner/Optimizer and Executor are able to handle parameters! > No problems with this. Nice discussion - especially when looking at what I initially posted. I assume you think about using SPI's saved plan feature for it. Right? > > > Some querys may not respond well to this, for example, if a table had > > an index on an integer field f1, this would probably be the best way > > to satisfy a select where f1<10. But if this were in a query as f1<x, > > then a sufficiently high value of x might make this not such a good > > way to run the query. I haven't looked into this, but I would assume > > that the optimizer relies on the specific values in such cases. > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ > Unfortunately, no! > We have to add this feature of 'course. > I don't know how we could deal with pre-compiled plans after this :( > May be, we could prepare/store not single plan, but some number of > possible plans. That's something I thought about when I used the SPI functions when I built PL/Tcl. Since the saved plan will be executed via SPI_execp(), we could change some details there. Currently SPI_prepare() and SPI_saveplan() return the plan itself. But they could also return a little control struct that contains the plan and other information. Since I don't expect someone uses these plans for something else than calling SPI_execp(), it wouldn't break anything. SPI_execp() can do some timing calculations. For each execution of a plan it collects the runtime in microseconds (gettimeofday()). After the 5th or 10th call, it builds an average and remembers that permanently. For all subsequent calls it calculates the average time of the last 10 calls and if that gets much higher than the initial average it wouldn't hurt to silently prepare and save the plan again. Using averages lowers the problem that differences in the parameters can cause the runtime differences. Another possible reason for the runtime differences is the overall workload of the server. This could be very high during the initial average calculation. So I think it could be smart to rebuild the plan after e.g. 1000 calls ignoring any runtimes. > > > We need to be able to handle changes to the structures and contents of > > the tables. If the query plan is built and we add 10000 rows to a > > table it references, the query should probably be recompiled. We > > could probably do this at vacuum time. There is also a small chance > > that a table or index that the query plan was using is dropped. We > > could automatically rebuild the query if the table was created after > > the query was compiled. > > We could mark stored plans as durty in such cases to force re-compiling > when an application tries to use this plan. Yep. SPI must remember all prepared and saved plans (and forget about only prepared ones at transaction end). Things like dropping an index or modifying a table structure cause invalidations in the relcache, syscache and catcache (even if another backend did it in some cases). I think it must be possible to tell SPI from there that something happened and which relations are affected. If a plans rangetable contains the affected relation, the plan is marked durty. Things like functions, operators and aggregates are also objects that might change (drop/recreate function -> funcnode in plan get's unusable). I think the best would be that SPI_prepare() set's up a collection of Oid's that cause plan invalidation in the control structure. These are the Oid's of ALL objects (relations, indices, functions etc.) used in the plan. Then a call to SPI_invalidate(Oid) from the cache invalidation handlers doesn't have to walk through the plan itself. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #
В списке pgsql-hackers по дате отправления:
Предыдущее
От: "Pedro J. Lobo"Дата:
Сообщение: Re: [HACKERS] Re: [QUESTIONS] Problems with running v6.3 on DIGITAL UNIX (fwd)