Re: Immutable function WAY slower than Stable function?
On Mon, Aug 6, 2018 at 7:42 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ken Tanzer <ken.tanzer@gmail.com> writes:
> Hi Adrian. Happy to provide this info. Though on a side note, I don't
> understand why it should matter, if functions are black box optimization
> fences.
They aren't, at least not when they are SQL-language functions that
meet the conditions for inlining.
Yeah, I kinda realized after I sent this that I wasn't really making much sense. :) I appreciate your explanation though.
The reason that EXPLAIN VERBOSE
is helpful here is that you can see whether the function got inlined
or not: do you see a call to the function, or a representation of
its body?
I wasn't able to get anything more VERBOSE than what I sent before. I re-copied it below. (If there's another command or option I should be using, please advise.)
There is the difference in the two output lines, which I guess suggests that the stable one got inlined and the immutable one didn't?
Output: client_id, si_imm(client_id)
Output: client_id, staff_inspector_stable(client_id, target_date())
spc=> EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT client_id,si_imm(client_id) FROM tbl_residence_own;
QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------Seq Scan on public.tbl_residence_own (cost=0.00..1990.02 rows=6977 width=8) (actual time=3.771..22665.604 rows=6983 loops=1) Output: client_id, si_imm(client_id) Buffers: shared hit=199814Planning time: 0.156 msExecution time: 22677.333 ms (5 rows) spc=> EXPLAIN (ANALYZE,BUFFERS,VERBOSE) SELECT client_id,si_stable(client_id) FROM tbl_residence_own; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------Seq Scan on public.tbl_residence_own (cost=0.00..3734.27 rows=6977 width=8) (actual time=3.100..1302.888 rows=6983 loops=1) Output: client_id, staff_inspector_stable(client_id, target_date()) Buffers: shared hit=60174Planning time: 0.354 msExecution time: 1315.746 ms (5 rows)Cheers,
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
В списке pgsql-general по дате отправления: